JDBC error in workbench

[Former Community Member]

All,



I have a requirement wherein i have to query based on two conditions. For some reason the queries don't get executed as the parameters are in xpath language. I have tried in Oracle and in SQLServer and I get the same error.



I noticed that the queries work as long as there are no or 1 condition but not when there are more than two.



I am pretty sure am missing something here to formulate the query, maybe escape sequence or quotations.







my query is like this :



select column_1 from table1 where folderId ={$ /process_data/@FolderId $} and tp = '{$ /process_data/@TechPackNum $}'



error in the sqlwindow is



Exception: Internal error.. Cause: ALC-DSC-000-000: com.adobe.idp.dsc.DSCRuntimeException: Internal error.



server log :



--



ERROR [org.jboss.ejb.plugins.LogInterceptor] RuntimeException in method: public abstract java.lang.Object com.adobe.idp.dsc.transaction.impl.ejb.adapter.EjbTransactionCMTAdapterLocal.doSupports(com.adobe.idp.dsc.transaction.TransactionDefinition,com.adobe.idp.dsc.transaction.TransactionCallback) throws com.adobe.idp.dsc.DSCException:

java.lang.RuntimeException: Incorrect syntax near '$ '.

at com.adobe.idp.dsc.jdbc.JDBCService.testQuerySingle(JDBCService.java:338)



....



Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '$ '.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)





whereas for a similar query the error is displayed as when tested with oracle db



'Exception: Non supported SQL92 token at position: 76: $. Cause: Non supported SQL92 token at position: 76: $'

anyone has faced this issue and been able to resolve it ?



any pointers would be greatly appreciated.

[Jasmin_Charbonn]

Are your variables resolving to strings? Try to play with your quotes. This seemed to be the cause of you issue.



Use with the quotes: select column_1 from table1 where folderId = '{$ /process_data/@FolderId $}' and tp = '{$ /process_data/@TechPackNum $}'



Or without the quotes : select column_1 from table1 where folderId ={$ /process_data/@FolderId $} and tp = {$ /process_data/@TechPackNum $}



I think the one without the quote should work.



Jasmin

[Former Community Member]

HI Jasmin,



Thanks for your reply, however playing with the quotes doesnt solve my problem, i.e. in my case folder Id is a int variable whereas packnum is a string , so either way it is failing as I had tried it earlier..



i guess this whole query is somewhere parsed using xpath internally before given to the jdbc service and hence the xpath expressions mix with the normal SQL expressions and create this error.

[Jasmin_Charbonn]

Have you tried to use the parametrized query option. You can write a sql statement like "select * from mytable where name = ?"



and then specify in the parametrize list what ? should be replaced with, in your case an xPath expression. You can also specify a test value to test your query.



Jasmin

[Former Community Member]

Hi Jasmin,



I did the following



select * from table1 ?



selected the parametrized option and enabled the following.



Index = 1

Type = String

Value = /process_data/@WhereClause



The variable is set before it is passed to the query.



and my whereclause is initialized to value like this



concat("where folderId = ", /process_data/@FolderId, " and tp = '", /process_data/@TechPackNum, "'")



java.lang.RuntimeException: Incorrect syntax near '@P0'.

at com.adobe.idp.dsc.jdbc.helper.PreparedSqlHelper.executeTestQuery(PreparedSqlHelper.java:118)

....



Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)



I infact tried a wierd option of enclosing the whole query in a string like



"select * from table1 ?";



and provided the same parametrized options, however the erro is different



java.lang.RuntimeException: The index 1 is out of range.

at com.adobe.idp.dsc.jdbc.helper.PreparedSqlHelper.executeTestUpdate(PreparedSqlHelper.java:159)



...



Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.



I know am missing something pretty simple, or is it just something to do with MSSQL ?

[HowardTreisman]

Hi Senthil

I decided to answer your question by writing an entry in my blog.



http://avokahhh.wordpress.com/2008/08/19/using-the-livecycle-sql-jdbc-component-part-i/



I hope this helps...



Howard

[Former Community Member]

Hi Howard,



Your example post is quite helpful. Infact I solved my issue by writing a simple query and just parametrizing instead of building the query string itself.



However I would still love to see the log or trace enabled for the values and the queries being generated by the livecycle before it is pushed to the sql engine.

[Former Community Member]

Check this out. You may have run into a known bug.



http://support.microsoft.com/kb/919568



Mike