JDBC error in workbench

Avatar

Avatar
Level 2
Sentah
Level 2

Likes

0 likes

Total Posts

29 posts

Correct reply

0 solutions
View profile

Avatar
Level 2
Sentah
Level 2

Likes

0 likes

Total Posts

29 posts

Correct reply

0 solutions
View profile
Sentah
Level 2

14-08-2008

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.

Replies

Avatar

Avatar
Coach
Level 10
Jasmin_Charbonn
Level 10

Likes

149 likes

Total Posts

2,154 posts

Correct reply

97 solutions
Top badges earned
Coach
Give Back 1000
Give back 900
Give Back 800
Give back 600
View profile

Avatar
Coach
Level 10
Jasmin_Charbonn
Level 10

Likes

149 likes

Total Posts

2,154 posts

Correct reply

97 solutions
Top badges earned
Coach
Give Back 1000
Give back 900
Give Back 800
Give back 600
View profile
Jasmin_Charbonn
Level 10

14-08-2008

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

Avatar

Avatar
Level 2
Sentah
Level 2

Likes

0 likes

Total Posts

29 posts

Correct reply

0 solutions
View profile

Avatar
Level 2
Sentah
Level 2

Likes

0 likes

Total Posts

29 posts

Correct reply

0 solutions
View profile
Sentah
Level 2

15-08-2008

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.

Avatar

Avatar
Coach
Level 10
Jasmin_Charbonn
Level 10

Likes

149 likes

Total Posts

2,154 posts

Correct reply

97 solutions
Top badges earned
Coach
Give Back 1000
Give back 900
Give Back 800
Give back 600
View profile

Avatar
Coach
Level 10
Jasmin_Charbonn
Level 10

Likes

149 likes

Total Posts

2,154 posts

Correct reply

97 solutions
Top badges earned
Coach
Give Back 1000
Give back 900
Give Back 800
Give back 600
View profile
Jasmin_Charbonn
Level 10

15-08-2008

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

Avatar

Avatar
Level 2
Sentah
Level 2

Likes

0 likes

Total Posts

29 posts

Correct reply

0 solutions
View profile

Avatar
Level 2
Sentah
Level 2

Likes

0 likes

Total Posts

29 posts

Correct reply

0 solutions
View profile
Sentah
Level 2

15-08-2008

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 ?

Avatar

Avatar
Boost 5
Level 9
HowardTreisman
Level 9

Likes

5 likes

Total Posts

936 posts

Correct reply

9 solutions
Top badges earned
Boost 5
Boost 3
Boost 1
Affirm 5
Affirm 3
View profile

Avatar
Boost 5
Level 9
HowardTreisman
Level 9

Likes

5 likes

Total Posts

936 posts

Correct reply

9 solutions
Top badges earned
Boost 5
Boost 3
Boost 1
Affirm 5
Affirm 3
View profile
HowardTreisman
Level 9

18-08-2008

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

Avatar

Avatar
Level 2
Sentah
Level 2

Likes

0 likes

Total Posts

29 posts

Correct reply

0 solutions
View profile

Avatar
Level 2
Sentah
Level 2

Likes

0 likes

Total Posts

29 posts

Correct reply

0 solutions
View profile
Sentah
Level 2

19-08-2008

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.