COLDFUSION 10 VENDORERROR -104 SQLSTATE 42601

[Luis_mackenzie]

Good Afternon,

My name is Luis, where are from Brasil,

The following problem occurs

After migrarmos version of Coldfusion Coldfusion version 4.5 to 10, everything worked normally.

configure access to DB2 database via JDBC. What is happening is the recordings on the bench with the DATE field,

segie the error attached.

[saurav Pandit]

Hi Luis,

What is the data type for the column "data"?

Thanks

[Aegis_Kleais]

Luis.  You might want to use <CFQUERYPARAM> to help protect your SQL queries

SELECT INTO table_name ( col_1, col_2, etc. )

VALUES ( <cfqueryparam type="cf_sql_varchar" value="text" />, <cfqueryparam type="cf_sql_integer" value="3">, etc. )

Date fields can be kinda pissy if the values put into them don't completely adhere to the right format.  I'm not familiar with DB2 databases, but can you dump the value that you are attempting to put into the column so we can see how it is formatted before it's attempting to be inserted?

[Luis_mackenzie]

Ok

ok, showed variable before the insert, the dates are different, the old

Coldfusion 4.5 shows the short date.

In the new coldfusion 10 the same date code shows the long

follows the attachment.

Em 23-03-2013 14:41, Aegis Kleais escreveu:

>

Re: COLDFUSION 10 VENDORERROR -104 SQLSTATE 42601

created by Aegis Kleais <http://forums.adobe.com/people/Aegis+Kleais>

in /ColdFusion/ - View the full discussion

<http://forums.adobe.com/message/5172673#5172673

[Aegis_Kleais]

I've always formated my date objects to match the format that the DB supports.  For example, this is for MSSQL which supports yyyy-mm-dd hh:mm:ss.nnn tt (ie, 2013-03-25 08:06:33.000 PM).  Can you do likewise for the DB2 date format?

I did a cursory glance at DB2 and there doesn't seem to be a "DATA" data type.  There is a DATE data type, and it looks like YYYY-MM-DD-HH-MM-SS.000000  (The last 6 0 are milliseconds I believe, and the hour uses a 24-hr clock)

So try this for your queryparam:

<cfqueryparam cfsqltype="cf_sql_timestamp" value="#dateFormat( dateVariable, 'yyyy-mm-dd' )#-#timeFormat( dateVariable, 'HH:mm:ss' )#.000000" />

[Luis_mackenzie]

Type date

Em 23-03-2013 05:20, saurav Pandit escreveu:

>

Re: COLDFUSION 10 VENDORERROR -104 SQLSTATE 42601

created by saurav Pandit

<http://forums.adobe.com/people/saurav+Pandit> in /ColdFusion/ - View

the full discussion <http://forums.adobe.com/message/5172104#5172104

[Aegis_Kleais]

Again, I'm not familiar with DB2, but I did a quick search on it, and it states that its date objects are formated by default as YYYY-MM-DD

If '23-03-2013' is what is being supplied, that would be seen as March 2013, 2023, which may be why it's throwing an error.

Can you try something like <cfqueryparam cfsqltype="cf_sql_date" value="#dateFormat( dateObject, 'yyyy-mm-dd' )#" />

(Or at the least can you temporarily force a static value of '2013-03-23' and see if it goes through)?

[Luis_mackenzie]

Goot Nigth

the data type is date for the column "data" (db2 database)

Luis Claudio

DTI- SP

(55) 11-2114-8021

Em 23-03-2013 05:20, saurav Pandit escreveu:

>

Re: COLDFUSION 10 VENDORERROR -104 SQLSTATE 42601

created by saurav Pandit

<http://forums.adobe.com/people/saurav+Pandit> in /ColdFusion/ - View

the full discussion <http://forums.adobe.com/message/5172104#5172104