使用JDBC创建DB2存储过程非法字符的问题

小屋哥 2013-04-11 08:03:56

我写了一个分页读取数据库的DB2存储过程脚本，在控制中心和Quest Central for DB2 V4.8中执行通过，但我使用JDBC来创建这个存储过程的时候就报SQLCODE=-104，有非法字符，但我的确是一个字儿都没改。请问有没有碰到诸如此类的问题的仁兄们，跪求解释。我DB2的版本为 V9.7 EXPC。顺便贴上我的存储过程。



CREATE PROCEDURE XIAOWU.GETDATAS

(

	IN p_tableName VARCHAR(1000), 

  	IN p_strWhere VARCHAR(1000), 

  	IN p_orderColumn VARCHAR(1000), 

  	IN p_orderStyle VARCHAR(1000), 

  	IN p_curPage INTEGER, 

  	IN p_pageSize INTEGER, 

  	IN p_primaryColumn VARCHAR(1000),

	IN p_selectColumns VARCHAR(1000),

  	OUT p_totalRecords VARCHAR(1000), 

  	OUT p_totalPages INTEGER

	

 ) 

LANGUAGE SQL

READS SQL DATA

DYNAMIC RESULT SETS 1 

BEGIN

  	DECLARE v_sql  VARCHAR(3000) DEFAULT '';

	DECLARE v_startRecord INTEGER DEFAULT 0;

	DECLARE v_endRecord INTEGER DEFAULT 0;

	

	DECLARE cursql STATEMENT;

	DECLARE r_cur CURSOR WITH RETURN FOR cursql;

	

	IF(p_selectColumns = '') THEN

	    SET p_selectColumns = '*';

	END IF;

	

	SET v_sql = 'SET (?) = (SELECT COUNT(' || p_primaryColumn || ') FROM ' || p_tableName || '';

	

	IF (p_strWhere <> '') THEN

		set v_sql = v_sql || ' WHERE ' || p_strWhere;

	END IF;

	

	set v_sql = v_sql || ')';

	

	prepare cursql from v_sql;

	execute cursql into p_totalRecords;

	

	IF (MOD(p_totalRecords,p_pageSize) = 0) THEN

	   set p_totalPages = p_totalRecords / p_pageSize;

	ELSE

	   set p_totalPages = p_totalRecords / p_pageSize + 1;

	END IF;

	

	SET v_startRecord = (p_curPage - 1) * p_pageSize + 1;

	SET v_endRecord = p_curPage * p_pageSize;

	

	SET v_sql = 'SELECT * FROM (SELECT A.*,rownumber() over(order by '|| p_orderColumn || ' ' || p_orderStyle || ') rowid FROM (SELECT ' || p_selectColumns || ' FROM ' || p_tableName;

	

	IF(p_strWhere <> '') THEN

	    SET v_sql = v_sql || ' WHERE ' || p_strWhere;

	END IF;

	

	SET v_sql = v_sql || ' order by ' || p_orderColumn || ' ' || p_orderStyle || ') A ) B WHERE ROWID >= ' || v_startRecord || ' AND ROWID <= '|| v_endRecord || '';



	prepare cursql from v_sql;

	open r_cur;

	

	

END;

我在JDBC代码中将整个存储过程脚本缩为一行，JDBC代码如下



	public static void main(String[] args) throws Exception {

		Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");

		Connection connection = DriverManager.getConnection(

				"jdbc:db2://127.0.0.1:50000/SAMPLE", "xiaowu", "xxiaowu#11#15");

		PreparedStatement ps = connection.prepareStatement("CREATE PROCEDURE XXIAOWUXX(IN p_tableName VARCHAR(1000),IN p_strWhere VARCHAR(1000),IN p_orderColumn VARCHAR(1000),IN p_orderStyle VARCHAR(1000),IN p_curPage INTEGER,IN p_pageSize INTEGER,IN p_primaryColumn VARCHAR(1000),IN p_selectColumns VARCHAR(1000),OUT p_totalRecords VARCHAR(1000),OUT p_totalPages INTEGER) LANGUAGE SQL READS SQL DATA DYNAMIC RESULT SETS 1 BEGIN DECLARE v_sql  VARCHAR(3000) DEFAULT '';DECLARE v_startRecord INTEGER DEFAULT 0;DECLARE v_endRecord INTEGER DEFAULT 0;DECLARE cursql STATEMENT;DECLARE r_cur CURSOR WITH RETURN FOR cursql;IF(p_selectColumns = '') THEN SET p_selectColumns = '*';END IF;SET v_sql = 'SET (?) = (SELECT COUNT(' || p_primaryColumn || ') FROM ' || p_tableName || '';IF (p_strWhere <> '') THEN set v_sql = v_sql || ' WHERE ' || p_strWhere;END IF;SET v_sql = v_sql || ')';PREPARE cursql FROM v_sql;EXECUTE cursql INTO p_totalRecords;IF (MOD(p_totalRecords,p_pageSize) = 0) THEN SET p_totalPages = p_totalRecords / p_pageSize;ELSE SET p_totalPages = p_totalRecords / p_pageSize + 1;END IF;SET v_startRecord = (p_curPage - 1) * p_pageSize + 1;SET v_endRecord = p_curPage * p_pageSize;SET v_sql = 'SELECT * FROM (SELECT A.*,rownumber() over(order by '|| p_orderColumn || ' ' || p_orderStyle || ') rowid FROM (SELECT ' || p_selectColumns || ' FROM ' || p_tableName;IF(p_strWhere <> '') THEN SET v_sql = v_sql || ' WHERE ' || p_strWhere;END IF;SET v_sql = v_sql || ' order by ' || p_orderColumn || ' ' || p_orderStyle || ') A ) B WHERE ROWID >= ' || v_startRecord || ' AND ROWID <= '|| v_endRecord || '';PREPARE cursql FROM v_sql;OPEN r_cur;END;");

		ps.executeUpdate();

		DBUtil.closeDBResource(connection, ps, null, null);

	}

JAVA控制台报错如下：
Exception in thread "main" com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=END;OM v_sql;OPEN r_cur;;END, DRIVER=3.57.82
at com.ibm.db2.jcc.am.bd.a(bd.java:676)
at com.ibm.db2.jcc.am.bd.a(bd.java:60)
at com.ibm.db2.jcc.am.bd.a(bd.java:127)
at com.ibm.db2.jcc.am.km.c(km.java:2506)
at com.ibm.db2.jcc.am.km.d(km.java:2483)
at com.ibm.db2.jcc.am.km.a(km.java:1963)
at com.ibm.db2.jcc.t4.db.g(db.java:139)
at com.ibm.db2.jcc.t4.db.a(db.java:39)
at com.ibm.db2.jcc.t4.t.a(t.java:32)
at com.ibm.db2.jcc.t4.sb.h(sb.java:135)
at com.ibm.db2.jcc.am.km.eb(km.java:1934)
at com.ibm.db2.jcc.am.lm.ic(lm.java:2798)
at com.ibm.db2.jcc.am.lm.b(lm.java:3517)
at com.ibm.db2.jcc.am.lm.Wb(lm.java:690)
at com.ibm.db2.jcc.am.lm.executeUpdate(lm.java:673)
at com.test.db.TestCallProcedureFromDB2.main(TestCallProcedureFromDB2.java:16)

求大神解答！

...全文