Strange JDBC PreparedStatements Problem

[Andreas Schildbach]

Hello everyone,

From yesterday to today, PreparedStatements with Parameters have stopped working: they do not return ResultSets any more.

The following code fragment returns 1 result set from my DB:

PreparedStatement ps = connection.prepareStatement("select * from mytable where id=1");
ResultSet rs = ps.executeQuery();
while(rs.next())
{
System.out.println(rs.getRow() + ": " + rs.getInt("id"));
}
rs.close();
ps.close();

This code is exactly the same, but the result set is empty. How can that be?

PreparedStatement ps = connection.prepareStatement("select * from mytable where id=?");
ps.setString(1, 1);
ResultSet rs = ps.executeQuery();
while(rs.next())
{
System.out.println(rs.getRow() + ": " + rs.getInt("id"));
}
rs.close();
ps.close();

I am using JDK 1.5.0_05, MySQL 4.1.12, Connector/J 3.1.10 (also tried with .11), Ubuntu 5.10.

Can anyone help me?

Regards,

Andreas

[Abhinav Srivastava]

what does

select * from mytable where id='1' 

return?

[Andreas Schildbach]

It returns no ResultSets as well.

select * from mytable where id=1 ==> 1 resultset
select * from mytable where id='1' ==> 1 resultset

select * from mytable where id=?; rs.setInt(1, 1); ==> 0 resultsets
select * from mytable where id=?; rs.setString(1, "1"); ==> 0 resultsets

[Jeanne Boyarsky]

Andreas,
Does the second one throw an exception or just return an empty resultset?

[Andreas Schildbach]

It does not throw an exception. It returns an empty ResultSet.

[Sonny Gill]

How is the id column defined?

[Andreas Schildbach]

describe mytable \g

+------------------------+--------------------------------------------------------------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+--------------------------------------------------------------------------+------+-----+---------------------+----------------+
| id | int(11) | | PRI | NULL | auto_increment

[Pratik Lohia]

Instead of ps.setString, try ps.setInt(1,1).

[Andreas Schildbach]

I also tried setInt(1, 1), it did not work.

It turns out that there is a bug with MySQL and server side PreparedStatements. There are several bug reports in the MySQL bug database, and some of them have been confirmed (and fixed in more recent versions). I filed a bug in the Ubuntu Bugzilla, and hope they will upgrade their distribution to 4.1.14 or 4.1.15.

Thanks for all of your help!

Regards,

Andreas