I just want to share with you a small tip I stumbled across recently about tracing sql statements.
In my project we use Hibernate as an OR Mapping tool and it is really powerfull and we like it a lot. But because OR Mapping tools shield you from writing your SQL statement troubleshooting can be more difficult. Sometimes we just need to check if the generated SQL statements are correct and which values are used. Hibernate can print the SQL statements to System.out but because it uses prepared statements all you really see are statements with a lot of '?' in them. E.g.
This is actually a common problem debugging prepared statements and this is why you need a wrapper around the normal JDBC driver that shows you the parameters. In the open source domain there is a tool called P6Spy which is a JDBC driver that wraps the original jdbc driver and logs all JDBC operation including the SQL statements with all '?' resolved. I actually started with this when I suddenly realized that the SAP J2EE Engine supports this right out of the box.
It is the built-in SQL Trace that is part of the Open SQL layer.
Setup
This is how you set it up. If you are using the system datasource you can start right away because it supports the full Open SQL functionality.
But sometimes (or very often) you connect to another database through another datasource. Here you have to enable it for SQLTrace by setting the type of the datasource to Native SQL.
Just a short overview about the different types: A datasource can be of type Open SQL that means you have all the functionality of the Open SQL layer like statement checker, tablebuffering etc. enabled. Unfortunately this feature only works with the system datasource.
The level Native SQL gives you a subset of the OpenSQL features like SQLTracing and statement caching.
Vendor SQL means that the datasource does not wrap the JDBC driver but routes all request right through to the real JDBC driver. Therefore you have no SQLTrace available when you use Vendor SQL. Lucky enough Native SQL works with most JDBC drivers (DB2 the old driver sometimes misbehaves) and therefore we can enable most datasources to have SQLTracing functionality.
Once you enabled the datasource you can open the following
URL:
http://yourhost:port/SQLTrace
You have to provide a username and password and then you get to SQLTrace Administration and Evaluation site. Here you can switch on the SQLTrace and then after clicking through your application and firing some JDBC statements you can evaluate the created logfiles.
Here I have an exampe of a simple select statement with one bind parameter. As you can see, the details of the statement reveal the value of the '?' parameter.
Just play around a bit with the filter settings because the information can be overwhelming.
Summary
In this small weblog I explained the differences between Open SQL, Native SQL and Vendor SQL and how you use the SQL Trace functionality to better troubleshoot your applications. I hope you find this usefull.
In my project we use Hibernate as an OR Mapping tool and it is really powerfull and we like it a lot. But because OR Mapping tools shield you from writing your SQL statement troubleshooting can be more difficult. Sometimes we just need to check if the generated SQL statements are correct and which values are used. Hibernate can print the SQL statements to System.out but because it uses prepared statements all you really see are statements with a lot of '?' in them. E.g.
INSERT INTO MYTABLE (ID,NAME) VALUE(?,?)This is actually a common problem debugging prepared statements and this is why you need a wrapper around the normal JDBC driver that shows you the parameters. In the open source domain there is a tool called P6Spy which is a JDBC driver that wraps the original jdbc driver and logs all JDBC operation including the SQL statements with all '?' resolved. I actually started with this when I suddenly realized that the SAP J2EE Engine supports this right out of the box.
It is the built-in SQL Trace that is part of the Open SQL layer.
Setup
This is how you set it up. If you are using the system datasource you can start right away because it supports the full Open SQL functionality.
But sometimes (or very often) you connect to another database through another datasource. Here you have to enable it for SQLTrace by setting the type of the datasource to Native SQL.
Just a short overview about the different types: A datasource can be of type Open SQL that means you have all the functionality of the Open SQL layer like statement checker, tablebuffering etc. enabled. Unfortunately this feature only works with the system datasource.
The level Native SQL gives you a subset of the OpenSQL features like SQLTracing and statement caching.
Vendor SQL means that the datasource does not wrap the JDBC driver but routes all request right through to the real JDBC driver. Therefore you have no SQLTrace available when you use Vendor SQL. Lucky enough Native SQL works with most JDBC drivers (DB2 the old driver sometimes misbehaves) and therefore we can enable most datasources to have SQLTracing functionality.
Once you enabled the datasource you can open the following
URL:
http://yourhost:port/SQLTrace
You have to provide a username and password and then you get to SQLTrace Administration and Evaluation site. Here you can switch on the SQLTrace and then after clicking through your application and firing some JDBC statements you can evaluate the created logfiles.
Here I have an exampe of a simple select statement with one bind parameter. As you can see, the details of the statement reveal the value of the '?' parameter.
Just play around a bit with the filter settings because the information can be overwhelming.
Summary
In this small weblog I explained the differences between Open SQL, Native SQL and Vendor SQL and how you use the SQL Trace functionality to better troubleshoot your applications. I hope you find this usefull.
