How to Print Out Bind Variables in Java Prepared Statements.

Ever been frustrated by the fact that when you use (as you should do!) bind variables within a java prepared statement you cannot see the actual values that get put into those bind variables? I know I have. For example if you had a class that had some JDBC calls that did the following….

Class.forName("com.mysql.jdbc.Driver");

connection = DriverManager.getConnection("jdbc:mysql://localhost/message_board", "root", "");

statement = connection.prepareStatement("select * from messages where id = ?");
statement.setLong(1,1L);

resultSet = statement.executeQuery();

There is no nice out the box way to find out what the ?’s will be resolved to. I’ve always wanted a method on PreparedStatement that would address this, but alas there still is none. Traditionally I would have done something like the following…


long id = 1L;
String sql = "select * from messages where id = ?"
logger.info(sql + "; ?1=" + id);

statement = connection.prepareStatement(sql);
statement.setLong(1,id);

Or write your own convoluted method for replacing each ? with the value which is prone to bugs and not being database agnostic.

When using an old jdbc based in-house framework this really wasn’t an issue for me, as we had such a method that was prone to bugs but for the most part it worked ok. Since then the work I have been doing has been much more hibernate based. This is where the issue came for me, you can turn on sql logging either by configuring your favourite logging framework to do so, or by turning on the hibernate.show_sql property, which will give you the following output…


select message0_.id as id2_, message0_.email_address as email2_2_, message0_.inserted_at as inserted3_2_, message0_.remote_host as remote4_2_, message0_.text as text2_ from messages message0_ where message0_.id=?

This sql was generated from the following hql

from Message where id = ?

Now apart from this sql being particularly hideous to look at, the question mark is still there, we have no way of possibly knowing what those bind variables were without printing them out at the time the hql is executed.

Enter p6spy.

I did a bit of googling about and found other people had the same issue. And there is an answer to this problem, and its called p6spy. P6spy is a jdbc driver that is a proxy for your real driver. I’ve only just started to use it, I believe it is capable of many cool things, but the one that I am interested in allows you to print out sql from prepared statements with – yep, you guessed it – the bind variables substituted for the real values.

You can download it from http://www.p6spy.com, it must be fairly stable as the last release was in 2003 (I’m so annoyed that I didn’t know about this earlier). There seems to be a little problem with their website as when you click download you get an error while trying to fill in or ignore their survey. Don’t panic, once you’ve ignored the survey go back to the main page and click download again and the files will be there waiting for you to download.

So how do we configure this little beauty? Well the download is p6spy-install.zip, unzip this somewhere safe (and keep in mind that all the files are in the root of the zip file, so you might want to create a directory to unzip it to first). The files we’re interested in are p6spy.jar and spy.properties. Copy the jar file into the lib folder of your project and copy the spy.properties file into somewhere that will be on the classpath.

If you look inside spy.properties you will see that it’s really very well documented and tells you all you really need to know on how to configure it. Although one thing to note in there is that they have said that the real driver you configure in your application should be set to com.p6spy.engine.P6SpyDriver which is actually incorrect, it should be com.p6spy.engine.spy.P6SpyDriver, the first one doesn’t exist and you will get a ClassNotFoundException.

So what I did… In my spy.properties I set the realdriver…

# mysql Connector/J driver
realdriver=com.mysql.jdbc.Driver

(note by default the open source mysql driver is uncommented which was a gotcha for me at first, so I commented it out, and uncommented the above)

Next I altered my code in the first example to the following…

Class.forName("com.p6spy.engine.spy.P6SpyDriver");

connection = DriverManager.getConnection("jdbc:mysql://localhost/message_board", "root", "");

statement = connection.prepareStatement("select * from messages where id = ?");
statement.setLong(1,1L);

resultSet = statement.executeQuery();

And lo and behold in the root of my eclipse project on the file system a file called spy.log has appeared. and in it the following…

1217374504582|6|0|statement|select * from messages where id = ?|select * from messages where id = 1
1217374504595|-1||resultset|select * from messages where id = 1|email_address = myemail@nospam.com, id = 1, inserted_at = 2008-07-29 00:00:00.0, remote_host = localhost, text = Thank you for showing me the wonders of p6spy

There is lots of other stuff in the log file as well but as you can see its very handy, as it shows you the statement with question marks and with the real values substituted, and also shows you the results set which is passed back 🙂 Beautiful lol.

And the hql of

from Message where id = ?

is output as:


1217374913350|5|2|statement|select message0_.id as id2_, message0_.email_address as email2_2_, message0_.inserted_at as inserted3_2_, message0_.remote_host as remote4_2_, message0_.text as text2_ from messages message0_ where message0_.id=?|select message0_.id as id2_, message0_.email_address as email2_2_, message0_.inserted_at as inserted3_2_, message0_.remote_host as remote4_2_, message0_.text as text2_ from messages message0_ where message0_.id=1
1217374913369|-1||resultset|select message0_.id as id2_, message0_.email_address as email2_2_, message0_.inserted_at as inserted3_2_, message0_.remote_host as remote4_2_, message0_.text as text2_ from messages message0_ where message0_.id=1|email2_2_ = myemail@nospam.com, remote4_2_ = localhost

Still ugly but at least I can now see what the bind variables are going to be 🙂

You can also configure it so it comes out in your normal logging file, the spy.properties file details how you do that.

Oh how I wish I’d have known this 4 years ago.

Technorati Tags: , , , , ,,,

Advertisements

How to get Logitech QuickCam Ultra Vision working in Ubuntu

I have recently removed Windows from my Dell Inspiron 9400, and replaced it with Ubuntu, which is a flavour of Linux (or GNU/Linux if you’re picky).

I was surprised at how easy the installation was, almost everything worked out of the box, wireless, all the media keys and 95% of the fn keys. (I’m having trouble with fn+f8 and fn+10 not working correctly)

Anyway I am very happy with the Installation, I love the effects you have on the windows, it looks really pretty.

I wanted to get my Logitech QuickCam Ultra Vision working with Ubuntu, so I could use it on Skype. I plugged it in and nothing happened…ho hum… on windows it would have picked it up and installed it, and asked for the driver cd, but anyway, after a bit of googling around I found this thread, and I followed the advise of Andrew Barber on there.

Make sure first you have all the tools for the job:

sudo apt-get install linux-headers-`uname -r` linux-restricted-modules-`uname -r` build-essential subversion

Once you have got all them you will need to use subversion to get the driver from the svn repo..

svn checkout http://svn.berlios.de/svnroot/repos/linux-uvc/

Then you need to compile and install

cd linux-uvc/linux-uvc/trunk
make
sudo make install

Plug the camera in and take a look at dmesg. It may [hopefully] give you the device listing for it… eg /dev/video1

Point your application at that device and see if it works.

I checked dmseg to see what had happened…


$ dmesg | grep -i vid
[    0.000000] BIOS-provided physical RAM map:
[    8.445959] Boot video device is 0000:00:02.0
[   26.441798] input: Video Bus as /devices/LNXSYSTM:00/device:00/PNP0A03:00/device:2b/LNXVIDEO:00/input/input7
[   26.489145] ACPI: Video Device [VID] (multi-head: yes  rom: no  post: no)
[   26.497079] input: Video Bus as /devices/LNXSYSTM:00/device:00/PNP0A03:00/LNXVIDEO:01/input/input8
[   26.537089] ACPI: Video Device [VID1] (multi-head: yes  rom: no  post: no)
[   26.537246] input: Video Bus as /devices/LNXSYSTM:00/device:00/PNP0A03:00/LNXVIDEO:02/input/input9
[   26.585000] ACPI: Video Device [VID2] (multi-head: yes  rom: no  post: no)
[ 2902.096243] Linux video capture interface: v2.00
[ 2902.175522] uvcvideo: Found UVC 1.00 device <unnamed> (046d:08c9)
[ 2902.189674] usbcore: registered new interface driver uvcvideo
[ 2902.189683] USB Video Class driver (v0.1.0)

After that it worked in skype, I needed to set the sound in device to be the microphone of the web cam in the options of skype…

Sound In:   USB Device 0x46d:0x8c9 (hw:U0x46d0x8c9,0)
Sound Out:  Default device(default)
Ringing:    Default device(default)

and in the video settings of skype…

Select webcam: UVC Camera (0x46d:0x8c9) (/dev/video0)

I am still currently having a weird issue where it seems that skype takes over the sound of all other applications. If I try and play some music after a skype call then it won’t play until I reboot. Also it seems I’m allowed a max of 1 skype call before the sound in skype doesn’t work, and therefore doesn’t let me make or receive anymore calls. Again it only seems at the moment that a reboot will fix it. For the moment I will live with this as I don’t use skype that often anyway. (If anyone has any advice for this part please leave a comment)

Technorati Tags: , , , ,,