Re: [DFW CFUG] DB2 Date field and CFQueryParam problem.

Mon, 19 Jun 2006 11:48:23 -0700 

Well, it might not be the date, but what if you tried
#dateFormat(ThisShiftDate, "dd-mmm-yy")#
OR
#dateFormat(ThisShiftDate, "dd-mmm-yyyy")#

and see if it likes one of those any better?

*****************
Ken Ferguson
214.636.6126
*****************






Christopher Jordan wrote:
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] <mailto:[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] <mailto:[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 <http://www.HostMySite.com>
      www.teksystems.com/ <http://www.teksystems.com/>





--
Ryan Everhart
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
_______________________________________________ Reply to DFWCFUG: [email protected] <mailto:[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 <http://www.HostMySite.com> www.teksystems.com/ <http://www.teksystems.com/> 
------------------------------------------------------------------------

_______________________________________________
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/ 


_______________________________________________
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/

Reply via email to