on 04-19-2007 1:06 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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
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
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
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?
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
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.