Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 49683 invoked from network); 2 Feb 2007 19:15:11 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 2 Feb 2007 19:15:11 -0000 Received: (qmail 65872 invoked by uid 500); 2 Feb 2007 19:15:14 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 65853 invoked by uid 500); 2 Feb 2007 19:15:14 -0000 Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user-java@ibatis.apache.org Delivered-To: mailing list user-java@ibatis.apache.org Received: (qmail 65842 invoked by uid 99); 2 Feb 2007 19:15:13 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 02 Feb 2007 11:15:13 -0800 X-ASF-Spam-Status: No, hits=2.0 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: domain of jeffgbutler@gmail.com designates 64.233.162.237 as permitted sender) Received: from [64.233.162.237] (HELO nz-out-0506.google.com) (64.233.162.237) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 02 Feb 2007 11:15:05 -0800 Received: by nz-out-0506.google.com with SMTP id q3so860816nzb for ; Fri, 02 Feb 2007 11:14:44 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=tBdvMYJu6Jj1T5lCB+1LKpjIUHwxUx3aeIMxytXHd76a7DBH+9QhJSwKK8/RGZHJay8P0XsDaYZYtHdDVsa+nwhpnpyhOq0kBiIAC3qw/aheD4tVbgJ2g4vnYmkCB1rGDQmWrS6ZQbtfcwlsm3DEFsQO8EMylO265nBZf5zTJQo= Received: by 10.114.151.13 with SMTP id y13mr323542wad.1170443683997; Fri, 02 Feb 2007 11:14:43 -0800 (PST) Received: by 10.114.72.19 with HTTP; Fri, 2 Feb 2007 11:14:43 -0800 (PST) Message-ID: Date: Fri, 2 Feb 2007 13:14:43 -0600 From: "Jeff Butler" To: user-java@ibatis.apache.org Subject: Re: Insert using select for values In-Reply-To: <2f55db670702020849i5a16d97ia96c6b77249c8220@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_11490_29879442.1170443683390" References: <2f55db670702020849i5a16d97ia96c6b77249c8220@mail.gmail.com> X-Virus-Checked: Checked by ClamAV on apache.org ------=_Part_11490_29879442.1170443683390 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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 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 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 > > > > > > > > > > > > > > > > > > > > ------=_Part_11490_29879442.1170443683390 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
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

 

 




------=_Part_11490_29879442.1170443683390--