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 19:14:43 GMT
I think you would have to do a CAST like this:

insert into sometable (id, date_column)
select id, DATE(#enteredDate#)
from someothertable

I haven't tried it, so no guarantees, but that's my best guess from the
docs.

Jeff Butler




On 2/2/07, Nathan Maves <nathan.maves@gmail.com> wrote:
>
> 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