4 Replies Latest reply on May 20, 2009 11:36 PM by Dmitri Zakharov

    SQL - query arguments debugging

    Dmitri Zakharov Newbie

      Hello,


      Does anybody know if it's possible to configure SQL debugging in Seam 2.1.1.GA/JBoss 5.0.1.GA to be able to see the final SQL executed by Hibernate? What I mean by the final is including the values of arguments for the query.


      I have a strange problem in the CRUD prototype project that has just been generated from existing DB2/AS400 database as EAR project and I did not modify anything yet. The problem seams to be random (at least I was not able yet to identify the exact pattern of the error cause). When I type in one of search parameters in the standard generated List view page and Search I get a huge exception stack, which seems to be starting with the following:


      2009-05-15 12:22:43,260 WARN  [org.hibernate.util.JDBCExceptionReporter] (http-127.0.0.1-8080-2) SQL Error: -171, SQLState: 42815
      2009-05-15 12:22:43,260 ERROR [org.hibernate.util.JDBCExceptionReporter] (http-127.0.0.1-8080-2) [SQL0171] Argument *N of function CONCAT not valid.
      



      The output above follows the following query that I see in server.log file:


      2009-05-15 12:22:43,245 INFO  [STDOUT] (http-127.0.0.1-8080-2) Hibernate: 
          select
              claim0_.CLAIM_ID as CLAIM1_1_,
              claim0_.ADJUSTMENT_NUMBER as ADJUSTMENT2_1_,
              claim0_.STATUS_ID as STATUS22_1_,
              claim0_.CREATE_APPLICATION as CREATE3_1_,
              claim0_.CREATE_DATE as CREATE4_1_,
              claim0_.CREATE_USER as CREATE5_1_,
              claim0_.DOCUMENT_NUMBER as DOCUMENT6_1_,
              claim0_.DUTY_AMOUNT as DUTY7_1_,
              claim0_.DUTY_PAID_DATE as DUTY8_1_,
              claim0_.EXCISE_DUTY_AMOUNT as EXCISE9_1_,
              claim0_.EXCISE_TAX_AMOUNT as EXCISE10_1_,
              claim0_.EXPORT_DATE as EXPORT11_1_,
              claim0_.EXPORTER_ID as EXPORTER23_1_,
              claim0_.GST_AMOUNT as GST12_1_,
              claim0_.IMPORTER_CITY as IMPORTER13_1_,
              claim0_.IMPORTER_COUNTRY as IMPORTER14_1_,
              claim0_.IMPORTER_NAME as IMPORTER15_1_,
              claim0_.IMPORTER_POSTAL_CODE as IMPORTER16_1_,
              claim0_.IMPORTER_PROVINCE as IMPORTER17_1_,
              claim0_.IMPORTER_STREET as IMPORTER18_1_,
              claim0_.PST_AMOUNT as PST19_1_,
              claim0_.SHIPMENT_NUMBER as SHIPMENT20_1_,
              claim0_.TOTAL_AMOUNT as TOTAL21_1_,
              claim0_.TRANSMISSION_ID as TRANSMI24_1_ 
          from
              UNITED.CREDITS_DB.CLAIM claim0_ 
          where
              lower(claim0_.IMPORTER_NAME) like lower(?)||'%' 
          order by
              claim0_.DOCUMENT_NUMBER asc fetch first 26 rows only 



      And concatanation is happening in the following part:


      where
              lower(claim0_.IMPORTER_NAME) like lower(?)||'%' 
      



      If I click Reset button and search again by Importer Name value, it might work OK. Than if I click one of the sort links in the result table header, type it search value and click Search again it might fails.


      So I would like to see what argument value is in the final SQL query. And what values causes this error:


      [SQL0171] Argument *N of function CONCAT not valid



      Any leads are really appreciated.


      Thank you.

        • 1. Re: SQL - query arguments debugging
          Nikos Paraskevopoulos Novice

          Hi,


          This is actually a Hibernate question. But take a look at this for a start.


          If it doesnt work, try searching for something like: hibernate show sql parameters.


          Good luck!

          • 2. Re: SQL - query arguments debugging
            Francisco Jose Peredo Noguez Master

            This is in fact a JDBC problem. And there is a JDBC solution, just use log4jdbc, I use it with all my hibernate/seam projects.

            • 3. Re: SQL - query arguments debugging
              Dmitri Zakharov Newbie

              Thank you for the lead Francisco. I'll check it out.


              This problems bugs me. It looks like DB2/AS400 driver problem. Just to compare I recreated the same database in MySQL instead of DB2/AS400. I generated the project with seam-gen again (using java mysql connector) and run it. I do not experience this problem with MySQL. The search for claims does not throw exception.


              So there is something that does not work well or requires extra configuration in this combinations:


              Seam       : 2.1.1.GA
              JBoss      : 5.0.1.GA
              Java       : 1.6.0_10
              JDBC driver: AS/400 Toolbox for Java JDBC Driver, version: 8.8
              



              I'm using JTOpen (IBM Toolbox for Java). I noticed that they have jtopen65jdbc40jdk6.zip, JDBC 4 driver that requires JDK 6. I am going to experiment with that and use log4jdbc logging suggested by you.

              • 4. Re: SQL - query arguments debugging
                Dmitri Zakharov Newbie

                Hello Francisco,


                I am setting up log4jdbc as described here http://code.google.com/p/log4jdbc/ . But I need DB2400 JDBC driver that is not in the list specified in section :


                3. Set your JDBC driver class to net.sf.log4jdbc.DriverSpy in your application's configuration.
                



                So as explained further I need to set system property


                -Dlog4jdbc.drivers=<driverclass>[,<driverclass>...]
                



                In my case that will be


                -Dlog4jdbc.drivers=com.ibm.as400.access.AS400JDBCDriver
                



                But, where can I set it for my Seam/JBoss project? When I run my project now I get the following error, obviously


                2009-05-20 17:25:52,144 WARN  [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] (HDScanner) Throwable while attempting to get a new connection: null
                org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (org.jboss.resource.JBossResourceException: Failed to register driver for: net.sf.log4jdbc.DriverSpy; - nested throwable: (java.lang.ClassNotFoundException: net.sf.log4jdbc.DriverSpy from BaseClassLoader@6f9894{VFSClassLoaderPolicy@8a45cd{name=vfsfile:/D:/JBoss/jboss-as-5.0.1.GA/server/default/deploy/credits.ear/ 
                



                Thank you.