QueryNew(), JavaCast(), And Notes About Data Type Translation
Posted September 26, 2006 at 8:13 AM
The ColdFusion query object is such a cool part of ColdFusion. And, I'm not just talking about getting data from a database. I'm talking about building queries from scratch and maintaining them programmaticly. The only small rub I can see in the situation is the fact that the data types used when creating a query are different from the ones used to set query cell values.
When you create a query via QueryNew(), you have the option to pass in a list of column names and column types. Or, you can leave these blank and add individual columns to the query, again passing in a column name and data type (and an array of values). When doing either of these, the possible data type values are:
- 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)
So, for example, if I wanted to create a simple query using data types, I could do so like:
Launch code in new window » Download code as text file »
- <!--- Create girls query. --->
- <cfset qGirls = QueryNew(
- "name, hotness, is_curvey",
- "VARCHAR, INTEGER, BIT"
- ) />
Here we are explicitly telling the query to be instantiated with three columns. We are also telling it that the underlying data types of the columns are Varchar, Integer, and Bit. Now, here's the weird thing: ColdFusion is typeless, but query object data is not really typeless. Anyone who has manually altered a query and tried to run a query of queries probably has come across type conversion errors. That's because under the surface, these data types have to map to Java data types when running non-typeless SQL statements.
Now, how do you make sure that you set query cell data values with the proper type? You have to use JavaCast():
Launch code in new window » Download code as text file »
- <!--- Add row to query. --->
- <cfset QueryAddRow( qGirls ) />
-
- <!--- Set column values. --->
- <cfset qGirls[ "name" ][ 1 ] = JavaCast( "string", "Sarah Vivenzio" ) />
- <cfset qGirls[ "hotness" ][ 1 ] = JavaCast( "int", 10 ) />
- <cfset qGirls[ "is_curvey" ][ 1 ] = JavaCast( "boolean", 1 ) />
As you can see in this example, the Java data types are not exactly the ones that I chose for use in the QueryNew() call. That's because the set of Java data types is different:
- boolean
- int
- long
- float
- double
- string
- null
It's gets a bit sticky when you see that there is not a Java data type for every QueryNew() data type. Take Date and Time for instance. There is no date or time data Java data types listed. If you need to set a value into a DATE field, you can pass in any value that can be interpreted as a date. In ColdFusion (and other languages), dates have both a string representation and a numeric representation. Take the date 01-01-1980 for instance. That can be represented in a query by any of the following:
- {ts '1980-01-01 00:00:00'}
- 29221.0
- 29221
The first is the string time-stamp. The second is a float. The third is either an integer, long, or double. Therefore, when you need to set a date field manually, you can use any of the Java types string, int, long, float, or double.
But, that's for DATES only. If you need to set a date AND time value, it gets a bit tricky. Remember as a numeric value, the time is the decimal part of the floating point number. In that case you NEED the floating point number. No passing in int, long, or double as your data type. Only string or float will do.
I am not even sure how you would map the byte array QueryNew() data type from the Java types. But, I have not come across that yet, so no worries.
But any ways, be careful when you are setting query values. You cannot just throw typeless values into these cells. Remember, ColdFusion is typeless but SQL is not. Therefore, when you run a query of queries using SQL (a subset of), you CANNOT assume that ColdFusion will type everything out correctly. You have to be explicit.
Download Code Snippet ZIP File
Post Comment | Ask Ben | Other Searches | Print Page
Newer Post
Skin Spider : The "No Framework" Framework
Older Post
Restarting Windows Explorer Without Restarting Your Computer
Reader Comments
There are no comments posted for this web log entry.
