Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Is it possible to view the SQL that got executed?

 
Joshua White
Ranch Hand
Posts: 97
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 97
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
http://www.provision6.com/index.htm
The JDBC driver is called P6spy
 
jenkinstechnology llc
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 243
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Guillaume Compagnon,
Excellent link!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic