Thanks Jeff!
That did the trick once we formatted it and added the quotes it worked
fine.
Tom
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.S");
return "'" + df.format(getEnteredDate()) + "'";
-----Original Message-----
From: Jeff Butler [mailto:[EMAIL PROTECTED]
Sent: Friday, February 02, 2007 10:26 AM
To: user-java@ibatis.apache.org
Subject: Re: Insert using select for values
Date literals are a real pain. I believe that the default toString() on
a Java Date is invalid for DB2. So you'll need to transform that Date
object into a properly formatted string for DB2 - like you used in your
hardcoded example.
Jeff Butler
On 2/2/07, Tom Henricksen <[EMAIL PROTECTED]> wrote:
We tried that and that works for everything but the date fields. The
date is coming across Fri Feb 02 09:42:11 CST 2007 where it needs quotes
around it. It gets a DB2 42601 "A character, token, or clause is invalid
or missing." I tried adding \'$enteredDate$\' and that didn't work
either. When I hard coded the dates as ' 2007-2-1 3:28:06.0' in the xml
it worked fine.
Thanks,
Tom
-----Original Message-----
From: Jeff Butler [mailto: [EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]> ]
Sent: Friday, February 02, 2007 9:23 AM
To: user-java@ibatis.apache.org
Subject: Re: Insert using select for values
DB2 SQLSTATE 42610 means "A parameter marker is not allowed"
My guess is that DB2 is complaining about the parameters in the select
list (enteredBy, enteredDate, etc.) Try changing to simple substitution
($enteredBy$, $enteredDate$, etc.)
Jeff Butler
On 2/2/07, Tom Henricksen < [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> >
wrote:
We are using Insert with select to populate the values. We are using
the select for most of the values but there are a few that we try to
pass in. The variables seem to be the problem.
We are using iBatis(2.1.5). We also use p6spy (captures all SQL to log)
and when we take the SQL insert and run it against db2( 8.2) it works
fine. The variables are getting populated correctly.
iBatis SQLMap syntax
INSERT INTO ${schemaPharm}.BATCHPRINTPRCS(
BATCHPRINTPRCSID,
CMPLXID,
INVLOCID,
DISPENSINGID,
BATCHTYPEID,
BATCHNMBR,
PRVDRID,
CMNTID,
STATUS,
ENTEREDBY,
ENTEREDDT,
ORIGENTEREDBY,
ORIGENTEREDDT,
ENTEREDLOCID)
SELECT
BATCHPRINTID,
CMPLXID,
INVLOCID,
DISPENSINGID,
BATCHTYPEID,
BATCHNMBR,
PRVDRID,
1,
1,
#enteredBy#,
#enteredDate#,
#enteredBy#,
#enteredDate#,
#enteredLocationId#
FROM ${schemaPharm}.BATCHPRINT
WHERE BATCHNMBR = #batchNmbr#
When this runs we get the following error.
java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0( Native Method )
at sun.reflect.NativeMethodAccessorImpl.invoke(
NativeMethodAccessorImpl.java:39 )
at sun.reflect.DelegatingMethodAccessorImpl.invoke(
DelegatingMethodAccessorImpl.java:25 )
at java.lang.reflect.Method.invoke( Method.java:324)
at com.advtechgrp.remoting.ResponseBuilder.getResponseUnsafe(
ResponseBuilder.java:118 )
at com.advtechgrp.remoting.ResponseBuilder.getResponse(
ResponseBuilder.java:52 )
at com.advtechgrp.remoting.RemotingServlet.doPost(
RemotingServlet.java:98 )
Caused by: com.advtechgrp.exceptions.UnexpectedException:
--- The error occurred in
com/advtechgrp/bop/pharmacy/rx/data/BatchPrintProcessVsp.xml.
--- The error occurred while applying a parameter map.
--- Check the
BatchPrintProcessVsp.insertByBatchNumber-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -418,
SQLSTATE: 42610, SQLERRMC: null
at
com.advtechgrp.bop.pharmacy.rx.service.BatchPrintProcessService.save(
BatchPrintProcessService.java:121 )
at
com.advtechgrp.bop.pharmacy.web.pages.rx.PrintBatchLabelsBean.setBatchNu
mber ( PrintBatchLabelsBean.java:258 )
Insert from p6spy
INSERT INTO
pharm.BATCHPRINTPRCS(BATCHPRINTPRCSID,CMPLXID,INVLOCID,DISPENSINGID,
BATCHTYPEID,BATCHNMBR,PRVDRID,CMNTID,STATUS,ENTEREDBY,ENTEREDDT,ORIGENTE
REDBY,
ORIGENTEREDDT,ENTEREDLOCID) SELECT BATCHPRINTID, CMPLXID,INVLOCID,
DISPENSINGID,
BATCHTYPEID,BATCHNMBR,PRVDRID,1, 1, 54365,'2007-02-02
08:51:04.857',54365,
'2007-02-02 08:51:04.857',258 FROM pharm.BATCHPRINT WHERE BATCHNMBR =
50341
This runs fine when run directly against db2.
Thanks,
Tom
