get SQL statement from PreparedStatement

J

JY

How can I get the SQL statement which will be submitted by the
PreparedStatement to the database to execute?

Thanks
 
R

Raymond DeCampo

JY said:
How can I get the SQL statement which will be submitted by the
PreparedStatement to the database to execute?

That isn't how prepared statements (necessarily) work. The idea behind
the prepared statement is that the driver or database will precompile
the SQL into some driver or database specific format that allows for
value bindings. Typically the execution plan for the SQL is determined
by this (the execution plan is the steps the database will take to
retrieve the results; e.g., use index XYZ, full table scan of ABC, etc.).

It is possible for a JDBC driver to trivially implement
PreparedStatements by generating SQL and transmitting that to the
database. However, in such a driver PreparedStatements would not be any
more efficient than regular Statements.

If what you really wanted to achieve was to known what parameters are
being passed to the PreparedStatement there are JDBC spy tools that can
help.

HTH,
Ray
 
J

JY

Raymond DeCampo said:
That isn't how prepared statements (necessarily) work. The idea behind
the prepared statement is that the driver or database will precompile the
SQL into some driver or database specific format that allows for value
bindings. Typically the execution plan for the SQL is determined by this
(the execution plan is the steps the database will take to retrieve the
results; e.g., use index XYZ, full table scan of ABC, etc.).

It is possible for a JDBC driver to trivially implement PreparedStatements
by generating SQL and transmitting that to the database. However, in such
a driver PreparedStatements would not be any more efficient than regular
Statements.

If what you really wanted to achieve was to known what parameters are
being passed to the PreparedStatement there are JDBC spy tools that can
help.

HTH,
Ray

I need to print out all the SQL messages as being submitted to database, in
my log file. I want to write a method which will take PreparedStatement as
an argument and print out its SQL to server.log file. Anywhere in my
application after I have set arguments to a PreparedStatement, I will call
this method before executing the SQL in PreparedStatement. Thats why I ask
this question. Any way I can achieve this task?

Thanks
 
R

Raymond DeCampo

JY said:
I need to print out all the SQL messages as being submitted to database, in
my log file. I want to write a method which will take PreparedStatement as
an argument and print out its SQL to server.log file. Anywhere in my
application after I have set arguments to a PreparedStatement, I will call
this method before executing the SQL in PreparedStatement. Thats why I ask
this question. Any way I can achieve this task?

Perhaps the best approach will be to examine the logging options
provided by your JDBC driver. But I re-iterate, the term "SQL message"
does not necessarily apply to a PreparedStatement. Also, you do not
"execute the SQL in [a] PreparedStatement." You execute the
PreparedStatement.

HTH,
Ray
 
J

JY

Raymond DeCampo said:
JY said:
I need to print out all the SQL messages as being submitted to database,
in my log file. I want to write a method which will take
PreparedStatement as an argument and print out its SQL to server.log
file. Anywhere in my application after I have set arguments to a
PreparedStatement, I will call this method before executing the SQL in
PreparedStatement. Thats why I ask this question. Any way I can achieve
this task?

Perhaps the best approach will be to examine the logging options provided
by your JDBC driver. But I re-iterate, the term "SQL message" does not
necessarily apply to a PreparedStatement. Also, you do not "execute the
SQL in [a] PreparedStatement." You execute the PreparedStatement.

HTH,
Ray

So java does not have a function which can tell me the SQL statement which
will be executed? In other languages like PowerBuilder, you can get the SQL
statement you are going to submit.
 
I

IchBin

JY said:
How can I get the SQL statement which will be submitted by the
PreparedStatement to the database to execute?

Thanks
Can find a lot of examples at the "The Java Developers Almanac 1.4"
http://www.google.com/custom?domain...000000;GFNT:0000FF;GIMP:0000FF;FORID:1;&hl=en

Example: "e259. Inserting a Row into a Database Table Using a Prepared
Statement"
http://javaalmanac.com/egs/java.sql/InsertPs.html

Documented in "The Big Index"
here:http://java.sun.com/docs/books/tutorial/reallybigindex.html

under: "Trail: JDBC(TM) Database Access"


--

Thanks in Advance...
IchBin, Pocono Lake, Pa, USA
http://weconsultants.servebeer.com/JHackerAppManager
__________________________________________________________________________

'If there is one, Knowledge is the "Fountain of Youth"'
-William E. Taylor, Regular Guy (1952-)
 
I

IchBin

JY said:
Raymond DeCampo said:
JY said:
JY wrote:

How can I get the SQL statement which will be submitted by the
PreparedStatement to the database to execute?

That isn't how prepared statements (necessarily) work. The idea behind
the prepared statement is that the driver or database will precompile the
SQL into some driver or database specific format that allows for value
bindings. Typically the execution plan for the SQL is determined by this
(the execution plan is the steps the database will take to retrieve the
results; e.g., use index XYZ, full table scan of ABC, etc.).

It is possible for a JDBC driver to trivially implement
PreparedStatements by generating SQL and transmitting that to the
database. However, in such a driver PreparedStatements would not be any
more efficient than regular Statements.

If what you really wanted to achieve was to known what parameters are
being passed to the PreparedStatement there are JDBC spy tools that can
help.

HTH,
Ray

--
This signature intentionally left blank.

I need to print out all the SQL messages as being submitted to database,
in my log file. I want to write a method which will take
PreparedStatement as an argument and print out its SQL to server.log
file. Anywhere in my application after I have set arguments to a
PreparedStatement, I will call this method before executing the SQL in
PreparedStatement. Thats why I ask this question. Any way I can achieve
this task?
Perhaps the best approach will be to examine the logging options provided
by your JDBC driver. But I re-iterate, the term "SQL message" does not
necessarily apply to a PreparedStatement. Also, you do not "execute the
SQL in [a] PreparedStatement." You execute the PreparedStatement.

HTH,
Ray

So java does not have a function which can tell me the SQL statement which
will be executed? In other languages like PowerBuilder, you can get the SQL
statement you are going to submit.
That is because the SQL statement is embedded in the DataWindow object.

--

Thanks in Advance...
IchBin, Pocono Lake, Pa, USA
http://weconsultants.servebeer.com/JHackerAppManager
__________________________________________________________________________

'If there is one, Knowledge is the "Fountain of Youth"'
-William E. Taylor, Regular Guy (1952-)
 
R

Raymond DeCampo

JY said:
So java does not have a function which can tell me the SQL statement which
will be executed? In other languages like PowerBuilder, you can get the SQL
statement you are going to submit.

I do not know how many other ways I can tell you that once you are at
the PreparedStatement stage you are past the SQL portion of the program.

There is nothing in the standard APIs to help you. So start looking in
the documentation specific to your driver or start implementing it yourself.

Ray
 
S

steve

Raymond DeCampo said:
JY said:
JY wrote:

How can I get the SQL statement which will be submitted by the
PreparedStatement to the database to execute?


That isn't how prepared statements (necessarily) work. The idea behind
the prepared statement is that the driver or database will precompile the
SQL into some driver or database specific format that allows for value
bindings. Typically the execution plan for the SQL is determined by this
(the execution plan is the steps the database will take to retrieve the
results; e.g., use index XYZ, full table scan of ABC, etc.).

It is possible for a JDBC driver to trivially implement
PreparedStatements by generating SQL and transmitting that to the
database. However, in such a driver PreparedStatements would not be any
more efficient than regular Statements.

If what you really wanted to achieve was to known what parameters are
being passed to the PreparedStatement there are JDBC spy tools that can
help.

HTH,
Ray

--
This signature intentionally left blank.


I need to print out all the SQL messages as being submitted to database,
in my log file. I want to write a method which will take
PreparedStatement as an argument and print out its SQL to server.log
file. Anywhere in my application after I have set arguments to a
PreparedStatement, I will call this method before executing the SQL in
PreparedStatement. Thats why I ask this question. Any way I can achieve
this task?

Perhaps the best approach will be to examine the logging options provided
by your JDBC driver. But I re-iterate, the term "SQL message" does not
necessarily apply to a PreparedStatement. Also, you do not "execute the
SQL in [a] PreparedStatement." You execute the PreparedStatement.

HTH,
Ray

So java does not have a function which can tell me the SQL statement which
will be executed? In other languages like PowerBuilder, you can get the SQL
statement you are going to submit.

basically you build an sql string and submit it to the database.
then you add in your paramaters etc and submit it to the database..


the database parses and compiles the statement internally. ( or it may even
have a pre-parsed, pre-compiled previous version squirreled away(oracle))


if you want to see the sql "at the database end" after the statement has been
assembled, then you CAN use java, but you must query the database tools to do
it.


the only other way i can think would be to re-write your sql routines, so
that you log the statement & params BEFORE they are sent to the database.



it depends on the database you are using.
 
T

Thomas Kellerer

JY wrote on 23.02.2006 21:08:
So java does not have a function which can tell me the SQL statement which
will be executed? In other languages like PowerBuilder, you can get the SQL
statement you are going to submit.

As the Statement class is implemented by the driver it's up to the database
vendor to implement such functionality. This has nothing to do with Java itself.
Actually there are drivers out there that will print everything when calling
toString() on a Statement or a PreparedStatement (e.g. the Postgres driver IIRC)

It's been some time since I have used PowerBuilder but I cannot recall a way to
get that information for statements using bind variables either.

Thomas
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,733
Messages
2,569,440
Members
44,830
Latest member
ZADIva7383

Latest Threads

Top