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

Hello,

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.

Thanks!
lhalkapeelAsked:
Who is Participating?
 
grim_toasterConnect With a Mentor Commented:
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:

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

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++) {
                oracle.jdbc.driver.OracleLog.startLogging();

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

                oracle.jdbc.driver.OracleLog.stopLogging();

                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!
0
 
earth man2Commented:
turn audit on
0
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.

All Courses

From novice to tech pro — start learning today.