spy sql parameter values format for jboss / wildlfy8 ?

[masummymesingh]

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)

[lafr]

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.

[masummymesingh]

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)

[masummymesingh]

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/

[masummymesingh]

Glassfish alreay

https://blogs.oracle.com/enterprisetechtips/entry/using_p6spy_and_the_glassfish

[masummymesingh]

http://www.databaseskill.com/2200183/

[jamezp]

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

[masummymesingh]

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 ?

[jamezp]

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

--

James R. Perkins

[masummymesingh]

@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 .

[jamezp]

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', '[e-mail]', '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

[masummymesingh]

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 ?

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

[jamezp]

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

[lafr]

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.

[masummymesingh]

   @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.