print out PreparedStatement

[uNConVeNtiOnAL]

Hi -

I have a PreparedStatement that I want to send to system.out.println
to see exactly the sql that is being sent to the db - printing
PreparedStatement.toString() just gives the object reference - does
anyone know how to divulge the string realization of the
PreparedStatement being sent to the db ??

Regards

Tom

 

[Roedy Green]

I have a PreparedStatement

Where do you find the JavaDoc for PreparedStatement?

The link at http://java.sun.com/products/jdbc/download2.html leads to
a page with nothing about JDBC on it.

 

[Steve Claflin]

Where do you find the JavaDoc for PreparedStatement?

The link at http://java.sun.com/products/jdbc/download2.html leads to
a page with nothing about JDBC on it.

The docs for PreparedStatement are in the basic API docs, but they don't
list any getQueryString type of method. But, the way prepared
statements are executed, there wouldn't be any resultant SQL string
anyway -- they are usually precompiled at the database end, not in the
application end. (But, strangely enough, plain old Statement doesn't
seem to have any method to read the query string, either -- I guess they
figure you ought to know what string you put into it).

It seems that you could write an extension of PreparedStatement that
would maintain a string separately, and override all the set methods to
pop the parameters into both the real PreparedStatement and the parallel
string for you to see separately.

 

[Sudsy]

Where do you find the JavaDoc for PreparedStatement?
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html

The link at http://java.sun.com/products/jdbc/download2.html leads to
a page with nothing about JDBC on it.

 

[uNConVeNtiOnAL]

When I have cause to visit the perl group, I am very careful about what I
post
since you usually have someone try and put you down for asking questions. I

realize some folks are trying to get others to do their work for them. but
others
are in the midst of coding, are under the gun, and really want some useful
snippet.

To me, most API docs read:

BIG - something really big.

No help there. I used eclipse help and the debugger first before posting
here. The folks
I inherited this template f rom used preparedstatements
exclusively, and I simply wanted to check what the db was getting in order
to
debug. I also rewrote my query as a statement and checked that over, since
of course I can see very well what was issued to the db.

Steve, thanks for your explaination. I couldn't 'see' the query the db was
receiving,
looks like I can't easily with preparedstatements.

I will think thrice before posting again. Google usually works ....

Tom

 

[Robert Olofsson]

uNConVeNtiOnAL ([email protected]) wrote:
: I have a PreparedStatement that I want to send to system.out.println
: to see exactly the sql that is being sent to the db - printing
: PreparedStatement.toString() just gives the object reference - does
: anyone know how to divulge the string realization of the
: PreparedStatement being sent to the db ??

Usually you can not do that.
Also, if you use prepared statements you usually have a few '?' in the
query string, when debugging/tracing sql you probably want to know what
each of the ? was for each execute.

To achieve this for myself (at work, so I can't share) was to write a
proxy for Connection that returned my own LogStatements/
LogPreparedStatements/LogCallableStatements instead of the thing
returned from the contained database connection. This made it easy to
get logging with full SQL and parameters. Very nice to have when the
system builds lots of queries. (Logging is basically a no-op if noone
is listening of course).

When I did this I also added some code that finds
(callable|prepared)statements that are not closed. Forgetting to close
thoose when the database is an Oracle is not a good idea. Oracle
statemens are never closed, not even in finilize (postgresql seems to
close on finilize). This means that not closing a statement => lots of
open cursors => db will not accept any more statements...

Building something like this is easy. The interfaces are big so it
takes some time, kinda booring actually.

Have fun.
/robo

 

[Brian Palmer]

When I did this I also added some code that finds
(callable|prepared)statements that are not closed. Forgetting to close
thoose when the database is an Oracle is not a good idea. Oracle
statemens are never closed, not even in finilize (postgresql seems to
close on finilize). This means that not closing a statement => lots of
open cursors => db will not accept any more statements...

Do you mean in LogPreparedStatement, your finalize() method had a
check to see if the statement had been closed, or do you mean you
wrote a byte-code verifier to check that close() is executed on every
flow, or...?

 

[Robert Olofsson]

Brian Palmer ([email protected]) wrote:
: > close on finilize). This means that not closing a statement => lots of
: > open cursors => db will not accept any more statements...

: Do you mean in LogPreparedStatement, your finalize() method had a
: check to see if the statement had been closed, or do you mean you
: wrote a byte-code verifier to check that close() is executed on every
: flow, or...?

No, when the statements are created I put them on a watch-list, when
close() is called they are removed from the watch-list. A separate
thread inspects the watch-list regurarly, this thread checks how long
the statement has been running and issues warning after some time and
tries to close the statement after some more time...

This system would not work well with a statement cache, but after
testing, we found that it made no difference (oracle caches statement
internally anyway so the java layer does not have to do it also).

/robo

 

[Brian A Palmer]

Brian Palmer ([email protected]) wrote:
: > close on finilize). This means that not closing a statement => lots of
: > open cursors => db will not accept any more statements...

: Do you mean in LogPreparedStatement, your finalize() method had a
: check to see if the statement had been closed, or do you mean you
: wrote a byte-code verifier to check that close() is executed on every
: flow, or...?

No, when the statements are created I put them on a watch-list, when
close() is called they are removed from the watch-list. A separate
thread inspects the watch-list regurarly, this thread checks how long
the statement has been running and issues warning after some time and
tries to close the statement after some more time...

Ah, that's a useful pragmatic solution.