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

Knipp, Eric Mon, 19 Jun 2006 12:47:58 -0700

Yes there are definitely shortcomings in iSeries DB2, that for the cost, should probably be addressed adequately. We seem to have to throw a lot more money at our database than I think we’d have to if we were using some other platform like you mentioned.

From: Christopher Jordan [mailto:[EMAIL PROTECTED]
Sent: Monday, June 19, 2006 2:47 PM
To: Dallas/Fort Worth ColdFusion User Group Mailing List
Subject: Re: [DFW CFUG] DB2 Date field and CFQueryParam problem.

Eric,

We're also running V5R3, but (if I've got it right) that's the version of the iSeries OS running on the AS400 and not the version of the DB2 engine that's running. I don't know maybe I'm wrong. I personally don't like the DB2 system not only because of its cost, but because it's dern hard to work with. The iSeries Navigator helps, but for me is very slow and clunky. Queries seem slow, (of course our box is only 750MHz) and that's despite having indexes on our tables. The iSeries Navigator will suggest indexes, but won't make them for you, and one you've made them it doesn't always use them. Also, you cannot resize a column in a table (well, down-size it anyway) using the alter statement. It just leaves much to be desired. :(

I'd much prefer SQL Server or MySQL any day.

Anyway, I was just curious what someone else also using iSeries/AS400, etc., etc.. thought about it. :)

Thanks.
Chris

Knipp, Eric wrote:

Chris,

I know we are running V5R3 because that is what iSeries Navigator tells me. There’s probably some kind of metadata that can be extracted to determine the version but I don’t know how to do that. IBM’s website is a great resource though and would probably help.

Overall, I don’t think that iSeries DB2 is really worth it. If we did not have a COBOL legacy system running on our iSeries, I would see no reason to pay the high cost of iSeries DB2. It has all the needed features and performs will, but is damned expensive. Having said that, it is very convenient for us to be able to have access to the same data consumed by our legacy system (although that introduces other issues of its own).

How do you like working with it? Are you using the JTOpen toolkit for JDBC connectivity or some other driver?

Eric

From: Christopher Jordan [mailto:[EMAIL PROTECTED]]
Sent: Monday, June 19, 2006 1:58 PM
To: Dallas/Fort Worth ColdFusion User Group Mailing List
Subject: Re: [DFW CFUG] DB2 Date field and CFQueryParam problem.

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

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:

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

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

Reply via email to