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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[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
> >
> >
> >
> >
> >
>
>
