• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Strange JDBC PreparedStatements Problem

 
Andreas Schildbach
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 354
Eclipse IDE Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
what does return?
 
Andreas Schildbach
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Andreas,
Does the second one throw an exception or just return an empty resultset?
 
Andreas Schildbach
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It does not throw an exception. It returns an empty ResultSet.
 
Sonny Gill
Ranch Hand
Posts: 1211
IntelliJ IDE Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How is the id column defined?
 
Andreas Schildbach
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
describe mytable \g

+------------------------+--------------------------------------------------------------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+--------------------------------------------------------------------------+------+-----+---------------------+----------------+
| id | int(11) | | PRI | NULL | auto_increment
 
Pratik Lohia
Ranch Hand
Posts: 88
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Instead of ps.setString, try ps.setInt(1,1).
 
Andreas Schildbach
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic