Bug #5032 ResultSet as result of PreparedStatement doesn't deliver Boolean
Submitted: 13 Aug 2004 14:01 Modified: 13 Aug 2004 23:51
Reporter: M Wensink Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S1 (Critical)
Version:Snapshot of august 13 OS:Microsoft Windows (W2K)
Assigned to: Mark Matthews CPU Architecture:Any

[13 Aug 2004 14:01] M Wensink
Description:
After execution of executeQuery() on a PreparedStatement, the ResultSet does not contain a value of type Boolean. Instead an Integer value is returned.

How to repeat:
Make a table containing a Boolean. For example:

CREATE TABLE `user_tbl` (
  `User_ID` INTEGER,
  `User_locked` BOOLEAN
);

Execute the query "SELECT * FROM User_tbl WHERE User_ID = ?" using a PreparedStatement via executeQuery();
Then execute the following code:
Map m = resultSet.getRows()[0];
Boolean b = ((Boolean) m.get ("User_locked")).booleanValue());

The result is: ClassCastException: Java.lang.Integer

Suggested fix:
None
[13 Aug 2004 16:32] Mark Matthews
If your code is calling getObject() on the result set, it won't work, as 'BOOLEAN' is currently an 'alias' for TINYINT(1) in MySQL (i.e. there is no true 'boolean' type in MySQL). If you call getBoolean() on the result set, it will work, as the driver does a type conversion.

You can see why this happens if you look at the columns that _actually_ get created for your table:

mysql> show columns from user_tbl\G
*************************** 1. row ***************************
  Field: User_ID
   Type: int(11)
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 2. row ***************************
  Field: User_locked
   Type: tinyint(1)
   Null: YES
    Key:
Default: NULL
  Extra:
2 rows in set (0.00 sec)

mysql>
[13 Aug 2004 19:56] M Wensink
Why does it work correctly if the PreparedStatement is replaced by an ordinary Statement?
[13 Aug 2004 20:53] Mark Matthews
You've hidden the actual JDBC call behind your class structure, so until I see _how_ you're calling ResultSet.getFoo(), I couldn't possibly tell you :(
[13 Aug 2004 22:58] M Wensink
My code is like this:

Result executeQuery ( ) throws SQLException
{
   PreparedStatement pstmt = null;
   Statement stmt = null;
   Result result = null;
   ResultSet rs = null;
   try
   {  if (values != null && values.size() > 0)
      {  // Use a PreparedStatement and set all values:
         pstmt = conn.prepareStatement (sqlValue);
         setValues (pstmt, values);
         rs = pstmt.executeQuery();
      }
      else
      {  // Use a regular Statement:
          stmt = conn.createStatement();
          rs = stmt.executeQuery (sqlValue);
      }
      result = ResultSupport.toResult (rs);
   }
   finally
   { .......  
   }
   return result;
}

Map [ ] getUsers (Connection conn) throws SQLException
{
   sqlValue = "SELECT * FROM User_tbl";
   values = null;
   Result result = executeQuery();
   if (result == null || result.getRowCount() == 0)
   {  // User not found
      return null;
   }
   return result.getRows();
}
[13 Aug 2004 23:23] Mark Matthews
Thank you for pasting your code in the report, but unfortunately it still doesn't show which getter method you're calling on ResultSet that shows the issue.

Does your code call ResultSet.getBoolean() or ResultSet.getObject() ?
[13 Aug 2004 23:51] Mark Matthews
Okay, found the issue. The driver _does_ detect the 'special' case of TINYINT(1), and considers it a 'BIT' type, however the test was not done in the case of binary-encoded result sets that are returned from server-side prepared statements.

This fix will be in tonight's nightly snapshot after 00:00 GMT, see http://downloads.mysql.com/snapshots.php
[14 Aug 2004 17:35] M Wensink
OK, this solves the problem.