ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Nathan Maves" <nathan.ma...@gmail.com>
Subject Re: Insert using select for values
Date Fri, 02 Feb 2007 16:49:54 GMT
Having never used DB2 can you not just specify what the JDBC type is for
that column?

Try adding #enteredDate:DATE# or #enteredDate:TIMESTAMP# to your inline
parameter map.

Nathan



On 2/2/07, Jeff Butler <jeffgbutler@gmail.com> wrote:
>
> 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