jdbc call through Lookup API ...problem??????

[Former Member]

in a message mapping, i have used a UDF, which makes JDBC calls using LookUp API and i m sending queries like "select * from table1",

for which i have created a business service with a receiver JDBC channel,

the problem is ....

In the JDBC receiver channel.....as message protocol...what should i use:

Native SQL String or XML SQL format....????????????which one

can i have some example blog if any exists??????

[bhavesh_kantilal]

It should be XML SQL Format.

Regards

Bhavesh

PS: You had raised a similar question this morning as well. Try to use the same thread for related question.

[Former Member]

yes bhavesh thanks...yes i did...but

the thread stopped suddenly....

anyways..... referring to that thread.....

thats not working at all.....not even

simple sql statements like "select *

from myTable", donno why...

my code was:

Query1 = "SELECT * FROM test1";

try{

//Determine a channel, as created in

the Configuration

channel = LookupService.getChannel("BS_sdptestJDBCReceiver","CC_sdptestJDBCReceiver");

/*Get a system accessor for the channel. As the call is being made to

an DB, an DatabaseAccessor is obtained.*/

accessor = LookupService.getDataBaseAccessor(channel);

//Execute Query and get the values in resultset

resultSet = accessor.execute(Query1);

}

catch(Exception e)

{

}

its giving the same error:

Error while parsing or executing XML-SQL document: Error processing request in sax parser: Error when executing statement for table/stored proc. 'table' (structure 'statement'): com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

the table has got relevant data

[bhavesh_kantilal]

This is how you entire code should look like. This is taken from the blog pointed by Michal.

String Query1 = "SELECT * FROM test1";
Channel channel = null;
DataBaseAccessor accessor = null;
DataBaseResult resultSet = null;


try{

//Determine a channel, as created in the Configuration
channel = LookupService.getChannel("DB-SYSTEM-NAME","DB-CHANNEL-NAME"); 

//Get a system accessor for the channel. As the call is being made to an DB, an DatabaseAccessor is obtained.
accessor = LookupService.getDataBaseAccessor(channel);

//Execute Query and get the values in resultset
resultSet = accessor.execute(Query);

for(Iterator rows = resultSet.getRows();rows.hasNext();){
Map rowMap = (Map)rows.next();
result.addValue((String)rowMap.get("URole"));
}

}
catch(Exception ex){
result.addValue(ex.getMessage())
}

finally{
try{
if (accessor!=null) accessor.close();
}
catch(Exception e){
result.addValue(e.getMessage())
}

}

2. The changes would be the name of the Business Service and Comm Channel. This is case Sensitive.

3. The change would also be on the code to read the data from the resulset as per your requirement.

A)Make sure that this query returns valid data in the actual table.

B) Make sure that the Receiver JDBC adapter has valid parameters and that the JDBC Drivers are installed

C) What is the status of the Receiver JDBC adaoter in Comm Channel Monitoring?

Regards

Bhavesh

[Former Member]

thanks bhavesh....

ur code's working.....

but if i use the query in the same code as "UPDATE test1 SET Status='india'"

the code again gives the same error......may be the query string has error like...

there's something called "escape character for apostrophe..."in the communication channel..ok i tried that....but still not working ...or there's restricions like there should be a space before a '=' and after that in the query string???.....or somthing like that...

though i have tried so many permutations and combinations......but still not working......do u have an idea

[bhavesh_kantilal]

Well, first thing~ You should never use the JDBC LookUp API to perfrom a DML operation on the Databse.

The Lookup API as the name implies is used to Look up/ Select data from the database and should never be used to update the data from the database.

I have never tried a Update on a JDBC Look up API and so am not sure what the exact reason could be, but I would stronlgy ask you not to follow this approach and rather use the standard way of doing this.

Regards

Bhavesh

[Former Member]

yes thanks...bhavesh....

just after a lot of trials i could finds that the accessor.execute(String myQuery) method doesnt work for DML statement....

anyways,

<<Well, first thing~ You should never use the JDBC LookUp API to perfrom a DML operation on the Databse.

>>

is vital info......

[MichalKrawczyk]

Hi,

did you have a look at this blog:

/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

Regards,

michal

-

---

<a href="/people/michal.krawczyk2/blog/2005/06/28/xipi-faq-frequently-asked-questions"><b>XI / PI FAQ - Frequently Asked Questions</b></a>

[Former Member]

sudheep

check this

/people/alessandro.guarneri/blog/2006/03/27/sap-xi-lookup-api-the-killer

/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

/people/prasad.illapani/blog/2006/10/25/how-to-check-jdbc-sql-query-syntax-and-verify-the-query-results-inside-a-user-defined-function-of-the-lookup-api

regards

krishna