Synchronous receiver JDBC adapter

[Former Member]

Hi All,

I tried a HTTP-JDBC synch scenario, the JDBC adapter is giving an error in RWB. The error is

Receiver Adapter v1027 for Party '', Service 'NMHG_SQL_SERVER':

Configured at 18:51:27 2006-09-12

Last message processing started 18:52:54 2006-09-12, Error: Transform error in xml processor class, rollback:

ERROR:Processing request: Error when executing statement for table/stored proc. 'null': java.lang.IndexOutOfBoundsException: Index: 1, Size: 1

at com.sap.aii.adapter.jdbc.xml2sql.service(xml2sql.java:175)

Any help is appreciated.

Thanks & Regards,

Jai Shankar.

[MichalKrawczyk]

Hi,

try to follow this weblog:

JDBC Receiver Adapter -- Synchronous Select – Step by Step

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

by Bhavesh to see how to do it step by step

Regards,

michal

[Former Member]

Hi Jai,

please analysze the payload of your message you give to the communication channel

a) is the table statement correct?

b) do you have the action attribute?

I would suggest to deactivate the target fields in your message mapping. Leave one field activated. If the insert in know OK, activate - step by step - the other fields.

This way you will find the field (element) that causes the error.

regards Mario

[Former Member]

Hi Mario,

I am using a select statement in the receiver adapter.

I am using the weblog by Bhavesh for reference.

This is my JDBC request msg type.

<?xml version="1.0" encoding="UTF-8"?>

<ns:MT_JDBC_REQ xmlns:ns="urn:http:jdbc:sync">

<STATEMENT>

<REQUEST ACTION="SELECT">

<TABLE>Employee</TABLE>

<ACCESS>

<Emp_Id>empty</Emp_Id>

<Emp_Name>empty</Emp_Name>

<From_date>empty</From_date>

<To_date>empty</To_date>

<Comments>empty</Comments>

<Created_On>empty</Created_On>

</ACCESS>

<KEY>

<Emp_Id>001</Emp_Id>

</KEY>

</REQUEST>

</STATEMENT>

</ns:MT_JDBC_REQ>

Kindly tell me where am I going wrong..

Thanks & Regards,

Jai Shankar.

[MichalKrawczyk]

hi,

and your response?

can you check the weblog from my reponse to compare?

Regards,

michal

[Former Member]

Hi Michal,

My respose msg is

<?xml version="1.0" encoding="UTF-8"?>

<ns:MT_JDBC_RES xmlns:ns="urn:http:jdbc:sync">

<STATEMENT_response>

<row>

<Emp_Id />

<Emp_Name />

<From_date />

<To_date />

<Comments />

<Created_On />

</row>

</STATEMENT_response>

</ns:MT_JDBC_RES>

I am not sure why I am getting the error msg ERROR:Processing request: Error when executing statement for table/stored proc. 'null': java.lang.IndexOutOfBoundsException: Index: 1, Size: 1

I am not using any stored procedure in my scenario...

Pls guide me...

Thanks & Regards,

Jai Shankar.

[former_member206604]

Hi,

<i>>>><ns:MT_JDBC_REQ xmlns:ns="urn:http:jdbc:sync">

<STATEMENT>

<<b>REQUEST</b> ACTION="SELECT">

<TABLE>Employee</TABLE>

<ACCESS>

<Emp_Id>empty</Emp_Id>

<Emp_Name>empty</Emp_Name>

<From_date>empty</From_date>

<To_date>empty</To_date>

<Comments>empty</Comments>

<Created_On>empty</Created_On>

</ACCESS>

<KEY>

<Emp_Id>001</Emp_Id>

</KEY>

<b></REQUEST></b>

</STATEMENT>

</ns:MT_JDBC_REQ></i>

Check out the blod part... is that fine?

Thanks,

Prakash

[bhavesh_kantilal]

Jai,

A few suggestions~~

We need to check what is the SQL query going out of the JDBC adapter . To see this, check this note :801367

After this, execute the same SQL query on your DB client and see if it returns some data.

My gut feeling, Emp_ID has come datatype mismatch.

Regards,

Bhavesh

[bhavesh_kantilal]

Jai,

Got the error

You have not given the attribute <b>compareOperation</b>

<ns:MT_JDBC_REQ xmlns:ns="urn:http:jdbc:sync">

<STATEMENT>

<REQUEST ACTION="SELECT">

<TABLE>Employee</TABLE>

<ACCESS>

<Emp_Id>empty</Emp_Id>

<Emp_Name>empty</Emp_Name>

<From_date>empty</From_date>

<To_date>empty</To_date>

<Comments>empty</Comments>

<Created_On>empty</Created_On>

</ACCESS>

<KEY>

<b><Emp_Id compareOperation="EQ">001</Emp_Id></b>

</KEY>

</REQUEST>

</STATEMENT>

</ns:MT_JDBC_REQ>

Try it and let me know.

Regards,

Bhavesh

[Former Member]

Bhavesh,

I have given the attribute compareOperation and checked. Still I am getting the same error.

P.S Why do I need to give the attribute compareOperation? Is it not enough if I give the key tag? Coz, in SAP help I read the select querry will be formulated with the action, table, access and key tags.

Some thing like <b>select <access tags elements> from <table> where <key field = values mapped></b>

Can you pls explain this also...

Thanks & Regards,

Jai Shankar.

[bhavesh_kantilal]

Jai,

If the Operation is = then the attribute is optional. If you want , greater than , less than etc it is mandatory.

Did u check with the SQL query? Did you check the SAP note on that?

Regards,

Bhavesh

[Former Member]

Yes Bhavesh. I checked the note.

I am unable to find the SQL querry in Msg monitoring details tab.

All I am getting here is "Unable to execute statement for table or stored procedure. "null" due to java.lang.IndexOutOfBoundsException: Index: 1, Size: 1"

I am on SP09. I am stuuck up here. Pls help me out....

Thanks & Regards,

Jai Shankar.

[bhavesh_kantilal]

Jai,

A few more questions,

1. Under ACCESS in your request message you have given "EMPTY" . Have you mapped it to EMPTY constant? All you need is a blank constant.

Also, like I told try executing the Call to the Select Statement using the DB Client.

Check if EMPID is a number or a string in the Table. If number , make it as 1 instead of 001.

Regards,

Bhavesh

[Former Member]

Bhavesh,

I changed the empty constants to blank constants.

While using the Select querry on db client (SQL querry analyser) it returns 1 row.

All the fields are string in both table and data type. Still the same error...

In SXMB_MONI I am getting 2 msgs

1. sender scheme : XI Party

sender agency : http//sap.com/xi/XI

sender service : SQL client service

sender namespace : urn:http:jdbc:sync

sender interface : MI_JDBC_IN

receiver scheme : XI party

receiver agency : http//sap.com/xi/XI

receiver service : HTTP client service

receiver namespace : urn:http:jdbc:sync

receiver intrface : MI_HTTP_OUT

and the other one is HTTP client to SQL server.

I am not sure why I am getting the first msg, is it because this is a sync scenario? If so why party names are coming in? This is a service with out party scenario.

Thanks a lot for your continous support and guidance.

Thanks & Regards,

Jai Shankar.

[bhavesh_kantilal]

Jai,

Am lost for ideas. Look into the note I had givem.

it suggests how to get the SQL statement in the JDBC adapter's Log in RWB.

This will help us understand the problem better.

Regards,

Bhavesh

[bhavesh_kantilal]

Jai,

One last try. Looked into one of my scenarios and this is how my mapping looks,

<ns:MT_JDBC_REQ xmlns:ns="urn:http:jdbc:sync">

<STATEMENT>

<b>BLANK CONSTANT for Request</b><REQUEST ACTION="SELECT">

<TABLE>Employee</TABLE>

<b>BLANK constant for ACCESS</b><ACCESS>

BLANK CONSTANT <Emp_Id>empty</Emp_Id>

BLANK CONSTANT<Emp_Name>empty</Emp_Name>

BLANK CONSTANT<From_date>empty</From_date>

BLANK CONSTANT<To_date>empty</To_date>

BLANK CONSTANT<Comments>empty</Comments>

BLANK CONSTANT<Created_On>empty</Created_On>

</ACCESS>

BLANK CONSTANT<KEY>

<Emp_Id>001</Emp_Id>

</KEY>

</REQUEST>

</STATEMENT>

</ns:MT_JDBC_REQ>

Can you try it with this mapping?

Regards,

Bhavesh

[Former Member]

Hi Bhavesh,

<i>Parameter name: "logSQLStatement"

Parameter type: boolean

Parameter value: true for any string value, false only for empty string

Parameter value default: false (empty String)</i>

Now to use this in my receiver JDBC adapter, should I use the advanced mode and give the parameters like this??

logSQLStatement true

or do I need to specify all the access tag elements here?

Thanks & Regards,

Jai Shankar.

[bhavesh_kantilal]

Jai,

In your JDBC adapter,

Advanced mode, in the table, add a row , and give logSQLStatement value aa true

And then test and see if the SQL statement is displayed. I have not tried this. Let me know if it does.

Regards,

Bhavesh

[Former Member]

Bhavesh,

It seems the setting is not working.

I am not able to see the SQL in MSG monitoring.

Thanks & Regards,

Jai Shankar.

[Former Member]

I tried this too. It does not work. The note is dated 5/18/2005. Does it apply only to SP 9. We are using SP16 now.

[Former Member]

The problem you are having is that the SQL is not constructed yet. Use lower case for tags action, table, access and key and then try again.

Regards

Venkat