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 :
<!--- ,<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:
[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/
