This looks like a sequence/increment-value to me, doesn't DB2 have something like this? That would probably be a lot safer as well in a concurrent environment.
Niels ________________________________ From: Michael Schall [mailto:[email protected]] Sent: Tuesday, January 06, 2009 5:39 PM To: [email protected] Subject: Multiple Queries in a single prepared statement in DB2 I would like to run multiple queries within a single select statement using IBATIS connecting to DB2 UDB. <select id="getIncrementedValue" parameterClass="java.lang.String" resultClass="java.lang.Integer"> <![CDATA[ UPDATE ${schema}.ADMCOUNTERS SET COUNTER = COUNTER + 1 WHERE COUNTERCD = #value# ; SELECT COUNTER FROM ${schema}.ADMCOUNTERS WHERE COUNTERCD = #value# ]]> </select> Following the suggestion from http://www.mail-archive.com/[email protected]/msg02858.html. I tried to add the allowMultiQueries setting on the connection URL but I still get an error from db2. The list of parameters for connection URLs for the v8 DB2 driver does not contain this setting. (http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com .ibm.db2.udb.doc/ad/rjvdsprp.htm) But I can't find a similar page for 9.5. Should this work? Is this a limitation of the DB2 JDBC driver? Am I missing something? Thanks for your time. Mike My connection URL: jdbc:db2://<server>:<port>/<database>:currentPackageSet=NULLIDR1;allowMu ltiQueries=true Root Error: Caused by: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: ? ;; SELECT COUNTER FROM;WHERE COUNTERCD =;<space> at com.ibm.db2.jcc.c.kh.c(kh.java:1660) at com.ibm.db2.jcc.c.kh.d(kh.java:1648) at com.ibm.db2.jcc.c.kh.a(kh.java:1205) at com.ibm.db2.jcc.b.db.g(db.java:139) at com.ibm.db2.jcc.b.db.a(db.java:39) at com.ibm.db2.jcc.b.t.a(t.java:34) at com.ibm.db2.jcc.b.sb.f(sb.java:142) at com.ibm.db2.jcc.c.kh.m(kh.java:1176) at com.ibm.db2.jcc.c.lh.bb(lh.java:1941) at com.ibm.db2.jcc.c.lh.d(lh.java:2383) at com.ibm.db2.jcc.c.lh.U(lh.java:1401) at com.ibm.db2.jcc.c.lh.execute(lh.java:1385) at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(Delegati ngPreparedStatement.java:169) at sun.reflect.GeneratedMethodAccessor93.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor Impl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(Prepared StatementLogProxy.java:62) at $Proxy1.execute(Unknown Source) at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor. java:185) at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQue ry(MappedStatement.java:221) at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryW ithCallback(MappedStatement.java:189) ... 47 more
