get SQL statement from PreparedStatement

Discussion in 'Java' started by JY, Feb 23, 2006.

  1. JY

    JY Guest

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

    Thanks
    JY, Feb 23, 2006
    #1
    1. Advertising

  2. 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.
    Raymond DeCampo, Feb 23, 2006
    #2
    1. Advertising

  3. JY

    JY Guest

    "Raymond DeCampo" <> wrote in message
    news:S0nLf.24789$...
    > 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
    JY, Feb 23, 2006
    #3
  4. JY wrote:
    > "Raymond DeCampo" <> wrote in message
    > news:S0nLf.24789$...
    >
    >>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.
    Raymond DeCampo, Feb 23, 2006
    #4
  5. JY

    JY Guest

    "Raymond DeCampo" <> wrote in message
    news:JKnLf.28689$...
    > JY wrote:
    >> "Raymond DeCampo" <> wrote in message
    >> news:S0nLf.24789$...
    >>
    >>>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.
    JY, Feb 23, 2006
    #5
  6. JY

    IchBin Guest

    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?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-)
    IchBin, Feb 23, 2006
    #6
  7. JY

    IchBin Guest

    JY wrote:
    > "Raymond DeCampo" <> wrote in message
    > news:JKnLf.28689$...
    >> JY wrote:
    >>> "Raymond DeCampo" <> wrote in message
    >>> news:S0nLf.24789$...
    >>>
    >>>> 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-)
    IchBin, Feb 23, 2006
    #7
  8. 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.
    Raymond DeCampo, Feb 23, 2006
    #8
  9. JY

    steve Guest

    On Fri, 24 Feb 2006 04:08:27 +0800, JY wrote
    (in article <0DoLf.21162$%>):

    >
    > "Raymond DeCampo" <> wrote in message
    > news:JKnLf.28689$...
    >> JY wrote:
    >>> "Raymond DeCampo" <> wrote in message
    >>> news:S0nLf.24789$...
    >>>
    >>>> 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.
    steve, Feb 23, 2006
    #9
  10. JY

    Real Gagnon Guest

    Real Gagnon, Feb 24, 2006
    #10
  11. 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
    Thomas Kellerer, Feb 24, 2006
    #11
  12. Andrea Desole, Feb 24, 2006
    #12
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ken Philips
    Replies:
    4
    Views:
    48,930
    venkat.sadasivam
    Nov 5, 2008
  2. tomas
    Replies:
    3
    Views:
    1,277
    Bomber
    Sep 16, 2006
  3. ddog
    Replies:
    1
    Views:
    2,031
    Thomas Kellerer
    Feb 7, 2007
  4. Christian

    SQL PreparedStatement

    Christian, Dec 1, 2007, in forum: Java
    Replies:
    10
    Views:
    1,046
    Christian
    Dec 1, 2007
  5. Alex Kizub
    Replies:
    10
    Views:
    1,710
    Alex Kizub
    Feb 11, 2010
Loading...

Share This Page