*
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 Java 8 in Action this week in the Java 8 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
internet detective
Marshal

Joined: May 26, 2003
Posts: 29287
    
140

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
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Strange JDBC PreparedStatements Problem
 
Similar Threads
Will Select Statement work with Prepared Statment?
java.lan g.AbstractMet hodError: oracle .jdbc.driver .OracleCon nection.prep areStat
Help me get started
How to get no of Rows ?
BMP -last inserted record alone gets fetched