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

Mon, 19 Jun 2006 11:45:58 -0700

I'm kinda surprised to hear that you get a truncation error on date field.  In my experience with MSSQL is you would get something like invalid date.  Instead of removing all the query params I would start by just removing that suspected field.  Remove that date from your query and see if the insert works.

The only reason I suggested TIMESTAMP is because it worked for me one time when I had an issue.  I still use TIMESTAMP today in all my query params and it keeps date and time fields just fine.

Ryan


On 6/19/06, Christopher Jordan <[EMAIL PROTECTED]> 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]> 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/





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

Reply via email to