Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > get SQL statement from PreparedStatement

Reply
Thread Tools

get SQL statement from PreparedStatement

 
 
JY
Guest
Posts: n/a
 
      02-23-2006
How can I get the SQL statement which will be submitted by the
PreparedStatement to the database to execute?

Thanks



 
Reply With Quote
 
 
 
 
Raymond DeCampo
Guest
Posts: n/a
 
      02-23-2006
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.
 
Reply With Quote
 
 
 
 
JY
Guest
Posts: n/a
 
      02-23-2006

"Raymond DeCampo" <(E-Mail Removed)> wrote in message
news:S0nLf.24789$(E-Mail Removed)...
> 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?

Thanks




 
Reply With Quote
 
Raymond DeCampo
Guest
Posts: n/a
 
      02-23-2006
JY wrote:
> "Raymond DeCampo" <(E-Mail Removed)> wrote in message
> news:S0nLf.24789$(E-Mail Removed)...
>
>>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
--
This signature intentionally left blank.
 
Reply With Quote
 
JY
Guest
Posts: n/a
 
      02-23-2006

"Raymond DeCampo" <(E-Mail Removed)> wrote in message
news:JKnLf.28689$(E-Mail Removed)...
> JY wrote:
>> "Raymond DeCampo" <(E-Mail Removed)> wrote in message
>> news:S0nLf.24789$(E-Mail Removed)...
>>
>>>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
> --
> This signature intentionally left blank.


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.


 
Reply With Quote
 
IchBin
Guest
Posts: n/a
 
      02-23-2006
JY wrote:
> 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?domains...D%3A1%3B&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/tutor...ybigindex.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-)
 
Reply With Quote
 
IchBin
Guest
Posts: n/a
 
      02-23-2006
JY wrote:
> "Raymond DeCampo" <(E-Mail Removed)> wrote in message
> news:JKnLf.28689$(E-Mail Removed)...
>> JY wrote:
>>> "Raymond DeCampo" <(E-Mail Removed)> wrote in message
>>> news:S0nLf.24789$(E-Mail Removed)...
>>>
>>>> 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
>> --
>> This signature intentionally left blank.

>
> 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-)
 
Reply With Quote
 
Raymond DeCampo
Guest
Posts: n/a
 
      02-23-2006
JY wrote:
>
>
> 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

--
This signature intentionally left blank.
 
Reply With Quote
 
steve
Guest
Posts: n/a
 
      02-23-2006
On Fri, 24 Feb 2006 04:08:27 +0800, JY wrote
(in article <0DoLf.21162$%(E-Mail Removed)>):

>
> "Raymond DeCampo" <(E-Mail Removed)> wrote in message
> news:JKnLf.28689$(E-Mail Removed)...
>> JY wrote:
>>> "Raymond DeCampo" <(E-Mail Removed)> wrote in message
>>> news:S0nLf.24789$(E-Mail Removed)...
>>>
>>>> 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
>> --
>> This signature intentionally left blank.

>
> 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.

 
Reply With Quote
 
Real Gagnon
Guest
Posts: n/a
 
      02-24-2006
> How can I get the SQL statement which will be submitted by the
> PreparedStatement to the database to execute?


Enabling the JDBC driver log function may be a way to get it.

See http://www.rgagnon.com/javadetails/java-0411.html
for an example.

Bye.
--
Real Gagnon from Quebec, Canada
* Looking for Java or PB code examples ? Visit Real's How-to
* http://www.rgagnon.com/howto.html
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mismatch in Statement and PreparedStatement execution in Oracle DB. Alex Kizub Java 10 02-11-2010 09:20 PM
Difference between Statement and preparedStatement (for SQL databases) ? Ken Philips Java 4 11-05-2008 01:04 AM
SQL PreparedStatement Christian Java 10 12-01-2007 09:29 PM
PreparedStatement Insert statement MS SQL Server problem ddog Java 1 02-07-2007 05:37 PM
PreparedStatement and prewiev SQL query tomas Java 3 09-16-2006 04:14 PM



Advertisments