Insert using select for values

[Tom Henricksen]

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.jav
a:39)

      at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor
Impl.java:25)

      at java.lang.reflect.Method.invoke(Method.java:324)

      at
com.advtechgrp.remoting.ResponseBuilder.getResponseUnsafe(ResponseBuilde
r.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(Bat
chPrintProcessService.java:121)

      at
com.advtechgrp.bop.pharmacy.web.pages.rx.PrintBatchLabelsBean.setBatchNu
mber(PrintBatchLabelsBean.java:258)

 

Insert from p6spy

 

INSERT INTO
pharm.BATCHPRINTPRCS(BATCHPRINTPRCSID,CMPLXID,INVLOCID,DISPENSINGID,


BATCHTYPEID,BATCHNMBR,PRVDRID,CMNTID,STATUS,ENTEREDBY,ENTEREDDT,ORIGENTE
REDBY,

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