• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2704
  • Last Modified:

JDBC: Quickest way to debug bad SQL when using PreparedStatement executeUpdate


I need a rapid way to display the value of a PreparedStatement prior to executing the executeUpdate() method.   I dynamically generated the preparedStatement and subsequent setString(n,"xxx"), setInt(n+1,000) statements.  I'm using the Eclipse debugger and it will only show the Prepared statement with the "?".  Is there a way to log or display the entire, completed statement just prior to execution?  I just spent lots of time debugging it and there must be an easier way.

1 Solution
earth man2Commented:
turn audit on
The complete statement really is the one with the ?'s in them.  These are place-holders for the bind variables, and as such there is no way to get the full string of what would be there with the values you set substituted in.  You can look at the v$open_cursor or v$sqlarea views to show that the statement sent to the database is the one with the ?'s in place.

The cleanest way to actually see the values being set is to turn on logging, and depending on which version of the drivers and JDK you are using, depends on the approach used.  Whatever happens you will need a version of the drivers with the debug/trace facilities included in them (identified by the suffix _g), available by following the links on:


The JDK 1.4 version of the Oracle 10g drivers (still usable in a 9i database - but I've only tested in a very limited fashion) has been changed to use the logging API's in JDK 1.4, and as such are much more powerful than the pre 10g driver versions - especially useful is the filtering of data.  If you need an example of it, just ask.

As for prior to 10g drivers, here's some sample code with output showing how to use the oracle logging (you must be using an _g driver).

            pstmt = conn.prepareStatement("SELECT * FROM t1 WHERE id = ? OR id = ?");

            for (int i = 0; i < 10; i++) {

                pstmt.setInt(1, i);
                pstmt.setInt(2, i + 1000);


                rs = pstmt.executeQuery();

                while (rs.next()) {
                    System.out.println(rs.getString(1));  // print any value...

This will print lots of rubbish, but you will need:

DRVR OPER OraclePreparedStatement.setInt(paramIndex=1, x=0)
DRVR OPER OraclePreparedStatement.setInt(paramIndex=2, x=1000)

Which shows what each parameter will be set to.

The other alternative of course, is to implement your own logging, but at least you have the options available!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now