cfqueryparam and datetime columns with timezone

[Antonio Perchinumio]

I have a problem in migration from Coldfusion 7 to Railo 3.2, the following statement work fine in Colfdfusion 7: "<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#" />". But in Railo an exception is launched:  the value [{ts '2011-09-01 10:30:01'}] is too large, defined maxlength is [19] but length of value is [26].

The problem is the data type datetime (I use Mysql 5) that was saved correctly in Coldfusion 7, but Railo throws the exception above.

This example work fine in Coldfusion 7:
  <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#" />

This example work fine in Railo:
 <cfqueryparam cfsqltype="cf_sql_timestamp" value="#CreateODBCDate(now())#" />

But in the last example I have to edit hundreds of files to solve problem. Can it be resolve with some particular configuration?

Any suggestion?

[Andrea Campolonghi]

Antonio,

what kind of datatype has your mysql table for the field you want to save?

Andrea

Andrea Campolonghi
and...@getrailo.org

[Antonio Perchinumio]

Hi Andrea,

The field is datetime in my table.

On Oct 10, 4:40 pm, Andrea Campolonghi <and...@getrailo.org> wrote:
> Antonio,
>
> what kind of datatype has your mysql table for the field you want to save?
>
> Andrea
>
> On Oct 10, 2011, at 4:34 PM, Antonio Perchinumio wrote:
>

> > I have a problem in migration from Coldfusion 7 to Railo 3.2, the following statement work fine in Colfdfusion 7: "<cfqueryparamcfsqltype="cf_sql_timestamp" value="#now()#" />". But in Railo an exception is launched:  the value [{ts '2011-09-01 10:30:01'}] is too large, defined maxlength is [19] but length of value is [26].
>
> > The problem is the data typedatetime(I use Mysql 5) that was saved correctly in Coldfusion 7, but Railo throws the exception above.

>
> > This example work fine in Coldfusion 7:

> >   <cfqueryparamcfsqltype="cf_sql_timestamp" value="#now()#" />

>
> > This example work fine in Railo:

> >  <cfqueryparamcfsqltype="cf_sql_timestamp" value="#CreateODBCDate(now())#" />

[Antonio Perchinumio]

[Todd Rafferty]

I use cfsqltype="CF_SQL_TIMESTAMP" all the time with DateTime and it just works. You said you're running on Railo 3.2, what version of 3.2 are you running and have you upgraded to the latest build (Railo 3.3.1.000 was released) to see if it's still an issue?

--
~Todd Rafferty
Volunteer Community Manager
Railo Server - Open Source
----

Get Railo: http://getrailo.org/
Write Documentation: http://wiki.getrailo.org/

Report Bugs: https://issues.jboss.org/browse/RAILO
Request Features: https://railo.uservoice.com/

[Antonio Perchinumio]

Yes the datetime in mysql has always worked fine with cf_sql_timestamp in cfqueryparam for Coldfusion. The problem seems to be that the Railo passes the date to cfqueryparm with the timezone {ts '2011-01-01 00:00:00 '} and the tag not execute any transformation. If I use the CreateODBCDate function on the value of cfqueryparam, it work with no problem.

I'll try to update Railo to the latest release.

Thanks.

[Ronan Lucio]

Antonio,

Why not just using NOW() function from SQL?

SET my_column = NOW()

Ronan

2011/10/10 Antonio Perchinumio <antoniope...@gmail.com>

[Andrea Campolonghi]

Antonio.

The point is that Railo is doing the correct thing so I do not think exists a "clean" workaround for this upgrade.

Andrea

Andrea Campolonghi

and...@getrailo.org

[Antonio Perchinumio]

The statement above is only for example, the date value in real
application is passed from client call.

On 11 Ott, 14:28, Ronan Lucio <ronanlu...@gmail.com> wrote:
> Antonio,
>
> Why not just using NOW() function from SQL?
>
> SET my_column = NOW()
>
> Ronan
>

> 2011/10/10 Antonio Perchinumio <antonioperchinu...@gmail.com>

[Antonio Perchinumio]

But in Coldfusion 7 it just works, and for Ralio Todd Rafferty wrote:
"I use cfsqltype="CF_SQL_TIMESTAMP" all the time with DateTime and it
just
works."

What's wrong in this code: <cfqueryparam cfsqltype="cf_sql_timestamp"
value="#now()#" /> ?

Antonio

On 11 Ott, 14:58, Andrea Campolonghi <and...@getrailo.org> wrote:
> Antonio.
>
> The point is that Railo is doing the correct thing so I do not think exists a "clean" workaround for this upgrade.
>
> Andrea
>
> On Oct 11, 2011, at 2:28 PM, Ronan Lucio wrote:
>
>
>
>
>
>
>
>
>
> > Antonio,
>
> > Why not just using NOW() function from SQL?
>
> > SET my_column = NOW()
>
> > Ronan
>

> > 2011/10/10 Antonio Perchinumio <antonioperchinu...@gmail.com>

[Ronan Lucio]

Antonio,

I agree with you a call to Now() function is supposed to work anywhere.

Anyway, I'm just trying to help you find a workaround for that.

Did you try using cf_sql_datetime instead of cf_sql_timestamp?

I use to use timestamp columns for auto-filled columns and datetime columns for dates where I need to inform the value.

Does this help you?

Ronan

2011/10/11 Antonio Perchinumio <antoniope...@gmail.com>