Upgrade to Cold Fusion 8, Date time stamps in procedures not working

[Cainlord]

We recently upgraded to Cold Fusion 8, and some of the code we have is now not working correctly.

We generate the date time stamp, and are passing this value to a stored procedure.

Here is the parameter line:

<cfprocparam type="IN" cfsqltype=CF_SQL_TIMESTAMP VALUE=#startDateTime# DBVARNAME=@start>

Where startDateTime when outputted prior to this procedure being ran contains:

{ts '2010-02-09 00:00:00'}

This works on Cold Fusion 7.  However, on Cold Fusion 8, I am getting the following error:

Syntax error during implicit conversion of VARCHAR value '2010-02-09 00:00:00.000000000' to a SMALLDATETIME field

On top of the error in general, from what I can tell the value being passes is having the .000000000 portion added on that is not there in the variable.

Any ideas?

[-__cfSearching__-]

How exactly are you using the value inside the procedure? It sounds like you are using as a string, instead of a datetime object. Which might account for the syntax error.

<cfprocparam type="IN" cfsqltype=CF_SQL_TIMESTAMP

VALUE=#startDateTime# DBVARNAME=@start>

I thought dbVarName was deprecated?

[Cainlord]

The value is being passed in be used as a smalldatetime type.

This is legacy code, which is where the DBVARNAME portion is coming from.  the order however of this is correct as far as what the procedure is expecting so no issues there.

Just not sure why now that this same logic works fine on 7 is not working on 8?

[-__cfSearching__-]

Can you post the cfstoredproc code and procedure header (ie w/variable declarations)?

[Cainlord]

I'll see what I can do there.

To update, after reading up more on sybase as well as when to use cfsqltypes CF_SQL_TIMESTAMP versus CF_SQL_DATE, as we are using Sybase for the Database, I decided to try CF_SQL_DATE, and it appeared now to run.

So my question to build on this is did something change in ColdFusion 8 from 7 where CF_SQL_TIMESTAMP no longer works for sybase usage?

[-__cfSearching__-]

Oh, I thought you were using MS SQL 😉 With so many similarities between the two, it is probably good to mention the database type up front. Well .. with database questions that is always a good idea.  But truthfully I have no idea what the data types are for Sybase, sorry.

Message was edited by: -==cfSearching==-

[Cainlord]

yeah sorry about that.  I have been working primarily in PERL now so its been a bit coming back to cold fusion and this one is a stumper right out of the gate as to why it appears the type has changed.

[-__cfSearching__-]

Are you sure timestamp is the correct type for Sybase? That would be my guess, but Sybase is not listed on the main cfqueryparam matrix. So it is hard to tell.

[ilssac]

The change that is probably tripping you up is a change in the database *driver* between ColdFusion and your database.

Before it was just providing a date time value that contained year, month, day, hour, minute and seconds.

Now it is providing a date time value that containes year, month, day, hour, minute, seconds and milliseconds.

And your database datatype of smalldate does not allow the milliseconds.

[Cainlord]

Thanks for the info.

I got in touch with the Vendor for the upgrade package they provided and the driver inbetween was not updated, We are currently at hotfix 3, wonder if hotfix 4 would do anything?

[ilssac]

I'm curious what vender you are refering to.

With each new version of ColdFusion, the provier of ColdFusion at the time [Adobe nee Macromedia nee Allaire], inculde updated database drivers for ColdFusion to use.

[Cainlord]

Looks like the change over to a more appropriate sqltype has fixed the issue. Thanks for all the help everyone!