Mysql 4.0.9 and Jconnector 3.0.5

[avaj avaj]

Hello.

Iam porting an application that previously run on InstantDB to Mysql.

This application makes use of transaction and isolation level(Read Committed). I previously tried using mysql 3.23.54 max with the old mm.mysql driver but encountered a problem in setting the Isolation level. I got hold of the new release of Mysql ver 4.0.9 and downloaded Connector J 3.0.5 for the driver.

I got pass the IsolationLevel exception I had before.. but somehow.. in a part of the program where I had to make a subquery inside a delete statement.. I ecountered an Exception.

I have this for my code:

String col = "4028809b:60a386:f302bed65b:-7df1"

sql = "DELETE FROM table1 WHERE timer IN (SELECT id FROM table2 WHERE col = ?)";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, col);
int rows = ps.executeUpdate();

Exception was...

java.sql.SQLException: Syntax error or access violation, message from server: "
You have an error in your SQL syntax. Check the manual that corresponds to your
MySQL server version for the right syntax to use near 'SELECT id FROM table2 WHERE col = '4028809b:60"

Somehow.. the string was truncated. I tried displaying the string prior to the sql statement and it return the correct value. I think It has to do with the driver's implemetation of ? and preparedStatement.setString() coz when I tried not using ? and setString it passed thru it correctly. However, there are numerous sql statement that makes use of ? and setString. So, changing them all is not a good idea.

Ist there a workaround for this? Can someone shed light regarding my error? Is this a bug in the driver? or am im just misled. YOur insight will be most welcome.

TIA





_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail


---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php