CFQueryparam Matrix for MySQL 5

As I predominately use MS SQL, I frequently end up having to search for the correct cfsqltypes when using cfqueryparam and MySQL. While I am sure there is an unofficial matrix out there somewhere, I never manage to find it when I need it. So I decided to create my own as a point of reference.

Not being an MySQL guru, I spent some time reviewing the MySQL 5.0 manual and the JDBC references to get a better understanding of the data types and limits, both in MySQL and Java. First the matrix and then I will explain some of the decisions behind it. The matrix should be pretty accurate. But corrections are always welcome.

Note: The mappings are based on tests with CF9, MySQL 5.1.42 and Connector/J 5.1.7. There are some significant differences in the various versions of MySQL and Connector/J drivers. So if you are using different versions, your mileage may vary.

ColdFusion 9	MySQL 5.1.42 Connector/J 5.1.7
CF_SQL_ARRAY	-
CF_SQL_BIGINT	bigint (signed), int (unsigned)
CF_SQL_BINARY	binary,tinyblob
CF_SQL_BIT	bit, bool
CF_SQL_BLOB	blob
CF_SQL_CHAR	char
CF_SQL_CLOB	-
CF_SQL_DATE	date
CF_SQL_DECIMAL	decimal
CF_SQL_DISTINCT	-
CF_SQL_DOUBLE	double, double precision, real
CF_SQL_FLOAT	-
CF_SQL_IDSTAMP	-
CF_SQL_INTEGER	mediumint (signed and unsigned), int (signed)
CF_SQL_LONGVARBINARY	mediumblob,longblob,tinyblob
CF_SQL_LONGVARCHAR	text,mediumtext,longtext
CF_SQL_MONEY	-
CF_SQL_MONEY4	-
CF_SQL_NULL	-
CF_SQL_NUMERIC	numeric, bigint(unsigned)
CF_SQL_OTHER	-
CF_SQL_REAL	float
CF_SQL_REFCURSOR	-
CF_SQL_SMALLINT	smallint (signed or unsigned), tinyint (signed)
CF_SQL_STRUCT	-
CF_SQL_TIME	**
CF_SQL_TIMESTAMP	datetime,timestamp
CF_SQL_TINYINT	tinyint (unsigned)
CF_SQL_VARBINARY	varbinary
CF_SQL_VARCHAR	varchar, tinytext, enum, set

Data Types 101
Since cfsqltypes are essentially a wrapper of java.sql.Types, I started with the MySQL documentation on Java, JDBC and MySQL Types and worked from there. The mappings were not always as straight-forward as I expected. In particular the mappings of the numeric data types.

In MySQL the range of allowed values can vary depending on whether the column is signed or unsigned. So unlike MS SQL, more than one cfsqltype was required to represent all possible values for certain types.

The MySQL INT type is a prime example. If the INT is signed, the range of allowed values is -2147483648 through 2147483647. For an unsigned INT,the range is 0 through 4294967295. Why does this matter? As with most things in CF, it relates back to java.

When you use CF_SQL_INTEGER you are using the java sql type INTEGER which "represents a 32-bit signed integer value ranging between -2147483648 and 2147483647". So obviously it does not have the capacity to represent the upper ranges of an unsigned INT. For values larger than 2147483647 you would need to use CF_SQL_BIGINT instead.

Decisions, Decisions ..
So why not just use CF_SQL_BIGINT for both types? Well, technically you could. But then you lose out on cfqueryparam's built in validation for signed INT's. That is one of the reasons I decided to use separate cfsqltypes for signed and unsigned numbers. Another reason is that the mappings are more intuitive this way, and more closely aligned with the standards (in my opinion). So you get the benefits of as much validation as possible, while using the smallest sql type necessary. Here is a quick breakdown of the various types and allowed ranges.



TINYINT
ColdFusion enforces the standard range: 0 to 255

CF_SQL_SMALLINT - Signed columns (Min: -128 / Max: 127)
CF_SQL_TINYINT - UnSigned columns (Min: 0 / Max: 255)

SMALLINT
ColdFusion does not stricly enforce the standard range: -32768 to 32767.
So you can use a single type for both signed and unsigned columns.

CF_SQL_SMALLINT - Signed (Min: -32768 / Max: 32767)
CF_SQL_SMALLINT - Unsigned (Min: 0 / Max: 65535)

MEDIUMINT
This seems to be a non standard size.The closest mapping is INTEGER

CF_SQL_INTEGER - Signed (Min: -8388608 / Max: 8388607)
CF_SQL_INTEGER - Unsigned (Min: 0 / Max: 16777215)

INT
ColdFusion enforces the standard range: -2147483648 to 2147483647.

CF_SQL_INTEGER - Signed (Min: -2147483648 / Max: 2147483647)
CF_SQL_BIGINT - Unsigned (Min: 0 / Max: 4294967295)

BIGINT
ColdFusion enforces the standard range: -9223372036854775808 to 9223372036854775807. I decided to use CF_SQL_NUMERIC (with a scale of 0), for unsigned values. Though CF_SQL_DECIMAL would probably work as well.

CF_SQL_BIGINT - Signed (Min: -9223372036854775808 / Max: 9223372036854775807)
CF_SQL_NUMERIC - Unsigned (Min: 0 / Max: 18446744073709551615)



Tonight at Comedy Central: Aproximate Numeric Types
Suffice it to say the collective documentation on approximate numeric types is a bit like a Who's on First routine. From everything I have read, the relationship between the java sql types and the MySQL data types is as follows: A REAL is a MySQL FLOAT, a DOUBLE is a MySQL REAL, and a FLOAT is .. well none of the MySQL data types corresponds to a java FLOAT. Are you with me so far? No? Good. Why should I be the only one with a headache.

The confusion is due in part to what some of the older java api's charmingly call "a possibly misguided attempt at consistency with previous database APIs" (in relation to the FLOAT type). Now throw in the fact that same terms have different meanings, depending on the context, and things get even murkier.

Conceptually, the easiest way to think of it is that approximate numbers fall into two categories: single and double precision. To trivialize the differences, both types can store large floating point numbers. But as the names imply, double precision can represent larger numbers than single precision. Pretty simple, right?

Unfortunately, from everything I have read, the mappings between java.sql.Types and MySQL data types are not nearly that logical. In fact they almost seem reversed. The java.sql.Types use REAL to represent single precision and FLOAT/DOUBLE to represent double precision numbers. Whereas MySQL uses FLOAT for single precision and REAL/DOUBLE for double precision numbers. See what I mean?

So why am I explaining all this? (Just to torture you.) But seriously, in part to explain why the cfqueryparam mappings for these types are likely different than what you expected. Also, to explain why I avoided any use of CF_SQL_FLOAT. The java api's apologetically mention the sql type FLOAT is often a source of confusion. The recommendation is that you not use this type, but use DOUBLE instead. FLOAT and DOUBLE are equivalent. (After reading the documentation, I wholeheartedly concur).

So without further ado, here are the mappings for the approximate types:



FLOAT
ColdFusion enforces the standard range: -3.40282347E+38F to 3.40282347E+38F. Note: All ranges are approximate and may not be exact.

CF_SQL_REAL - Signed (Min: -3.40282347E+38F / Max: 3.40282347E+38F)
CF_SQL_REAL - Unsigned (Min: 0 / Max: 3.40282347E+38F)

DOUBLE, DOUBLE PRECISION, REAL (Synonyn for DOUBLE)
ColdFusion enforces the standard range: -1.79E+308 to 1.79E+308. Note: All ranges are approximate and may not be exact.

CF_SQL_DOUBLE - Signed (Min: -1.79E+308 / Max: 1.79E+308)
CF_SQL_DOUBLE - Unsigned (Min: 0 / Max: 1.79E+308)



Open Questions
The one data type I could not figure out how to properly map was time. At least not easily. From a few cursory searches it seems problematic. As I do not make much use of this data type anyway, I decided not investigate it further. If anyone has figured out the correct mapping for this type, feel free to enlighten me.


Final Conclusions: Does the CFSQLType Really Matter?
As you can see from the list of types and ranges above there is some definite overlapping. For example, the number 126 could be submitted using CF_SQL_SMALLINT, CF_SQL_INTEGER or even CF_SQL_BIGINT. Obviously the latter two would be overkill for a TinyInt column. But since all of them work, does the type really matter?

Yes. Obviously the type must have the capacity to store the value you are submitting. If it is too small, the query may fail or the value may be truncated. But even if you select a type that has ample capacity to store your value, that value may be too large or invalid for your target column. So selecting the correct cfsqltype gives you an extra layer of validation in CF, before the SQL is ever submitted to the database. Finally, the selected type should interpret the value correctly according to the target column. To use an ridiculous example, you could insert the number zero (0) into a VARCHAR column, using CF_SQL_DATE. Technically, it will work. But the value actually inserted into the database would not be zero (0). So selecting the proper cfsqltype for each column does matter.

As always, comments and corrections are welcome.