SQL Statement Logging


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: SQL Statement Logging

  1. #1
    John Senford Guest

    SQL Statement Logging


    I want to create logs of all sql statements being sent to a database.
    There is no simple method (e.g. toString(), or toSQL()) provided by the Statement,
    PreparedStatment, and CallableStatement interfaces therefore I have chosen
    to create wrapper classes which contain a single Statement/Prepared.../Callable...
    object and intercept all 'sql string building' operations so as to manually
    maintain my own copy of the SQL string being used.

    However, there is a major flaw in this plan when it comes to the parameter
    setting of a PreparedStatement. A long list of methods are provided to setting
    every possible data type and known object type. The simple data types are
    easy to 'stringify' and insert to my sql string......but what can I do about
    the various objects that may be used as parameters???
    (e.g. Object, java.sql.Date, java.sql.Time, java.sql.Timestamp etc.)

    So we can see, if a using class (that I'm not going to write) wants to use
    PreparedStatement to carry out the operation then my job of logging their
    sql statement becomes very difficult when they want to set a Date object
    (for example) as one of the parameters in the sql string.

    How do I generate a (simple) log statment for that?!?!?!

    by what mechanism could I link a parameter in a simple textual log string
    to an Object?


    example log file:

    ---START---
    10/11/01 13:47:32
    SELECT * FROM Book

    10/11/01 13:49:55
    SELECT BookID FROM Book WHERE Name=?

    ---END---

    now how could I go about linking the parameter for the Name field to an object
    (let's pretend some complex Java class is used as the data type for the Name
    field)???


    Ideally I do not want to have take this linking approach, but there seems
    to be no alternative (and how do you do it anyway).

    any ideas greatly appreciated...

    JohnSenford@hotmail.com

  2. #2
    Paul Clapham Guest

    Re: SQL Statement Logging

    And suppose I had a BLOB field in my database and I used setBinaryStream()
    on a PreparedStatement to store, say, the contents of a JPG file into that
    field? What would that look like in your missing "toSQL()" method? Even if
    you could think up a way to make it displayable, you would have to read the
    entire stream for your logging and then reset it so that the database could
    use it. That's one reason there's no such method. There's another reason:
    the driver may not even generate SQL when you execute the query. Instead,
    it may just pass the "precompiled" query plus a list of parameters over to
    the database.

    I think you are out of luck here. Your project to log SQL statements was
    based on the assumption that it could actually be done. As you've found, it
    can be partially done. So you'll have to decide whether you want a partial
    solution, and if so how partial. You mention dates as an example; one of
    the reasons I use PreparedStatements is that they free me from having to
    know how my database wants its dates represented. Your wrapper class would
    have to know that... or would it? Would it matter if the string you logged
    wasn't actually valid SQL for the database, but just an idealized form of
    SQL? I guess that would depend on what you wanted to do with the log
    information.

    PC2

    "John Senford" <JohnSenford@hotmail.com> wrote in message
    news:3bb1ea50@news.devx.com...
    >
    > I want to create logs of all sql statements being sent to a database.
    > There is no simple method (e.g. toString(), or toSQL()) provided by the

    Statement,
    > PreparedStatment, and CallableStatement interfaces therefore I have chosen
    > to create wrapper classes which contain a single

    Statement/Prepared.../Callable...
    > object and intercept all 'sql string building' operations so as to

    manually
    > maintain my own copy of the SQL string being used.
    >
    > However, there is a major flaw in this plan when it comes to the parameter
    > setting of a PreparedStatement. A long list of methods are provided to

    setting
    > every possible data type and known object type. The simple data types are
    > easy to 'stringify' and insert to my sql string......but what can I do

    about
    > the various objects that may be used as parameters???
    > (e.g. Object, java.sql.Date, java.sql.Time, java.sql.Timestamp etc.)
    >
    > So we can see, if a using class (that I'm not going to write) wants to use
    > PreparedStatement to carry out the operation then my job of logging their
    > sql statement becomes very difficult when they want to set a Date object
    > (for example) as one of the parameters in the sql string.
    >
    > How do I generate a (simple) log statment for that?!?!?!
    >
    > by what mechanism could I link a parameter in a simple textual log string
    > to an Object?
    >
    >
    > example log file:
    >
    > ---START---
    > 10/11/01 13:47:32
    > SELECT * FROM Book
    >
    > 10/11/01 13:49:55
    > SELECT BookID FROM Book WHERE Name=?
    >
    > ---END---
    >
    > now how could I go about linking the parameter for the Name field to an

    object
    > (let's pretend some complex Java class is used as the data type for the

    Name
    > field)???
    >
    >
    > Ideally I do not want to have take this linking approach, but there seems
    > to be no alternative (and how do you do it anyway).
    >
    > any ideas greatly appreciated...
    >
    > JohnSenford@hotmail.com




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center