Getting Error in JDBC

[Former Member]

Hello,

got same error befor (look ), but can not understand this time.

Sending this JDBC request :

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

- <ns0:MT_User_CMDB_request xmlns:ns0="http://com.test">

- <STATEMENT>

- <TABLENAME action="SELECT">

<TABLE>tablename</TABLE>

<access>

<lastname />

<firstname />

<ccresp />

</access>

<key>

<ccresp compareOperation="LIKE">%9313%</ccresp>

</key>

</TABLENAME>

</STATEMENT>

</ns0:MT_User_CMDB_request>

With following error:

<SAP:AdditionalText>com.sap.aii.af.ra.ms.api.DeliveryException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'dbo.exp_isos_Usr' (structure 'STATEMENT'): com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '9313'.</SAP:AdditionalText>

Normally this happens, if the field ccresp would be of type integer and I try to use a like-statement. But this field has the type varchar and with a database tool I'm able to perform a statement like this:

select * from tablename

where ccresp like '9313'

But this takes about 2 Minutes. Too long for XI? But then the error message is confusing me.

thanks

chris

[Former Member]

Thomas,

Add the parameter <b>logSQLStatement</b> and value <b>true</b> in your JDBC Commn. channel. This will log the query created with your data type in CC monitoring. Copy this query and execute it directly in your data base and check.

Refer SAP note: 801367

Regards,

Jai Shankar

[Former Member]

Hello,

I inserted the parameter, but where can I find this query? Which monitoring do you mean? In sxmb_moni or the component monitoring of the runtime monitoring (web browser)?

thx

[Former Member]

Thomas,

Check the log in Communication channel moitoring (Web browser).

Regards,

Jai Shankar

[Former Member]

Hi,

I finally found the SQL statement

SELECT lastname, firstname, ccresp FROM tablename

WHERE (ccresp LIKE %9314%)

This statement does not work in the tool "Query Analyser", because he didn't accept the %.

Following statements would work:

SELECT lastname, firstname, ccresp FROM tablename

WHERE (ccresp LIKE 9314)

or

SELECT lastname, firstname, ccresp FROM tablename

WHERE ccresp LIKE %9314%

Can I change the format of the created SQL statement? I just need the % sign.

Thanks

chris

[Former Member]

HI,

Check your structure because the ACTION is not existed.

Check the below link

http://help.sap.com/saphelp_nw2004s/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/content.htm

http://help.sap.com/saphelp_nw2004s/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Regards

Chilla

[Former Member]

Chris,

You can give the value you need in mapping. Just populate the value accepted by DB client. In your reply, you have mentioned the same query works

<i>>>Following statements would work:

SELECT lastname, firstname, ccresp FROM tablename

WHERE (ccresp LIKE 9314)

or

SELECT lastname, firstname, ccresp FROM tablename

WHERE ccresp LIKE %9314%</i>

Regards,

Jai Shankar

[Former Member]

Hey,

what do you mean with action ist not existed? In the structure I send

to the JDBC there is choosen the action "Select" (line 4) or what do you

mean?

@Jai Shankar

Like you can see is the structure from the working statements different

to the statement created by XI. The statements that seem to work are tested

in a SQL-Tool. But also in this tool (where no XML files are generated) the

query takes about 1,5 minute. Too long for XI? It seems I received a timeout

more earlier. Can I change the time until he abort with a timeout?

thanks

chris

[Former Member]

Chris,

I can not see any difference bewtween your 2nd working SQL and the SQL created by XI except for the (). But this should not be a problem. Correct?

Or am I still missing some thing?

If your query takes 1,5 min to execute in SQL tool, it means your databse is really slow. This has nothing to do with XI. May be your DB admin should help here to sort this.

Regards,

Jai Shankar

[Former Member]

Hey,

the problem is the wildcard %. This seems to work only without brackets.

Once again. This is created by XI:

WHERE (ccresp LIKE %9314%) -> does not work

This works in my SQL-Tool, but just without %

WHERE (ccresp LIKE 9314)

The only way the wildcard is useable in the SQL-Tool is following:

WHERE ccresp LIKE %9314%

But I don't know if this is the problem within XI.

The query has to lookup 18.000 entries with a wildcard. Don't know if it is to slow for this huge data. Can I change the time to wait for a JDBC action?

greets

chris

[Former Member]

Chris,

I dont think you can alter the way the SQL is formed at receiver JDBC adapter. But, am still wondering if this would cause an error at db. Let us see if some one else has a good soln. for this.

If the database is very huge it is normal to expect a few min delay. Do you get an error in XI due to this? In some of scenarios, I have worked the msg status will be "waiting" till the query completes execution in db and then the status will be changed to "delivered". I have not faced any error msg. If you can post us the error msg, may be I can try some thing more.

Regards,

Jai Shankar

[Former Member]

Hey,

after sending the filled JDBC-structure to database, the breaks after a minute with this error:

<b>

500 Connection timed out

Connection to partner timed out after 60s

</b>

So I think he breaks after 1 minute even the SQL statement needs 1,5 minute.

Or what do you think? So I would like to set up the time to wait, but how?

[Former Member]

Chris,

Is it a synchronous interface? AFAIK, there is no timeout setting for asynchronous adapters. If it is a synch interface, take a look at Michal's weblog on timeouts

/people/michal.krawczyk2/blog/2006/06/08/xi-timeouts-timeouts-timeouts

Be careful when you modify these setting, go through the SAP notes pointed in the blog.

Regarding your other problem of () formed in the query, try raising a OSS message to SAP.

Regards,

Jai Shankar