You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Michael Schall <mi...@gmail.com> on 2009/01/06 17:38:38 UTC
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/dev@ibatis.apache.org/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;allowMultiQueries=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(DelegatingPreparedStatement.java:169)
at sun.reflect.GeneratedMethodAccessor93.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at
com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.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.sqlExecuteQuery(MappedStatement.java:221)
at
com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
... 47 more
Re: Multiple Queries in a single prepared statement in DB2
Posted by Stephen Boyd <sw...@gmail.com>.
Yes, it does.
select nextval for <sequence_name>
from sysibm.sysdummy1
On Tue, Jan 6, 2009 at 2:28 PM, Niels Beekman <n....@wis.nl> wrote:
> 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:mike.schall@gmail.com]
> *Sent:* Tuesday, January 06, 2009 5:39 PM
> *To:* user-java@ibatis.apache.org
> *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/dev@ibatis.apache.org/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;allowMultiQueries=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(DelegatingPreparedStatement.java:169)
>
> at sun.reflect.GeneratedMethodAccessor93.invoke(Unknown
> Source)
>
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>
> at java.lang.reflect.Method.invoke(Method.java:585)
>
> at
> com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.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.sqlExecuteQuery(MappedStatement.java:221)
>
> at
> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
>
> ... 47 more
>
>
>
>
>
RE: Multiple Queries in a single prepared statement in DB2
Posted by Niels Beekman <n....@wis.nl>.
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:mike.schall@gmail.com]
Sent: Tuesday, January 06, 2009 5:39 PM
To: user-java@ibatis.apache.org
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/dev@ibatis.apache.org/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
Re: Multiple Queries in a single prepared statement in DB2
Posted by Mario Ds Briggs <ma...@in.ibm.com>.
Michael,
multiple semicolon separated queries are not allowed by DB2. It is good
from a security viewpoint too.
thanks
Mario
"Michael Schall"
<mike.schall@gmai
l.com> To
user-java@ibatis.apache.org
06/01/2009 22:08 cc
Subject
Please respond to Multiple Queries in a single
user-java@ibatis. prepared statement in DB2
apache.org
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/dev@ibatis.apache.org/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;allowMultiQueries=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(DelegatingPreparedStatement.java:169)
at sun.reflect.GeneratedMethodAccessor93.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at
com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.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.sqlExecuteQuery(MappedStatement.java:221)
at
com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
... 47 more