MySQL Bugs: #5614: New connector retrieve incorrect "tinyint" field type in JRun4/ColdFusion

Bug #5614	New connector retrieve incorrect "tinyint" field type in JRun4/ColdFusion
Submitted:	16 Sep 2004 18:43	Modified:	21 Dec 2004 0:17
Reporter:	Claude Morin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.0.15-ga and 3.1.4-beta	OS:	Any (any)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
I just installed the latest MySQL Connector/J (mysql-connector-java-3.0.15-ga-bin.jar) in ColdFusion MX 6.1 and found a problem with MySQL "tinyint" field. I then tried the beta connector mysql-connector-java-3.1.4-beta-bin.jar and it did the same thing. When I reverted back to mysql-connector-java-3.0.14-production-bin.jar, everything worked normally again.

Strangely, CF treats the field as being a boolean True/False field instead of a tinyint.

How to repeat:
I created a simple table to reproduce the problem: table name "TestTiny" with only one field "TinyIntg" of Type tinyint, Length 1, Decimals 0, Not Allowing Null and Unsigned. I have 10 records, with "TinyIntg" ranging from 0 to 9, in the "TestTiny" table.

If I use CFQUERY to "SELECT * FROM TestTiny" and then return the TinyIntg field, I get:

"false,true,false,false,false,false,false,false,false,false"

If I do the query using a java object (see code below), the return resultSet is:

0,1,2,3,4,5,6,7,8,9

Here is the code:

<cfscript>
class = CreateObject("java", "java.lang.Class");
class.forName("com.mysql.jdbc.Driver");
dm = CreateObject("java", "java.sql.DriverManager");
con = dm.getConnection("jdbc:mysql://localhost/database?user=User&password=Password");
st = con.createStatement();
sql_string = "SELECT * FROM TestTiny";
resultSet = st.executeQuery(sql_string);
Test1 = CreateObject("java", "coldfusion.sql.QueryTable").init(resultSet);
con.close();
</cfscript>
<cfdump var="SELECT * FROM TestTiny">
<br><cfdump var="#ValueList(Test1.TinyIntg)#">
<cfdump var="#Test1#">
<P><CFQUERY NAME="Test2" DATASOURCE="database">SELECT * FROM TestTiny</CFQUERY
><CFDUMP VAR="SELECT * FROM TestTiny"
><BR><CFDUMP VAR="#ValueList(Test2.TinyIntg)#"
><CFDUMP VAR="#Test2#">

RESULTS:

SELECT * FROM TestTiny
0,1,2,3,4,5,6,7,8,9

row#  TINYINTG
 1     0
 2     1
 3     2
 4     3
 5     4
 6     5
 7     6
 8     7
 9     8
10     9

SELECT * FROM TestTiny
false,true,false,false,false,false,false,false,false,false

row#  TINYINTG
 1     0
 2     1
 3     0
 4     0
 5     0
 6     0
 7     0
 8     0
 9     0
10     0

This regression is because we need to be able to detect a 'boolean' type, which the MySQL server silently converts to 'TINYINT(1)' in order to be JDBC-compliant.

We will in the next version offer the ability to turn this detection off, however your only workaround currently is to not use TINYINT(1) fields.

Thank you so much Mark!

Boy, you are fast to answer! I converted all my tinyint(1) fields into tinyint(2) fields and everything now works as it should.

Claude

See the connection property 'tinyint1IsBit'.