QueryNew(), JavaCast(), And Notes About Data Type Translation
Posted September 26, 2006 at 8:13 AM by Ben Nadel
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:
- <!--- 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():
- <!--- 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.
Reader Comments
Ben,
I have been trying to pass in a Date/Time that java will accept. In java I have
private Timestamp _requiredDeliveryDate;
and in CF I have tried several things and the latest being:
<cfset requiredDeliveryDate = dateformat(form.po_requiredDeliveryDate,"yyyy-mm-dd") & " " & timeformat(form.po_requiredDeliveryDateTime,"HH:mm:ss")>
<cfset myDate = JavaCast("float",requiredDeliveryDate)>
<cfset poObject.setRequiredDeliveryDate(myDate)>
But java will not accept and date/time I throw at it from cf.
Do you have any other suggestions of what I can try?
Thanks
What I ended up with was passing the date/time in as a string to a method in java that will convert it to a timestamp and then set the correct variable in java. Not the way I would have liked but it's working.
@Dan,
Since this post, I've started to rely on parseDateTime() as a way to create actual date/time stamps. Also, now() returns a date/time object:
<cfset dt = now() />
<cfdump var="#dt.getClass().getName()#" />
... gives you:
coldfusion.runtime.OleDateTime
This is also the data type that parseDateTime() returns. Also, if you get the super class of the date/time object:
<cfdump var="#dt.getClass().getSuperClass().getName()#" />
... you get the following:
java.util.Date
I don't know if this can be cast to the "timestamp" data type easily, but just some thoughts on it. Good to know that the time-as-string approach worked for you. Thanks for the insight.
