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]
> *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]> 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.setBatchNumber(
> * PrintBatchLabelsBean.java:258* )
>
>
>
> Insert from p6spy
>
>
>
> INSERT INTO
pharm.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, 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
>
>
>
>
>
