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

[Christopher Jordan]

Yeah, I did that. I think I just found the problem though. For any interested here it is... the original error was: Error Occurred While Processing Request  Error Executing Database Query.  Data truncation    The error occurred in C:\Inetpub\wwwroot\Include\CFC\Note.cfc: line 134 Called from C:\Inetpub\wwwroot\MasterConsole\MasterConsoleDevelopment\1.1.9\AddNote.cfm: line 148 Called from C:\Inetpub\wwwroot\Include\CFC\Note.cfc: line 134 Called from C:\Inetpub\wwwroot\MasterConsole\MasterConsoleDevelopment\1.1.9\AddNote.cfm: line 148   134 :                             <!--- ,<CFQueryParam value="#ThisShiftDate#"        cfsqltype="CF_SQL_DATE"> ---> 135 :                         </CFIf>    136 :                     ) 137 :                 </CFQuery> Making me think that the problem had to do with the date. I took out all the CFQueryParams and ran the query as "normal" and got the following: [SQL0404] Value for column or variable USERNAME too long.  after all that, it was the username field and not the date field that was the culprit. I'll check out the length of that field and see what's wrong, put everything back the way it was with the CFQueryParams and see what happens. I appreciate the list's quick response! :) Awesome... Also, glad to hear I'm not the only one using an AS400 DB2... what do you think of yours Eric? And how on earth do you tell what version of the DB2 SQL you're using? -Chris Knipp, Eric wrote: 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:  List@list.dfwcfug.org 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:   List@list.dfwcfug.org 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/   CONFIDENTIALITY NOTICE: The information contained in this e-mail and attached document(s) may contain confidential information that is intended only for the addressee(s). If you are not the intended recipient, you are hereby advised that any disclosure, copying, distribution or the taking of any action in reliance upon the information is prohibited. If you have received this e-mail in error, please immediately notify the sender and delete it from your system. _______________________________________________ Reply to DFWCFUG: List@list.dfwcfug.org 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: 
  List@list.dfwcfug.org
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/