SQL Statement Logging

[John Senford]

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

[Paul Clapham]

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