1 2 Previous Next 16 Replies Latest reply on Dec 16, 2014 5:47 AM by Gulam Samdani

    spy sql parameter values format for jboss / wildlfy8 ?

    Gulam Samdani Expert

      trace JDBC statements ?

       

       

      stept 1 :

       

      <logger category="jboss.jdbc.spy">

                      <level name="TRACE"/>

        </logger>

       

      step 2 :

         <datasource jndi-name="java:/ascend" pool-name="dbpool" enabled="true" spy="true">

       

      step 3 : output look likes : SQL

       

      [jboss.jdbc.spy] (default task-2) java:/bankds [Connection] prepareStatement(insert into account (balance, name) values (?, ?))

      [jboss.jdbc.spy] (default task-2) java:/bankds [PreparedStatement] setLong(1, 150)

      [jboss.jdbc.spy] (default task-2) java:/bankds [PreparedStatement] setString(2, masum)


        [jboss.jdbc.spy] (default task-2) java:/bankds [PreparedStatement] executeUpdate()

       

      any one please help me ?

       

      =========================================================================

       

      How to display  sql parameter values log into following format  for jboss/wildfy 8?

       

       

       

      insert into account (balance, name) values (150, masum)

        • 1. Re: spy sql parameter values format for jboss / wildlfy8 ?
          Frank Langelage Master

          I think there is no way to change the output to your format by configuration change.

          The code responsible for such logging statements is fixed.

          By changing the loggin pattern you can influence the layout of a line but not change it's content or even aggregate lines.

          1 of 1 people found this helpful
          • 2. Re: spy sql parameter values format for jboss / wildlfy8 ?
            Gulam Samdani Expert

            I have found several way to do this output format ...............

            https://code.google.com/p/log4jdbc-log4j2/

            JBoss - TecBoard - InfTec Confluence

            http://ssgru.blogspot.com/2014/03/jboss-7-log4jdbc-jndi-jpa-logback.html

             

            http://sfleiter.github.io/blog/2013/12/08/jboss-datasource-proxy-with-log4jdbc-log4j2/

             

             

            I trying to do this output above way , i have found some error-------------

             

             

            2014-11-14 10:36:33,408 ERROR [org.jboss.as.controller.management-operation] (ServerService Thread Pool -- 28) JBAS014612: Operation ("add") failed - address: ([

                ("subsystem" => "datasources"),

                ("jdbc-driver" => "log4jdbc")

            ]): java.util.ServiceConfigurationError: java.sql.Driver: Provider net.sf.log4jdbc.sql.jdbcapi.DriverSpy could not be instantiated

                at java.util.ServiceLoader.fail(ServiceLoader.java:232) [rt.jar:1.8.0_20]

                at java.util.ServiceLoader.access$100(ServiceLoader.java:185) [rt.jar:1.8.0_20]

                at java.util.ServiceLoader$LazyIterator.nextService(ServiceLoader.java:384) [rt.jar:1.8.0_20]

                at java.util.ServiceLoader$LazyIterator.next(ServiceLoader.java:404) [rt.jar:1.8.0_20]

                at java.util.ServiceLoader$1.next(ServiceLoader.java:480) [rt.jar:1.8.0_20]

                at org.jboss.as.connector.subsystems.datasources.JdbcDriverAdd.performRuntime(JdbcDriverAdd.java:117)

                at org.jboss.as.controller.AbstractAddStepHandler$1.execute(AbstractAddStepHandler.java:75) [wildfly-controller-8.0.0.Final.jar:8.0.0.Final]

                at org.jboss.as.controller.AbstractOperationContext.executeStep(AbstractOperationContext.java:591) [wildfly-controller-8.0.0.Final.jar:8.0.0.Final]

                at org.jboss.as.controller.AbstractOperationContext.doCompleteStep(AbstractOperationContext.java:469) [wildfly-controller-8.0.0.Final.jar:8.0.0.Final]

                at org.jboss.as.controller.AbstractOperationContext.completeStepInternal(AbstractOperationContext.java:273) [wildfly-controller-8.0.0.Final.jar:8.0.0.Final]

                at org.jboss.as.controller.AbstractOperationContext.executeOperation(AbstractOperationContext.java:268) [wildfly-controller-8.0.0.Final.jar:8.0.0.Final]

                at org.jboss.as.controller.ParallelBootOperationStepHandler$ParallelBootTask.run(ParallelBootOperationStepHandler.java:343) [wildfly-controller-8.0.0.Final.jar:8.0.0.Final]

                at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_20]

                at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_20]

                at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_20]

                at org.jboss.threads.JBossThread.run(JBossThread.java:122) [jboss-threads-2.1.1.Final.jar:2.1.1.Final]

            Caused by: java.lang.NoClassDefFoundError: Unable to find Log4j2 as default logging library. Please provide a logging library and configure a valid spyLogDelegator name in the properties file.

                at net.sf.log4jdbc.log.SpyLogFactory.loadSpyLogDelegator(SpyLogFactory.java:94)

                at net.sf.log4jdbc.Properties.<clinit>(Properties.java:203)

                at net.sf.log4jdbc.log.SpyLogFactory.getSpyLogDelegator(SpyLogFactory.java:69)

            • 3. Re: spy sql parameter values format for jboss / wildlfy8 ?
              Gulam Samdani Expert

              main problem is : Unable to find Log4j2 as default logging library . Log4j2 is unsupported in jboss7/wildfly 8. so, i got this problem .

               

              http://blog.c2b2.co.uk/2014/09/alternative-logging-frameworks-for.html#comment-form

              https://code.google.com/p/log4jdbc-log4j2/

              • 6. Re: spy sql parameter values format for jboss / wildlfy8 ?
                James Perkins Master

                What is it you're looking to do? The logs above look correct. When preparing a statement the values are unknown which is likely why they're printed as separate log lines.

                 

                I'm not sure how all these log4j2 issues tie in. There seems to be a lot going on here and it would be easiest to try to solve once issue at a time.

                 

                --

                James R. Perkins

                • 7. Re: spy sql parameter values format for jboss / wildlfy8 ?
                  Gulam Samdani Expert

                  100 % log is correct , agree with you  but it is not sufficient for development .

                  Real world application size is so large so when a table has 50 field then how to identify ?

                  • 8. Re: spy sql parameter values format for jboss / wildlfy8 ?
                    James Perkins Master

                    I'm really not sure what you're looking for here. What is it you'd like to see?

                     

                    --

                    James R. Perkins

                    • 9. Re: spy sql parameter values format for jboss / wildlfy8 ?
                      Gulam Samdani Expert

                      @james , I understand that , this is not logging issue .

                       

                      i am finding solution look likes that any one who already solved this problem, just want some help .

                      • 10. Re: Re: spy sql parameter values format for jboss / wildlfy8 ?
                        James Perkins Master

                        I don't think it's an issue. From what I can gather you're looking to have both the values set for a PreparedStatement to print on the same line as the prepared statement. I just don't see how that's possible. The provided are using Statements or calls not PreparedStatements. A java.sql.Statement can produce the values in a single line as they're known at execution time.

                         

                        Example output from statements:

                        12:10:34,826 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 50) java:jboss/datasources/psg [Statement] executeUpdate(create table Member (id int8 not null, email varchar(255) not null, name varchar(25) not null, phone_number varchar(12) not null, primary key (id)))
                        12:10:34,863 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 50) java:jboss/datasources/psg [Statement] getWarnings()
                        12:10:34,864 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 50) java:jboss/datasources/psg [Statement] executeUpdate(alter table Member add constraint UK_9qv6yhjqm8iafto8qk452gx8h unique (email))
                        12:10:34,883 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 50) java:jboss/datasources/psg [Statement] getWarnings()
                        12:10:34,884 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 50) java:jboss/datasources/psg [Statement] executeUpdate(create sequence hibernate_sequence start 1 increment 1)
                        12:10:34,892 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 50) java:jboss/datasources/psg [Statement] getWarnings()
                        12:10:34,893 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 50) java:jboss/datasources/psg [Statement] executeUpdate(insert into Member (id, name, email, phone_number) values (0, 'John Smith', 'john.smith@mailinator.com', '2125551212'))
                        12:10:34,901 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 50) java:jboss/datasources/psg [Statement] getWarnings()
                        12:10:34,901 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 50) java:jboss/datasources/psg [Statement] close()
                        12:10:34,901 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 50) java:jboss/datasources/psg [Connection] getWarnings()
                        12:10:34,901 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 50) java:jboss/datasources/psg [Connection] clearWarnings()
                        12:10:34,901 DEBUG [jboss.jdbc.spy] (ServerService Thread Pool -- 50) java:jboss/datasources/psg [Connection] close()
                        

                         

                        The above log output shows statements printing the values like you'd like.

                         

                        --

                        James R. Perkins

                        • 11. Re: spy sql parameter values format for jboss / wildlfy8 ?
                          Gulam Samdani Expert

                          wow !! very nice your output . but I can not generate this type of output . My server log by default , show PreparedStatements . i can not configure statement for jboss log.

                           

                          2014-12-02 00:04:08,955 DEBUG [jboss.jdbc.spy] (default task-1) java:/bankds [Connection] prepareStatement(insert into account (balance, name) values (?, ?), 1)

                          2014-12-02 00:04:09,005 DEBUG [jboss.jdbc.spy] (default task-1) java:/bankds [PreparedStatement] setLong(1, 90)

                          2014-12-02 00:04:09,006 DEBUG [jboss.jdbc.spy] (default task-1) java:/bankds [PreparedStatement] setString(2, masum)

                          2014-12-02 00:04:09,009 DEBUG [jboss.jdbc.spy] (default task-1) java:/bankds [PreparedStatement] executeUpdate()

                           

                           

                          2014-12-02 00:04:09,774 DEBUG [jboss.jdbc.spy] (default task-1) java:/bankds [Connection] prepareStatement(SELECT * FROM bank.account Where accid=?)

                          2014-12-02 00:04:09,777 DEBUG [jboss.jdbc.spy] (default task-1) java:/bankds [PreparedStatement] setInt(1, 1)

                          2014-12-02 00:04:09,779 DEBUG [jboss.jdbc.spy] (default task-1) java:/bankds [PreparedStatement] executeQuery()

                          =======================my source code is =========

                          @Stateless

                          public class AccountService implements AccountServiceRemote {

                           

                              @PersistenceContext

                              EntityManager em;

                           

                           

                              public void doSave(Account account){

                                  em.persist(account);

                                  int i=1;

                              Query query=em.createNativeQuery("SELECT * FROM bank.account Where accid=:accid",Account.class);

                              query.setParameter("accid", i);

                              Account ob =(Account)query.getSingleResult();

                           

                               

                              }

                           

                           

                             <datasource jndi-name="java:/bankds" pool-name="MySQLPool2" enabled="true" spy="true">

                                              <connection-url>jdbc:mysql://localhost:3306/bankdb</connection-url>

                                              <driver>mysql</driver>

                                              <pool>

                                                  <max-pool-size>30</max-pool-size>

                                              </pool>

                                              <security>

                                                  <user-name>root</user-name>

                                                  <password>root</password>

                                              </security>

                                          </datasource>

                           

                           

                           

                          =================================================

                          java:jboss/datasources/psg [Statement] executeUpdate  ., i understand my problem comes form PreparedStatement  ..

                           

                          How to show my log using statement instead of PreparedStatement ?


                          ========================================================

                          • 12. Re: spy sql parameter values format for jboss / wildlfy8 ?
                            James Perkins Master

                            That is the correct behavior. There is no way to know the parameter values for a java.sql.PreparedStatement at the time the statement is logged. That is why parameter values are logged AFTER the PreparedStatement logs. There is no way to log the parameters in a single log statement with a PreparedStatement. It simply will not work.

                             

                            --

                            James R. Perkins

                            • 13. Re: spy sql parameter values format for jboss / wildlfy8 ?
                              Frank Langelage Master

                              It's not a question of logging. If you not want PreparedStatements, don't use them.

                              Instead of placeholders and setParameter build you final statement yourself: Query query=em.createNativeQuery("SELECT * FROM bank.account Where accid='accid'",Account.class);

                              But be aware of the draw backs.

                              • 14. Re: spy sql parameter values format for jboss / wildlfy8 ?
                                Gulam Samdani Expert

                                   @James , @Frank

                                  

                                   Thanks for your comments , I am agree that this is not error ,this is  jdbc

                                   statement vs prepared statement log related variation . so, it is not any problem .

                                     

                                   But on the other side , already i have found several way to do this type output

                                  

                                   Logger is : jdbc.sqlonly

                                  

                                   14:22:55,726 INFO  [jdbc.sqlonly] (http-/127.0.0.1:8080-1) insert into Foo (a, b, version, id) values ('aValue', 'bValue', 0, 1)

                                  

                                   14:22:55,727 INFO  [jdbc.sqltiming] (http-/127.0.0.1:8080-1) insert into Foo (a, b, version, id) values ('aValue', 'bValue', 0, 1)

                                 

                                  

                                  

                                   http://sfleiter.github.io/blog/2013/12/08/jboss-datasource-proxy-with-log4jdbc-log4j2/

                                 

                                   http://www.mkyong.com/hibernate/how-to-display-hibernate-sql-parameter-values-solution/

                                  

                                   ==============================================================================================

                                   I am try to using log4jdbc-log4j2 then i got following error .  How to solve this error , give me a solution ?

                                  

                                   Error :

                                  

                                    https://code.google.com/p/log4jdbc-log4j2/

                                  

                                   Caused by: java.lang.NoClassDefFoundError: Unable to find Log4j2 as default logging library.

                                              Please provide a logging library and configure a valid spyLogDelegator name in the properties file.

                                1 2 Previous Next