I have a problem, I using jdbcTemplate exec a Sybase stored procedure returning mulit ResultSet,how could I get all the resultSet?
我有一个问题，我使用jdbcTemplate exec一个Sybase存储过程返回mulit ResultSet，我如何获得所有的ResultSet ?
if object_id('p_if_pms_spyh_upload') <> null
drop proc p_if_pms_spyh_upload
go
create procedure p_if_pms_spyh_upload
@status char(1) --- 订单状态 A/D
as
begin
declare @rq char(8) , @errcode int, @errmsg varchar(60)
create table #head (
op_type char(2) not null, -- 操作类型 A 表示新建 D 表示删除
xyqrid varchar(30) not null, -- 确认函编号
dh varchar(20) not null, -- 优惠单编号
yhdmc varchar(30) not null, -- 优惠单名称
type varchar(18) not null, -- 直降种类 04表示10有函
qsrq char(8) not null, -- 起始日期
jzrq char(8) not null, -- 截止日期
qssj char(8) not null, -- 起始时间
jzsj char(8) not null, -- 截止时间
jsfs varchar(8) not null, ---结算方式
oano varchar(30) not null, -- OA公文号
description varchar(200) not null, -- 优惠单描述
oprrq char(8) not null, -- 审批删除日期
oprsj char(6) not null -- 审批删除时间
)
create table #detail (
op_type char(2) not null, -- 操作类型 A 表示新建 D 表示删除
xyqrid varchar(30) not null, -- 确认函编号
dh varchar(20) not null, -- 优惠单编号
gdsid varchar(20) not null, -- 优惠商品名称
orgid varchar(10) not null, -- 销售组织编码
qdid char(4) not null, -- 渠道编码
tjlb varchar(20) not null, -- 商品组
pp varchar(20) not null, -- 品牌编码
gys varchar(20) not null, -- 供应商编码
fmid varchar(10) not null, -- 库位编码
yhed numeric(16,4) not null, -- 优惠额度
yhsl numeric(16,4) not null, -- 优惠数量
unit varchar(3) not null -- 销售单位
)
-- 删除1个月之前已成功发送的数据
select @rq=convert(char(8),dateadd(month,-1,getdate()),112)
select @errcode=0
select @errmsg = ''
begin tran
delete from IV_YHD_HEAD_SPYH_TO_PMS where processflag = 'S' and convert(char(8),lastmodified,112) = @rq
if @@transtate = 2 or @@transtate = 3 or @@error != 0
begin
select @errcode=-1
select @errmsg = '删除IV_YHD_HEAD_SPYH_TO_PMS一月之前的数据失败！'
goto procfail
end
delete from IV_YHD_DETAIL_SPYH_TO_PMS where processflag = 'S' and convert(char(8),lastmodified,112) = @rq
if @@transtate = 2 or @@transtate = 3 or @@error != 0
begin
select @errcode=-1
select @errmsg = '删除IV_YHD_DETAIL_SPYH_TO_PMS一月之前的数据失败！'
goto procfail
end
insert into #head
select op_type, xyqrid, dh, yhdmc, type, qsrq,jzrq, qssj, jzsj, jsfs, oano, description, oprrq, oprsj
from IV_YHD_HEAD_SPYH_TO_PMS where processflag = 'Y' and op_type = @status
update IV_YHD_HEAD_SPYH_TO_PMS set processflag = 'R'
from IV_YHD_HEAD_SPYH_TO_PMS a, #head b
where a.dh = b.dh and a.processflag = 'Y' and a.op_type = @status
if @@transtate = 2 or @@transtate = 3 or @@error != 0
begin
select @errcode=-1
select @errmsg = '更新IV_YHD_HEAD_SPYH_TO_PMS表数据出错！'
goto procfail
end
insert into #detail
select a.op_type,a.xyqrid,a.dh,a.gdsid,a.orgid,a.qdid,a.tjlb,a.pp,a.gys,a.fmid,a.yhed,a.yhsl,a.unit
from IV_YHD_DETAIL_SPYH_TO_PMS a,#head b
where a.dh = b.dh and a.processflag = 'Y' and a.op_type = @status
update IV_YHD_DETAIL_SPYH_TO_PMS set processflag = 'R'
from IV_YHD_DETAIL_SPYH_TO_PMS a, #head b
where a.dh = b.dh and a.processflag = 'Y' and a.op_type = @status
if @@transtate = 2 or @@transtate = 3 or @@error != 0
begin
select @errcode=-1
select @errmsg = '更新IV_YHD_DETAIL_SPYH_TO_PMS表数据出错！'
goto procfail
end
procsuccess:
commit trans
goto myexit
procfail:
rollback trans
goto myexit
myexit:
select @errcode,@errmsg
select op_type, xyqrid, dh, yhdmc, type, qsrq, jzrq,qssj, jzsj, jsfs, oano, description, oprrq, oprsj from #head
select op_type,xyqrid,dh,gdsid,orgid,qdid,tjlb,pp,gys,fmid,yhed,yhsl,unit from #detail
end
go
grant all on p_if_pms_spyh_upload to ws
go
I got you what you say and try something else. I show you the code blow:
我给你说了你说的话，然后试试别的。我给你们看一下密码:
ResultSet rs = null;
int updateCount = -1;
int index = 1;
do {
updateCount = cs.getUpdateCount();
if (updateCount != -1) { // it means it's a updateConut
cs.getMoreResults();
System.out.println("updateCount:" + updateCount);
continue;
}
rs = cs.getResultSet();
System.out.println(rs);
if (rs != null) {// it means updateCount == -1 and returns a resultSet
if (1 == index) {
index++ ;
if(rs.next()) {
int errCode = rs.getInt(1);
String errMsg = rs.getString(2);
System.out.println("errCode:" + errCode + " errMsg:" + errMsg);
if (!(Constants.PROC_ERRORCODE_SUCC == errCode)) {
logger.error("call sp errror:" + errMsg);
}
}
} else if (2 == index) {
index++ ;
rs = cs.getResultSet();
while (rs.next()) {
//proc resultSet
}
} else if (3 == index) {
index++ ;
rs = cs.getResultSet();
// proc resultSet
}
} else {
break;
}
cs.getMoreResults();
continue;
}
// it means updateCount == -1 && rs == null nothing left to return
} while (!(updateCount == -1 && rs == null));
this is the debug result
这是调试结果。
updateCount:1
updateCount:1
updateCount:1
updateCount:0
updateCount:0
updateCount:1
updateCount:1
updateCount:1
updateCount:1
org.apache.commons.dbcp.DelegatingResultSet@bd4e3c
errCode:0 errMsg:
updateCount:1
updateCount:1
updateCount:1
null
This is the correct code
这是正确的代码
ResultSet rs = null;
int updateCount = -1;
int index = 1;
do {
updateCount = cs.getUpdateCount();
if (updateCount != -1) {// it means it returns a updateCount
cs.getMoreResults();
continue;
}
rs = cs.getResultSet();
if (rs != null) {// it means it returns a ResultSet
if (1 == index) {
index++;
if(rs.next()) {
int errCode = rs.getInt(1);
String errMsg = rs.getString(2);
// System.out.println("errCode:" + errCode + " errMsg:" + errMsg);
if (!(Constants.PROC_ERRORCODE_SUCC == errCode)) {
logger.error("call sp execute error:" + errMsg);
}
}
rs.close(); // it should call close() here
} else if (2 == index) {
index++;
while (rs.next()) {
//proc the second resultSet
}
rs.close(); // it should call close() here
} else if (3 == index) {
index++;
while (rs.next()) {
//proc the second resultSet
}
rs.close(); // it should call close() here
} else {
break;
}
cs.getMoreResults();
continue;
}
} while (!(updateCount == -1 && rs == null)); //nothing to return
1
You keep calling the getMoreResults in a loop
在循环中调用getMoreResults。
CallableStatement cstmt;
ResultSet rs;
int i;
...
cstmt.execute(); // Call the stored procedure
while (cstmt.getMoreResults()){ // If we have more Results
rs = cstmt.getResultSet(); // Get the result set
while (rs.next()) {
i = rs.getInt(1);
System.out.println("Value from result set = " + i);
}
}
rs.close();
cstmt.close();
For your stored proc, it looks like you always return the three resultsets
对于存储的proc，看起来您总是返回三个结果集
The code should get you close, but if you want to debug it, you can do the loop like I showed you, and inside the loop, call ResultSetMetaData rsmd = rs.getMetaData(); and the rsmd object will tell you pretty much everything about that current resultset, this way you can get a true idea of what is being returned.
代码应该会使您接近，但是如果您想要调试它，您可以像我展示的那样执行循环，在循环内部，调用ResultSetMetaData rsmd = rs.getMetaData();rsmd对象会告诉你关于当前resultset的几乎所有信息，这样你就能知道返回的是什么了。
CallableStatement cstmt;
ResultSet rs;
int i;
...
cstmt.execute(); // Call the stored procedure
// select @errcode,@errmsg
rs = cstmt.getResultSet(); // Get the result set
while (rs.next()) {
// Process the resultset
}
rs.close();
// select op_type, xyqrid, dh, yhdmc, type, qsrq, jzrq,qssj, jzsj, jsfs, oano, description, oprrq, oprsj from #head
rs = cstmt.getResultSet(); // Get the result set
while (rs.next()) {
// Process the resultset
}
rs.close();
// select op_type,xyqrid,dh,gdsid,orgid,qdid,tjlb,pp,gys,fmid,yhed,yhsl,unit from #detail
rs = cstmt.getResultSet(); // Get the result set
while (rs.next()) {
// Process the resultset
}
rs.close();
cstmt.close();
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有