7 Replies Latest reply on Sep 2, 2008 10:44 AM by (appleby_michael)

    JDBC error in workbench

    Sentah
      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(c om.adobe.idp.dsc.transaction.TransactionDefinition,com.adobe.idp.dsc.transaction.Transacti onCallback) 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.
        • 1. Re: JDBC error in workbench
          Jasmin Charbonneau Level 4
          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
          • 2. Re: JDBC error in workbench
            Sentah Level 1
            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.
            • 3. Re: JDBC error in workbench
              Jasmin Charbonneau Level 4
              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
              • 4. Re: JDBC error in workbench
                Sentah Level 1
                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:1 18)
                ....

                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 ?
                • 5. Re: JDBC error in workbench
                  HowardTreisman Level 1
                  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
                  • 6. Re: JDBC error in workbench
                    Sentah Level 1
                    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.
                    • 7. Re: JDBC error in workbench
                      Check this out. You may have run into a known bug.

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

                      Mike