aspose file tools*
The moose likes JDBC and the fly likes Strange JDBC PreparedStatements Problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Strange JDBC PreparedStatements Problem" Watch "Strange JDBC PreparedStatements Problem" New topic
Author

Strange JDBC PreparedStatements Problem

Andreas Schildbach
Ranch Hand

Joined: Jan 22, 2003
Posts: 34
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

Joined: Nov 19, 2002
Posts: 349

what does return?
Andreas Schildbach
Ranch Hand

Joined: Jan 22, 2003
Posts: 34
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

Joined: May 26, 2003
Posts: 31057
    
232

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


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Andreas Schildbach
Ranch Hand

Joined: Jan 22, 2003
Posts: 34
It does not throw an exception. It returns an empty ResultSet.
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

How is the id column defined?
Andreas Schildbach
Ranch Hand

Joined: Jan 22, 2003
Posts: 34
describe mytable \g

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

Joined: May 05, 2005
Posts: 88
Instead of ps.setString, try ps.setInt(1,1).
Andreas Schildbach
Ranch Hand

Joined: Jan 22, 2003
Posts: 34
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Strange JDBC PreparedStatements Problem