Have you tried using createODBCDate(),
that fixed a few of our problems in the past in cases like this. We are also
using iSeries DB2.
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On
Behalf Of Christopher Jordan
Sent: Monday, June 19, 2006 1:47
PM
To: Dallas/Fort Worth ColdFusion
User Group Mailing List
Subject: Re: [DFW CFUG] DB2 Date
field and CFQueryParam problem.
I hadn't. So I tried. Same
result. My data type in the table is "date" not timestamp, and I
*want* it to be a date and not a timestamp. I'm not so certain that it's the
date anymore, even though that's where the program halts. I changed the query
to look like this:
<CFQuery Name="AddNewNote"
DataSource="#as400ds#">
INSERT INTO #PrependLibrary("Notes")#
(#ThisNoteInsertList#)
VALUES (
<CFQueryParam
value="#ThisClientID#"
cfsqltype="CF_SQL_INTEGER">,
<CFQueryParam
value="#ThisNoteID#"
cfsqltype="CF_SQL_INTEGER">,
<CFQueryParam
value="#ThisNoteType#"
cfsqltype="CF_SQL_VARCHAR">,
<CFQueryParam
value="#ThisNoteTimeStamp#"
cfsqltype="CF_SQL_TIMESTAMP">,
<CFQueryParam
value="#ThisNoteText#"
cfsqltype="CF_SQL_VARCHAR">,
<CFQueryParam
value="#ThisNoteAuthor#"
cfsqltype="CF_SQL_VARCHAR">,
<CFQueryParam
value="#ThisNoteAuthorIPAddress#"
cfsqltype="CF_SQL_VARCHAR">
<CFIf
Len(ThisEmployeeID)>
,<CFQueryParam value="#ThisEmployeeID#"
cfsqltype="CF_SQL_INTEGER">
</CFIf>
<CFIf
Len(ThisCostCenterID)>
,<CFQueryParam value="#ThisCostCenterID#"
cfsqltype="CF_SQL_INTEGER">
</CFIf>
<CFIf
Len(ThisShiftID)>
,<CFQueryParam value="#ThisShiftID#"
cfsqltype="CF_SQL_INTEGER">
</CFIf>
<CFIf
Len(ThisShiftDate)>
,#ThisShiftDate#
<!---,<CFQueryParam value="#ThisShiftDate#"
cfsqltype="CF_SQL_DATE">--->
</CFIf>
)
</CFQuery>
and I got the same results. I'm guessing my next steps will be to remove all of
the cfqueryparam statements, make sure my query runs without them, and then
start adding them back in one at a time. Or I suppose I could remove them one
at a time until the statement runs... either way. ugh... :'(
Thanks Ryan. Any other thoughts I'm open.
Chris
Ryan Everhart wrote:
Chris,
I am no DB2 guru, but have ou tried CF_SQL_TIMESTAMP as the SQL type?
Ryan
On 6/19/06, Christopher Jordan < [EMAIL PROTECTED]> wrote:
Hi folks,
I'm getting a Data Truncation error with the following code snippet:
<CFQuery Name="AddNewNote"
DataSource="#as400ds#">
INSERT INTO #PrependLibrary("Notes")#
(#ThisNoteInsertList#)
VALUES (
<CFQueryParam value="#ThisClientID#"
cfsqltype="CF_SQL_INTEGER">,
<CFQueryParam
value="#ThisNoteID#"
cfsqltype="CF_SQL_INTEGER">,
<CFQueryParam
value="#ThisNoteType#"
cfsqltype="CF_SQL_VARCHAR">,
<CFQueryParam
value="#ThisNoteTimeStamp#"
cfsqltype="CF_SQL_TIMESTAMP">,
<CFQueryParam
value="#ThisNoteText#"
cfsqltype="CF_SQL_VARCHAR">,
<CFQueryParam
value="#ThisNoteAuthor#"
cfsqltype="CF_SQL_VARCHAR">,
<CFQueryParam
value="#ThisNoteAuthorIPAddress#"
cfsqltype="CF_SQL_VARCHAR">
<CFIf
Len(ThisEmployeeID)>
,<CFQueryParam value="#ThisEmployeeID#"
cfsqltype="CF_SQL_INTEGER">
</CFIf>
<CFIf
Len(ThisCostCenterID)>
,<CFQueryParam value="#ThisCostCenterID#"
cfsqltype="CF_SQL_INTEGER">
</CFIf>
<CFIf
Len(ThisShiftID)>
,<CFQueryParam value="#ThisShiftID#"
cfsqltype="CF_SQL_INTEGER">
</CFIf>
<CFIf
Len(ThisShiftDate)>
,<CFQueryParam value="#ThisShiftDate#"
cfsqltype="CF_SQL_DATE">
</CFIf>
)
</CFQuery>
The issue appears to be with the CF_SQL_DATE line. My DB2 table is set up such
that the field called ShiftDate is of datatype "DATE", and the value
of "ThisShiftDate" at the time I'm trying to insert it is: {d
'2006-18-06'}
I'm at a loss. I've only just begun to use CFQueryParam and I wonder if I'm
doing something wrong or if it's just the stupid DB2 table or what?
Any DB2 gurus in the group? I've got no idea what version of DB2 I'm on, but
I'd be willing to bet it's rather old, as the AS400 it resides on is quite old.
Help would be greatly appreciated. :)
Thanks,
Chris
_______________________________________________
Reply to DFWCFUG:
[email protected]
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.HostMySite.com
www.teksystems.com/
--
Ryan Everhart
[EMAIL PROTECTED]
_______________________________________________
Reply to DFWCFUG:
[email protected]
Subscribe/Unsubscribe:
http://lists1.safesecureweb.com/mailman/listinfo/list
List Archives:
http://www.mail-archive.com/list%40list.dfwcfug.org/
http://www.mail-archive.com/list%40dfwcfug.org/
DFWCFUG Sponsors:
www.HostMySite.com
www.teksystems.com/
