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
Solved

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

Posted on 2004-09-02
2
2,579 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
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can i Import Access Table Into Oracle Using Toad 36 222
How do I call MySQL Stored Procedure from oracle using HS link ? 5 58
Oracle sql query 7 74
grant user/role question 11 32
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

861 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