We've had a bit of a struggle with this, but finally came up with a
solution: the following UDF will enable DateTime type objects to be passed
in to Oracle 8.05 via CFMX 6.1
<cfscript>
function create805DateTime(ts) {
return "TO_DATE('#DateFormat(ts,'MM/DD/YYYY')#
#TimeFormat(ts,'HH:mm:ss')#','MM/DD/YYYY HH24:MI:SS')";
}
</cfscript>
We're still not sure why CFMX 6.1 uses a non-backward-compatible database
function for Oracle calls. Anybody with inside information care to comment?
Thanks,
ecd.
-----Original Message-----
From: Davis, Eric [mailto:[EMAIL PROTECTED]
Subject: RE: [CFCDev] OT: CFMX 6.1, Oracle 8.05, CreateODBCDateTime()
We've decided to code the date format ourselves:
{ts '#DateFormat(foo, 'yyyy-mm-ss')# #TimeFormat(foo, 'HH:mm:ss')'}
I'll let you know what the results are.
I'd still like to get to the bottom of why the function no longer works in
Oracle 8. What would make Macromedia change their call to a version-specific
database function? This just seems sloppy to me. And Brad, how did you
figure out exactly what the SQL call was?
Thanks for your help, all.
-----Original Message-----
From: Jeff Britts [mailto:[EMAIL PROTECTED]
Subject: RE: [CFCDev] OT: CFMX 6.1, Oracle 8.05, CreateODBCDateTime()
Change your queryparam type to CF_SQL_TIMESTAMP.
-----Original Message-----
From: Brad Howerter [mailto:[EMAIL PROTECTED]
Subject: RE: [CFCDev] OT: CFMX 6.1, Oracle 8.05, CreateODBCDateTime()
The query doesn't work correctly. It doesn't error, but it seems to treat
the value as just a time stamp with no date part.
This query returns 0 rows:
<cfquery name="rs" datasource="#request.dsn.select#">
SELECT * FROM DUAL WHERE SYSDATE - 1 < <cfqueryparam
value=#createODBCDateTime(now() )# cfsqltype="CF_SQL_TIME">
</cfquery>
-----Original Message-----
From: Stacy Young [mailto:[EMAIL PROTECTED]
Subject: RE: [CFCDev] OT: CFMX 6.1, Oracle 8.05, CreateODBCDateTime()
What happens if u use cfqueryparam and use "timestamp" data type?
-----Original Message-----
From: Davis, Eric [mailto:[EMAIL PROTECTED]
Subject: [CFCDev] OT: CFMX 6.1, Oracle 8.05, CreateODBCDateTime()
When used together, CFMX 6.1, Oracle 8.05, CreateODBCDateTime() error out.
The same code works fine in CFMX 6.0. Can anyone verify?
The code in use is:
---
<cfquery name="getOperator" datasource="mva">
SELECT oper_name, (etc)
FROM mva_assignment
WHERE x_datetime_insert = #CreateODBCDateTime(url.dt)#
AND (etc)
</cfquery>
---
The exact error returned is:
---
Error Executing Database Query.
ORA-00904: invalid column name
---
The odd thing is that if the call is changed to CreateODBCDate(), it no
longer errors out; the field is, however, a datetime field.
I'm not sure if this is a driver issue or a JDBC-ODBC Bridge issue, or some
other issue entirely. I'm just looking for help for a colleague. Has anyone
run into this?
--
ecd
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email
to [EMAIL PROTECTED] with the word 'unsubscribe cfcdev'
in the message of the email.
CFCDev is run by CFCZone (www.cfczone.org) and supported
by Mindtool, Corporation (www.mindtool.com).
An archive of the CFCDev list is available at www.mail-archive.com/[EMAIL PROTECTED]
