aspose file tools*
The moose likes JDBC and the fly likes Is it possible to view the SQL that got executed? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Is it possible to view the SQL that got executed?" Watch "Is it possible to view the SQL that got executed?" New topic
Author

Is it possible to view the SQL that got executed?

Joshua White
Ranch Hand

Joined: Jun 04, 2001
Posts: 97
Hello all,
It would be nice to be able to retrieve the SQL that was executed by the database for debugging purposes. Has anyone found any useful techniques for doing this?

------------------
Joshua White
Perusing Java Certification
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

If you are using statements you can do the following:
String query = "select * from emp";
System.out.println(query);
rs = stmt.executeQuery(query);
Unfortunately with PreparedStatements you can only print out the individual parts of it:
String query = select * from emp where id = ?";
PreparedStatement ps = con.prepareStatement(query);
System.out.println(query);
System.out.println(value1);
ps.setString(1,value1);
...
Hope this helped a little bit. If you are looking for a method like ResultSet.getQueryString(); I think you're out of luck.

Jamie
Robert Brunner
Ranch Hand

Joined: Jul 18, 2001
Posts: 49
Originally posted by Jamie Robertson:
If you are using statements you can do the following:
String query = "select * from emp";
System.out.println(query);
rs = stmt.executeQuery(query);
Unfortunately with PreparedStatements you can only print out the individual parts of it:
String query = select * from emp where id = ?";
PreparedStatement ps = con.prepareStatement(query);
System.out.println(query);
System.out.println(value1);
ps.setString(1,value1);
...
Hope this helped a little bit. If you are looking for a method like ResultSet.getQueryString(); I think you're out of luck.

Jamie


Actually, there is a tool called a JDBC spy driver, which actually shows the SQL that is being used by the database (which may be different than what is in your java code. Merant provides a version (not sure who actually wrote it). Basically, the spy driver does the normal stuff, but also logs what it is sending.
Again, very useful for actual debugging, especially with complex joins or stored procedures.
Cheers,
Robert
Joshua White
Ranch Hand

Joined: Jun 04, 2001
Posts: 97
This is exactly what I was looking for. Could you provide me with a URL where I could find more information?
Thank you.
------------------
Joshua White
Perusing Java Certification
Guillaume Compagnon
Ranch Hand

Joined: Aug 09, 2001
Posts: 106
http://www.provision6.com/index.htm
The JDBC driver is called P6spy


---------<BR>Guillaume
jenkinstechnology llc
Greenhorn

Joined: Aug 18, 2001
Posts: 7
Originally posted by Robert Brunner:

Actually, there is a tool called a JDBC spy driver, which actually shows the SQL that is being used by the database (which may be different than what is in your java code. Merant provides a version (not sure who actually wrote it). Basically, the spy driver does the normal stuff, but also logs what it is sending.
Again, very useful for actual debugging, especially with complex joins or stored procedures.
Cheers,
Robert

only the same lines, and you may already know, but Microsoft SQL Server has SQL Profiler which shows you what is occuring at the database level itself. I find this invaluable, especially when looking for lock contention, stored procedure input params, etc.
paul
Stanley Tan
Ranch Hand

Joined: May 17, 2001
Posts: 243
Guillaume Compagnon,
Excellent link!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Is it possible to view the SQL that got executed?