| Bug #5032 | ResultSet as result of PreparedStatement doesn't deliver Boolean | ||
|---|---|---|---|
| Submitted: | 13 Aug 2004 16:01 | Modified: | 14 Aug 2004 1:51 |
| Reporter: | M Wensink | ||
| Status: | Closed | ||
| Category: | Connector/J | Severity: | S1 (Critical) |
| Version: | Snapshot of august 13 | OS: | Microsoft Windows (W2K) |
| Assigned to: | Mark Matthews | Target Version: | |
[13 Aug 2004 18: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 21:56]
M Wensink
Why does it work correctly if the PreparedStatement is replaced by an ordinary Statement?
[13 Aug 2004 22: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 :(
[14 Aug 2004 0: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();
}
[14 Aug 2004 1: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() ?
[14 Aug 2004 1: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 19:35]
M Wensink
OK, this solves the problem.
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