ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Butler" <jeffgbut...@gmail.com>
Subject Re: Insert using select for values
Date Fri, 02 Feb 2007 16:25:35 GMT
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 <TomH@a-t-g.com> 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:jeffgbutler@gmail.com]
> *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* <TomH@a-t-g.com> 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
>
>
>
>
>

Mime
View raw message