存储过程出站网关未返回 DB2 数据库的任何结果集

Question

我们正在使用 DB2 数据库。我通过以下方式配置了stored-proc-outbound-gateway：

<int-jdbc:stored-proc-outbound-gateway
auto-startup="true"
data-source="routingDataSource" 
stored-procedure-name="ZSPPQCIMGL"
skip-undeclared-results="true"
ignore-column-meta-data="true"  
use-payload-as-parameter-source = "false" 
expect-single-result="true" >
<int-jdbc:sql-parameter-definition name="P_CLMN" direction="IN" type="VARCHAR" />
<int-jdbc:sql-parameter-definition name="P_ALTC1" direction="IN" type="VARCHAR" />
<int-jdbc:sql-parameter-definition name="P_ALTC2" direction="IN" type="VARCHAR" />
<int-jdbc:sql-parameter-definition name="P_ALTC3" direction="IN" type="VARCHAR" />
<int-jdbc:sql-parameter-definition name="P_ALTC4" direction="IN" type="VARCHAR" />
<int-jdbc:sql-parameter-definition name="P_STRCRDATTIM" direction="IN" type="VARCHAR" />
<int-jdbc:sql-parameter-definition name="P_PAGESIZE" direction="IN" type="VARCHAR" />

<int-jdbc:parameter name="P_CLMN" expression="#xpath(payload, '//ClaimListResults/checkClaimSources/claimsCopy[1]/ClmNum')" />
<int-jdbc:parameter name="P_ALTC1" expression="#xpath(payload, '//ClaimListResults/checkClaimSources/claimsCopy[1]/AltClaim')" />
<int-jdbc:parameter name="P_ALTC2" expression="#xpath(payload, '//ClaimListResults/checkClaimSources/claimsCopy[1]/AltClaim')" />
<int-jdbc:parameter name="P_ALTC3" expression="#xpath(payload, '//ClaimListResults/checkClaimSources/claimsCopy[1]/AltClaim')" />
<int-jdbc:parameter name="P_ALTC4" expression="#xpath(payload, '//ClaimListResults/checkClaimSources/claimsCopy[1]/AltClaim')" />
<int-jdbc:parameter name="P_STRCRDATTIM" expression="#xpath(headers.originalRequest, '//lastCRDATTIM')" />
<int-jdbc:parameter name="P_PAGESIZE" expression="#xpath(headers.originalRequest, '//pageSize')" />
<int-jdbc:returning-resultset name="rowMapper" row-mapper="com.dsths.cs.awd.utils.ResultSetRowMapper"/>
</int-jdbc:stored-proc-outbound-gateway>

上述stored-proc-outbound-gateway没有返回它应该返回的任何ResultSet。我使用callablestatement编写了一个独立的 JDBC 代码，发现执行存储过程后返回ResultSet。但奇怪的是，stored-proc-outbound-gateway没有归还ResultSet.存储过程如下：

CREATE PROCEDURE ZSPPQCIMGL ( 
IN P_CLMN    CHAR(75),
IN P_ALTC1   CHAR(75),
IN P_ALTC2   CHAR(75),
IN P_ALTC3   CHAR(75),
IN P_ALTC4   CHAR(75),
IN P_STRCRDATTIM TIMESTAMP,
IN P_PAGESIZE  CHAR(10)
)

RESULT SETS 1
LANGUAGE SQL
SET OPTION COMMIT=*CS, DFTRDBCOL=*NONE, DYNDFTCOL=*NO

P1 : BEGIN
DECLARE C1 CURSOR WITH RETURN FOR 
SELECT 
BE2.CRDATTIM AS CRDATETIME, CONCAT(CHAR(BE2.CRDATTIM), CONCAT(BE2.RECORDCD, BE2.CRNODE))  AS  AwdObjectID,  CONCAT(CHAR(W67.PCRDATTIM), CONCAT(W67.PRECORDCD, W67.PCRNODE))  AS AWDTransId, W0.UNITCD AS BusinesArea, W0.OBJTYPE AS SourceType, W0.OBJECTID AS PhisyscalFileRef, CRUSERID as CreatedUserID,BE.CLMN AS ClaimNumber, W03.UNITCD as TransBusinessArea,W03.WRKTYPE as TransWorkType,W03.STATCD as TransStatus,W03.QUEUECD as TransQueue
FROM BESCAN BE JOIN W67U999S W67 ON 
W67.PCRDATTIM = BE.CRDATTIM
AND   W67.PRECORDCD = BE.RECORDCD
AND   W67.PCRNODE = BE.CRNODE
AND       BE.RECORDCD = 'T'
JOIN W03U999S W03 ON 
BE.CRDATTIM = W03.CRDATTIM
AND   BE.RECORDCD = W03.RECORDCD
AND   BE.CRNODE = W03.CRNODE
AND       BE.RECORDCD = 'T'
JOIN  W00U999S W0 ON
W0.CRDATTIM = W67.CCRDATTIM
AND   W0.RECORDCD = W67.CRECORDCD 
AND   W0.CRNODE = W67.CCRNODE
JOIN  BESCAN BE2 ON
W0.CRDATTIM = BE2.CRDATTIM
AND   W0.RECORDCD = BE2.RECORDCD
AND   W0.CRNODE = BE2.CRNODE
AND       BE2.RECORDCD = 'O'
WHERE (BE.CLMN  IN (P_CLMN,P_ALTC1,P_ALTC2,P_ALTC3,P_ALTC4 ))
                          AND BE2.CRDATTIM < P_STRCRDATTIM
UNION                       

SELECT BE2.CRDATTIM AS CRDATETIME, CONCAT(CHAR(BE2.CRDATTIM), CONCAT(BE2.RECORDCD, BE2.CRNODE))  AS  AwdObjectID,  CONCAT(CHAR(W67.PCRDATTIM), CONCAT(W67.PRECORDCD, W67.PCRNODE))  AS AWDTransId, W0.UNITCD AS BusinesArea, W0.OBJTYPE AS SourceType, W0.OBJECTID AS PhisyscalFileRef, CRUSERID as CreatedUserID 
,BE.CLMN AS ClaimNumber, W03.UNITCD as TransBusinessArea,W03.WRKTYPE as TransWorkType,W03.STATCD as TransStatus,W03.QUEUECD as TransQueue
FROM BEAG BE JOIN W67U999S W67 ON 
W67.PCRDATTIM = BE.CRDATTIM
AND   W67.PRECORDCD = BE.RECORDCD
AND   W67.PCRNODE = BE.CRNODE
AND       BE.RECORDCD = 'T'
JOIN W03U999S W03 ON 
BE.CRDATTIM = W03.CRDATTIM
AND   BE.RECORDCD = W03.RECORDCD
AND   BE.CRNODE = W03.CRNODE
AND       BE.RECORDCD = 'T'
JOIN  W00U999S W0 ON
W0.CRDATTIM = W67.CCRDATTIM
AND   W0.RECORDCD = W67.CRECORDCD 
AND   W0.CRNODE = W67.CCRNODE
JOIN  BEAG BE2 ON
W0.CRDATTIM = BE2.CRDATTIM
AND   W0.RECORDCD = BE2.RECORDCD
AND   W0.CRNODE = BE2.CRNODE
AND       BE2.RECORDCD = 'O'
WHERE (BE.CLMN  IN (P_CLMN,P_ALTC1,P_ALTC2,P_ALTC3,P_ALTC4 ))
                          AND BE2.CRDATTIM < P_STRCRDATTIM
UNION                      
SELECT BE2.CRDATTIM AS CRDATETIME, CONCAT(CHAR(BE2.CRDATTIM), CONCAT(BE2.RECORDCD, BE2.CRNODE))  AS  AwdObjectID,  CONCAT(CHAR(W67.PCRDATTIM), CONCAT(W67.PRECORDCD, W67.PCRNODE))  AS AWDTransId, W0.UNITCD AS BusinesArea, W0.OBJTYPE AS SourceType, W0.OBJECTID AS PhisyscalFileRef, CRUSERID as CreatedUserID 
,BE.CLMN AS ClaimNumber, W03.UNITCD as TransBusinessArea,W03.WRKTYPE as TransWorkType,W03.STATCD as TransStatus,W03.QUEUECD as TransQueue
FROM BECLAIM BE JOIN W67U999S W67 ON 
W67.PCRDATTIM = BE.CRDATTIM
AND   W67.PRECORDCD = BE.RECORDCD
AND   W67.PCRNODE = BE.CRNODE
AND       BE.RECORDCD = 'T'
JOIN W03U999S W03 ON 
BE.CRDATTIM = W03.CRDATTIM
AND   BE.RECORDCD = W03.RECORDCD
AND   BE.CRNODE = W03.CRNODE
AND       BE.RECORDCD = 'T'
JOIN  W00U999S W0 ON
W0.CRDATTIM = W67.CCRDATTIM
AND   W0.RECORDCD = W67.CRECORDCD 
AND   W0.CRNODE = W67.CCRNODE
JOIN  BECLAIM BE2 ON
W0.CRDATTIM = BE2.CRDATTIM
AND   W0.RECORDCD = BE2.RECORDCD
AND   W0.CRNODE = BE2.CRNODE
AND       BE2.RECORDCD = 'O'
WHERE (BE.CLMN = P_CLMN OR BE.ALTC IN (P_ALTC1,P_ALTC2,P_ALTC3,P_ALTC4 ))
                          AND BE2.CRDATTIM < P_STRCRDATTIM
ORDER BY AwdObjectID desc
FETCH FIRST 10 ROWS ONLY;

OPEN C1 ;
END P1;

这里可能有什么问题？

CallableStatement代码如下：

package com.test;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import com.ibm.db2.jcc.DB2Types;

public class FolderRestructure {
private static final String DB_DRIVER = "com.ibm.as400.access.AS400JDBCDriver";
private static final String DB_CONNECTION = "jdbc:as400://XXX.40.103.XXX/ABC";
private static final String DB_USER = "ABC";
private static final String DB_PASSWORD = "DEF";

public static void main(String[] argv) {
try {
callOracleStoredProcCURSORParameter();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
private static void callOracleStoredProcCURSORParameter()
throws SQLException {
Connection dbConnection = null;
CallableStatement callableStatement = null;
ResultSet rs = null;
String getDBUSERCursorSql = "{call ZSPPQCIMGL(?,?,?,?,?,?,?)}";
// String getDBUSERCursorSql = "{call ZSPQALLIMGLKP(?,?,?,?,?,?,?,?)}";
//call ZSPPQCIMGL('12021000012A','CHAVA-00001','CHAVA-00001','CHAVA-00001','CHAVA-00001','2017-08-04-23.59.59.999999','4');
try {
Timestamp timestamp = null;
try {
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.SSSSSS");
java.util.Date parsedDate = dateFormat.parse("2017-08-08 23:59:59.999999");
timestamp = new java.sql.Timestamp(parsedDate.getTime());
} catch(ParseException e) { //this generic but you can control another types of exception
System.out.println(e.getMessage());
}
dbConnection = getDBConnection();
callableStatement = dbConnection.prepareCall(getDBUSERCursorSql);
callableStatement.setString(1, "12021000012A");
callableStatement.setString(2, "CHAVA-00001");
callableStatement.setString(3, "CHAVA-00001");
callableStatement.setString(4, "CHAVA-00001");
callableStatement.setString(5, "CHAVA-00001");
callableStatement.setTimestamp(6, timestamp);
callableStatement.setString(7, "4");
//callableStatement.registerOutParameter(8, DB2Types.CURSOR);
//P_CLMN=12021000012A, P_STRCRDATTIM=9999-99-99 99:99:99.999999, P_PAGESIZE=4, P_ALTC3=CHAVA-00001, P_ALTC4=CHAVA-00001, P_ALTC1=CHAVA-00001, P_ALTC2=CHAVA-00001
/* callableStatement.setString(1, "DT207030");
callableStatement.setString(2, "KKMTEST2000");
callableStatement.setString(3, "");
callableStatement.setString(4, "2XSRCTYP");
callableStatement.setString(5, "0000-00-00 00:00:00.000000");
callableStatement.setString(6, "9999-99-99 99:99:99.999999");
callableStatement.setString(7, "9999-99-99 99:99:99.999999");*/

// execute getDBUSERCursor store procedure
rs = callableStatement.executeQuery();
// get cursor and cast it to ResultSet
//rs = (ResultSet) callableStatement.getObject(7);
while (rs.next()) {
//  CASEID , CASEBA, CASEWK,  CASESTA,  CASEQUE, ISSUEID , ISSUEBA,  ISSUEWK,  ISSUESTA,  ISSUEQUE,  ISSUEASIGNID,
//         OBJECTID ,  OBJECTBA, OBJECTWK, OBJECTPHYFILEID,  OBJECTCREATEDUSERID
//W0.OBJTYPE AS SourceType, W0.OBJECTID AS PhisyscalFileRef, CRUSERID as CreatedUserID,BE.CLMN AS ClaimNumber, 
//W03.UNITCD as TransBusinessArea,W03.WRKTYPE as TransWorkType,W03.STATCD as TransStatus,W03.QUEUECD as TransQueue
System.out.println("CRDATIM : " + rs.getString("CRDATETIME"));
System.out.print("  AwdObjectID : " + rs.getString("AwdObjectID"));
System.out.print("  AWDTransId : " + rs.getString("AWDTransId"));
System.out.print("  BusinesArea : " + rs.getString("BusinesArea"));
System.out.print("  SourceType : " + rs.getString("SourceType"));
System.out.print("  PhisyscalFileRef : " + rs.getString("PhisyscalFileRef"));
System.out.print("  CreatedUserID : " + rs.getString("CreatedUserID"));
System.out.print("  ClaimNumber : " + rs.getString("ClaimNumber"));
System.out.print("  TransBusinessArea : " + rs.getString("TransBusinessArea"));
System.out.print("  TransWorkType : " + rs.getString("TransWorkType"));
System.out.print("  TransStatus : " + rs.getString("TransStatus"));
System.out.print("  TransQueue : " + rs.getString("TransQueue"));
/*   String userid = rs.getString("ISSUEID");
String userName = rs.getString("ISSUEBA");
String createdBy = rs.getString("ISSUEWK");
String createdDate = rs.getString("ISSUESTA");
System.out.print("  ISSUEID : " + userid);
System.out.print("  ISSUEBA : " + userName);
System.out.print("  ISSUEWK : " + createdBy);
System.out.print("  ISSUESTA : " + createdDate);*/
// System.out.println("created : " + rs.getString("CASEQUE"));
/* System.out.println("AwdObjectID : " + userid);
System.out.println("BusinesArea : " + userName);
System.out.println("SourceType : " + createdBy);
System.out.println("PhisyscalFileRef : " + createdDate);
System.out.println("created : " + rs.getString("CASEQUE"));*/
}
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if (rs != null) {
rs.close();
}
if (callableStatement != null) {
callableStatement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER);
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try {
dbConnection = DriverManager.getConnection(
DB_CONNECTION, DB_USER,DB_PASSWORD);
return dbConnection;
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return dbConnection;
}
}

Answer 1

看起来您肯定从您的过程中返回 CURSOR。所以，这很function。

试试这个：is-function="true"

<xsd:attribute name="is-function" default="false">
<xsd:annotation>
<xsd:documentation>
If "true", a SQL Function is called. In that case
the "stored-procedure-name" attribute defines
the name of the called function.
</xsd:documentation>
</xsd:annotation>
<xsd:simpleType>
<xsd:union memberTypes="xsd:boolean xsd:string" />
</xsd:simpleType>
</xsd:attribute>