• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Strange JDBC PreparedStatements Problem

 
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
 
Ranch Hand
Posts: 354
Eclipse IDE Oracle Java
  • 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
 
author & internet detective
Posts: 40169
812
Eclipse IDE VI Editor Java
  • 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.
 
Ranch Hand
Posts: 1211
Mac IntelliJ IDE
  • 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
 
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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
    Bookmark Topic Watch Topic
  • New Topic