Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to connect SQL Server from my Oracle database? 11 95
Can i Import Access Table Into Oracle Using Toad 36 169
Queries 15 38
1 FROM DUAL wont work with additional columns ?? 4 36
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

776 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