SQL Data Types Returned From GetMetaData() Are Not Available For QueryNew() And Why That Should Scare You
Posted November 15, 2006 at 9:40 AM by Ben Nadel
When you dump out a ColdFusion query's meta data:
- <!--- Get the query meta data. --->
- <cfdump var="#GetMetaData( qData )#" />
... you get information about the columns in the query. What I just noticed though is that the column data types returned from a standard SQL query do not line up with the data types that are available for ColdFusion's QueryNew() method for manually creating queries.
When I do this for my blog:
- <!--- Get a single record for the demo. --->
- <cfquery name="qTopEntry" datasource="...">
- SELECT TOP 1
- *
- FROM
- blog_entry
- </cfquery>
-
- <!--- Display meta data. --->
- <cfdump var="#GetMetaData( qTopEntry )#" />
... among other column data types, I get:
INT
VARCHAR
TEXT
TINYINT
DATETIME
This completely lines up with the data types as defined in MS SQL Server. However, let's take a look at what is available for the QueryNew() method (as taken out of the help files):
Integer: 32-bit integer
BigInt: 64-bit integer
Double: 64-bit decimal number
Decimal: Variable length decimal, as specified by java.math.BigDecimal
VarChar: String
Binary: Byte array
Bit: Boolean (1=True, 0=False)
Time: Time
Date: Date (can include time information
Notice that there is no TEXT, TINYINT, or DATETIME data types available.
Now, most of the time this has no bearing on how we do anything at all. But, the second we start messing with queries manually, it's a whole new ball game. There are a lot of ColdFusion user defined functions (UDFs) out there that append queries or select random rows from queries or simulate queries. These generally require setting query cells manually, and this can cause runtime errors if the result query has strange data type values.
The ultimate problem is that the ColdFusion query is a custom Java result set of some sort underneath the covers. And while ColdFusion is very lenient about data types, Java is just the opposite. Stuff better be the correct data type or else. When you set a value in query cell ColdFusion does its best to "guess" which Java data type it should cast to, but a lot of the time it simply doesn't guess correctly. This will cause errors during a future query or query.
To overcome this, it is best practice to use JavaCast() when manually setting a query cell:
- <!--- Set ID to 1. --->
- <cfset qData[ "id" ][ 1 ] = JavaCast( "int", 1 ) />
But this raises another issue. The data types available for JavaCast() are:
boolean
int
long
float
double
string
null
As you can see, these data types are different from the QueryNew() data types.
To get all of this to play nicely, you need to start mapping your SQL data types to Query data types and Query data types to Java data types. Then, when you mess with a query, you need to pass in the appropriate data type based on the mapped association. This already sounds confusing right? Well it should, because it's fairly complicated.
And furthermore, you have to account for different databases. For instance, I might have a query that comes back from MS SQL Server with the data types:
BIT
MONEY
SQL_VARIANT
Are these universal data types across databases? I am guessing they are not. This means that your data mapping has to map data types for different databases. And what about custom data types? Don't some databases allow for custom data types? If not now, then maybe in the future. The problem is a bit out of hand.
What really needs to happens is ColdFusion needs to somehow force the casting of passed in data types to the underlying Java data types of the query. But this is almost impossible. If you look at my post on using the query object as a complex object iterator, you will see that I am passing in custom ColdFusion objects to query cells. How can this be accommodated? Or is this a hack that will eventually be killed.
And, this raises another issue: coupling. Your data maps now have to be directly tied to databases. This is obvious (I mean isn't that what data mapping is anyway?). But think about it, if your application gets a query from somewhere and then messes with it, your application is now coupled to the database implementation.
Now, you might think I am crazy right? Out of my gourde? Well, check this out:
- <cfquery name="qTest" datasource="...">
- SELECT TOP 1
- id
- FROM
- blog_entry
- </cfquery>
-
-
- <!--- Add a row. --->
- <cfset QueryAddRow( qTest, 1 ) />
-
- <!---
- Set the value and let ColdFusion do
- the data type casting.
- --->
- <cfset qTest[ "id" ][ 2 ] = 3 />
-
-
- <!--- Order by the id. --->
- <cfquery name="qOrderBy" dbtype="query">
- SELECT
- id
- FROM
- qTest
- ORDER BY
- id ASC
- </cfquery>
This throws the error:
null null <br>The error occurred on line 113.
The problem is that ColdFusion stores "3" as a string. It then passes this string into a field (id) which is actually an "int" in the database. Then, when you do the order by, the sorting method gets screwed because now it is sorting apples and oranges ("string"s and "int"s). To fix this, you could do:
- <!---
- Set the value and tell ColdFusion how to
- cast the data for the data type.
- --->
- <cfset qTest[ "id" ][ 2 ] = JavaCast( "int", 3 ) />
This would work perfectly.
Are you seeing what is going on here? I need to know that the data type is an "INT" before I set the value. That means that this code is HIGHLY COUPLED to the database implementation.
Now, I may be taking this waaaaaay too seriously, but please, this should be throwing red flags about using any UDFs that manipulate queries.
Reader Comments
Ben - You can also use the cfqueryparam sql types when using queryNew - they would likely match up better, no?
Todd,
AWESOME post. I was totally not aware of this. I didn't realize that CFSQLTYPES were available for use. This will certainly help some of the data mapping. Thank you very much for posting this.
However, there is still the issue of coupling of the code to the database implementation. Not every CFSQLTYPE maps to a database's data type. For instance, MS SQL Server uses CFSQLTIMESTAMP properly but will sometimes error out when using CFSQLDATE. Not that this is a huge problem....
I am more curious to see what the Framework people have to say about this. I know zero about frameworks, but I am pretty sure that most frameworks attempt to de-couple the database implementation to the rest of the code... or maybe I am way off here.
No prob... I KNOW i've seen a udf somewhere that mapped the cf sql types to the proper db type, but I can't seem to find it.
Maybe someone else knows?
That would be cool to see. I do it on a need-to-know basis for my code, but it would be really cool to see how someone does it on a "global" scale.
Check this out - there is a matrix here that shows the mappings:
Todd,
Excellent link... but as you can see, not all databases support all the differnt CFSQL... types. Take that and talk about casting to Java data types, and things are still complicated.
