Solved

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

Posted on 2004-09-02
2
2,610 Views
Last Modified: 2008-01-09
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!
0
Comment
Question by:lhalkapeel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 11969438
turn audit on
0
 
LVL 7

Accepted Solution

by:
grim_toaster earned 250 total points
ID: 11970813
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question