Help - how to log hibernate/jdbc bind parameters in sql query statements

[cumbe.ma...@gmail.com]

Hello.

I've tried glowroot, and I am more interested in the queries.

I would like to show the sql queries including the the values of the sql prepared statement parameters. I'm using hibernate for persistence in my applications.

How can I do it?

Is there anything to be configured for that?

[Trask Stalnaker]

Hi, thanks for posting!

Is Glowroot capturing transactions (e.g. web requests) for your application?

If so, it should be capturing sql queries and bind parameters.

Can you give some more details?

Thanks,

Trask

[cumbe.ma...@gmail.com]

Hi, Thanks for your quick response.

It is capturing transactions, and I can see every sql statements. although it is not showing the parameter values.

Here's an example of sql statement log:

/* update mz.gov.cedsif.mpa.core.user.entity.UserEntity */ update MPA_USER set CREATED_BY=?, CREATION_DATE=?, UPDATE_DATE=?, UPDATED_BY=?, ACTIVATED_BY=?, ACTIVATION_DATE=?, ACTIVE=?, STATE=?, BIRTH_DATE=?, NAMES=?, PASSWORD=?, PASSWORD_EXPIRE_DATE=?, PROFILE_ID=?, SESSION_EXPIRE_TIME=?, SESSION_ID=?, SURNAME=?, UNIT_ID=?, USER_NAME=? where ID=?

I'm interested at getting also the real values instead of "?"

For the other hand, I could notice that going to menu Errors, and going to Trace Entries the values are shown in a separate array, like below (see the text in blue):  

jsf apply request: /import-payment-file/import-payment-file3.xhtml

jsf invoke: #{urlController.urlEvaluate(mi1)}

#{urlController.urlEvaluate(mi1)}: javax.el.ELException: java.lang.IllegalStateException: Transaction DummyTransaction{xid=DummyXid{id=284}, status=3} is not in a valid state to be invoking cache operations on.

exception

jdbc query: /* UserEntity.findUserByNuit */ select userentity0_.ID as ID1_14_, userentity0_.CREATED_BY as CREA ... .USER_NAME as USER_NAME19_14_ from MPA_USER userentity0_ where userentity0_.USER_NAME=? ['100000040'] => 1 row

jdbc query: select userentity0_.ID as ID1_14_0_, userentity0_.CREATED_BY as CREATED_BY2_14_0_, userentity0_.CR ...  userentity0_.USER_NAME as USER_NAME19_14_0_ from MPA_USER userentity0_ where userentity0_.ID=? [109] => 1 row

hibernate flush

jdbc query: /* update mz.gov.cedsif.mpa.core.user.entity.UserEntity */ update MPA_USER set CREATED_BY=?, CREATION_DATE=?, UPDATE_DATE=?, UPDATED_BY=?, ACTIVATED_BY=?, ACTIVATION_DATE=?, ACTIVE=?, STATE=?, BIRTH_DATE=?, NAMES=?, PASSWORD=?, PASSWORD_EXPIRE_DATE=?, PROFILE_ID=?, SESSION_EXPIRE_TIME=?, SESSION_ID=?, SURNAME=?, UNIT_ID=?, USER_NAME=? where ID=? [108, 2018-10-03 13:54:05.606, 2019-03-20 10:06:12.68, 109, 108, 2018-10-03 13:54:05.606, '1', 0, 1942-12-28 00:00:00.0, 'ADELINA ISABEL BERNARDINO PAINDANA', 'ru0w1KE5v+ohJ8GygS/gtA==', 2019-05-04 09:51:14.24, 3, NULL, NULL, NULL, 101, '100000040', 109]

hibernate flush

log error: o.i.i.InvocationContextInterceptor - ISPN000136: Error executing command %s, writing keys %s

Transaction DummyTransaction{xid=DummyXid{id=284}, status=3} is not in a valid state to be invoking cache operations on.

exception

log warning: j.e.r.webcontainer.jsf.lifecycle - #{urlController.urlEvaluate(mi1)}: javax.el.ELException: java.l ... on DummyTransaction{xid=DummyXid{id=284}, status=3} is not in a valid state to be invoking cache operations on.

#{urlController.urlEvaluate(mi1)}: javax.el.ELException: java.lang.IllegalStateException: Transaction DummyTransaction{xid=DummyXid{id=284}, status=3} is not in a valid state to be invoking cache operations on.

exception

Thanks

[Trask Stalnaker]

Oh yes, the Queries tab data is aggregated across all bind parameters.

The individual slow traces (and error traces) show the bind parameters.

Trask

[cumbe.ma...@gmail.com]

I didn't realize that before. 

I would like to list all individual queries with bind parameters, like in the Queries tab, instead of having them aggregated, and also be able to get information like the time of execution of each query.

I thought of implementing an agent based on glowroot. I don't have any experience at working with agents.

How execute and build glowroot agent from an IDE? I'm using eclipse IDE. My main goal is to get the sql statements log (individually) only and export them to a database or to a file. I've tried another tool that does it - p6spy, but it does not capture transactions, which was a limitation).

Is there any documentation for glowroot?

Thanks

[Trask Stalnaker]

You can add your own collector which could log these, e.g. https://github.com/trask/glowroot-example-collector

See also this thread for some details on building your own collector https://groups.google.com/d/msg/glowroot/yU8plF1eC5w/JR-_TQ-0BQAJ

You would want to set the trace threshold to 0 (in your config.json file), so that every trace is captured and sent to your collector where you can log the details.

Trask

[Maximiano Cumbe]

Thanks!

I'll try this!

--
You received this message because you are subscribed to a topic in the Google Groups "Glowroot" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/glowroot/wEBOQFMe1S8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to glowroot+u...@googlegroups.com.
To post to this group, send email to glow...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/glowroot/efeef20a-49a3-44d7-ba61-928ad158a3cf%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Saiavinash Pusarapu]

Hi,

By default my glowroot is capturing all the bind parameters and not aggregating the queries. How can I aggregate the queries without bind parameters. All I need is total count etc of similar query with ?  in place of parameters, but it is listing all the instances of similar query with parameters by default. Please help me in fixing this?