Nested JDBC Statement Query Problem

WonYoung Lee

unread,

May 22, 2001, 10:20:12 AM5/22/01

to

Abstract : Nested JDBC statement query produce
"CLI0125E Function sequence error" with a datasource
using JDBC App Driver for DB2.

Environment :
- Windows 2000
- WebSphere 3.5.3 (or + PQ47404)
- DB2 6.1 + fixpack 4
- DataSource using JDBC App Driver for UDB DB2
- DB and WebSphere are located in the same PC
- UDB DB2 already issued with command "...\sqllib\java12\usejdbc2"

Regeneration Error Senario :

db2 => describe select * from emp
SQLDA Info
sqldaid : SQLDA sqldabc: 896 sqln: 20 sqld: 2
Column Info
sqltype sqllen sqlname.data
sqlname.length
-------------------- ------ ------------------------------ -------------
-
485 DECIMAL 4, 0 EMPNO
5
449 VARCHAR 8 ENAME
5

db2 => select * from emp
EMPNO ENAME
------ --------
1000. aaaaa
1001. bbbbb
1002. cccccc
1003. ddddd
1004. eeeee
1005. fffff

6 records selected.

http://localhost/test1.jsp
---------------------------------------------------------------------
<%@ page contentType="text/html; charset=euc-kr" %>
<%@ page import="java.sql.*,javax.sql.*,javax.naming.*" %>
<%!
private static DataSource ds = null;
private static String source = "jdbc/LWYDB";
private static String user = "db2admin";
private static String password = "db2admin";
static {
try {
java.util.Hashtable props = new java.util.Hashtable();
props.put(Context.INITIAL_CONTEXT_FACTORY,
"com.ibm.ejs.ns.jndi.CNInitialContextFactory");
Context ctx = null;
try {
ctx = new InitialContext(props);
ds = (DataSource)ctx.lookup(source);
}
finally {
if ( ctx != null ) ctx.close();
}

}
catch (Exception e) {
System.err.println(e.toString());
}
}
%>
<xmp>
<%
Connection conn = null;
Statement stmt = null;
try {
conn = ds.getConnection(user, password);

//Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
//conn =
DriverManager.getConnection("jdbc:db2:LWYDB",user,password);

//Class.forName("COM.ibm.db2.jdbc.net.DB2Driver").newInstance();
//conn =
DriverManager.getConnection("jdbc:db2://localhost/LWYDB",user,password);

stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select empno from emp");
while(rs.next()){
Statement stmt2 = null;
try {
stmt2 = conn.createStatement();
ResultSet rs2 = stmt2.executeQuery(
"select * from emp where empno = " + rs.getString(1));
while(rs2.next()) {
out.println(rs2.getString(1) + ", " +
rs2.getString(2) );
}
rs2.close();
}
finally{
if(stmt2 != null) try{stmt2.close();}catch(Exception e){}
}
}
rs.close();
}
catch(Exception e){
java.io.ByteArrayOutputStream bout =
new java.io.ByteArrayOutputStream();
java.io.PrintWriter pw = new java.io.PrintWriter(bout);
e.printStackTrace(pw);
pw.flush();
out.println(bout.toString());
}
finally {
if(stmt != null) try {stmt.close();}catch(Exception e){}
if(conn != null) try {conn.close();}catch(Exception e){}
}

%>
</xmp>
---------------------------------------------------------------------

Result:
---------------------------------------------------------------------
1000, aaaaa
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0125E
Function sequence error. SQLSTATE=HY010
at java.sql.SQLException.<init>(SQLException.java:45)
at COM.ibm.db2.jdbc.DB2Exception.<init>(DB2Exception.java:71)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException
(SQLExceptionGenerator.java(Compiled Code))
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException
(SQLExceptionGenerator.java:187)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(
SQLExceptionGenerator.java:438)
at COM.ibm.db2.jdbc.app.DB2ResultSet.next(DB2ResultSet.java:444)
at com.ibm.ejs.cm.proxy.ResultSetProxy.next(ResultSetProxy.java:
495)
at D_0003a.WebSphere.AppServer.hosts.d_00025fault_0005fhost.AppS
erverWebApp.web._test1_2E_jsp_jsp_6._jspService(_test1_2E_jsp_js
p_6.java(Compiled Code))
at com.sun.jsp.runtime.HttpJspBase.service(HttpJspBase.java:175)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:214)
......
---------------------------------------------------------------------

No error Cases :
- Withoutout DataDource, just using DB2 App Driver:Class.forName(..)
- Withoutout DataDource, just using DB2 Net Driver:Class.forName(..)
- DataSource using DB2 Net Driver

Error Case:
- DataSource using DB2 App Driver

Is it a bug, really ?

WonYoung Lee.

Al Briggs

unread,

Jun 21, 2001, 9:24:25 AM6/21/01

to

I have same problem - any help would be wonderful.

Goodguy

unread,

Jun 21, 2001, 7:21:44 PM6/21/01

to

Create another connection (conn) and execute the second query. the
problem will be solved. This solution would be resource itensive.

Use connection pooling!!!

Al.B...@transitioncomputing.com (Al Briggs) wrote in message news:<bf77f74f.01062...@posting.google.com>...