SQLSERVER:
call.registerOutParameter(19,Types.VARCHAR);
callly.execute();
id=callly.getString(19);//这行报错[Microsoft][SQLServer JDBC Driver]The requested data is not available存储过程在数据库里测试了。正常输出、、
call.registerOutParameter(19,Types.VARCHAR);
callly.execute();
id=callly.getString(19);//这行报错[Microsoft][SQLServer JDBC Driver]The requested data is not available存储过程在数据库里测试了。正常输出、、
{
ResultSet myrs = null;
String sql = "{call PROC_AUTOID(?,?)}";
String temp = "";
String tempid = "";
try
{
conn = DriverManager.getConnection(sConnStr, UseName, PassWord);
CallableStatement callablestatement = conn.prepareCall(sql);
callablestatement.setString(1, p);
callablestatement.registerOutParameter(2, 1);
myrs = callablestatement.executeQuery();
myrs.next();
temp = myrs.getString(1);
tempid = callablestatement.getString(2);
myrs.close();
myrs = null;
callablestatement.close();
callablestatement = null;
}
catch(Exception e)
{
System.err.println("executeProc:" + e.getMessage());
}
finally
{
myrs = null;
}
return tempid;
}
Connection cn = ConnectionPools.getConnection();
Connection ly = ConnectionPools.getConnection("0");
CallableStatement call,callly;
ResultSet rs=null;
String pid,id,xlh;
Statement st =null;
int count=0;
try {
call= cn.prepareCall("{call if_locale_p_matbdfpos}");
callly = ly.prepareCall("{call ghlycgdb.dbo.insDDMX(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }");
callly.registerOutParameter(2,Types.VARCHAR);
st = cn.createStatement();
rs=call.executeQuery();
ly.setAutoCommit(false);
cn.setAutoCommit(false);
while(rs.next()){
id=rs.getString("id");
pid=rs.getString("powerid");
try{
System.out.println(Integer.parseInt(pid));
callly.setInt(1,Integer.parseInt(pid));
callly.setString(3,rs.getString("mee"));
callly.setInt(4,rs.getInt("bstmen"));
callly.setString(5,rs.getString("berdtm"));
callly.setString(6,rs.getString("lifadr"));
callly.setString(7, rs.getString("aendtm"));
callly.setString(8,rs.getString("firknz"));
callly.setInt(9,rs.getInt("artnum"));
callly.setInt(10,rs.getInt("bdfnum"));
callly.setString(11,rs.getString("planname"));
callly.setString(12,rs.getString("plantype"));
callly.setString(13,rs.getString("bdfmel"));
callly.setString(14,rs.getString("orgknz"));
callly.setInt(15,rs.getInt("bdfmen"));
callly.setString(16,rs.getString("bdfprio"));
callly.setString(17,rs.getString("matbes"));
callly.setFloat(18,rs.getFloat("verpre"));
callly.setInt(19,rs.getInt("wlbh"));
callly.execute();
xlh=callly.getString(2);
st.execute("update if_cache_matbdfpos set loc_status=10,lyxlh=cast('"+xlh+"' as int) where id="+id);
ly.commit();
cn.commit();
}catch(Exception e){
e.printStackTrace();
ly.rollback();
cn.rollback();
}
count++;
}
ly.setAutoCommit(true);
cn.setAutoCommit(true);
call.close();
callly.close();
} catch (SQLException e) {
e.printStackTrace();
}
ConnectionPools.close("0",ly);
ConnectionPools.close(cn);
而且这个存储过程不复杂,插入数据,返回插入的ID号。。
在xlh=callly.getString(2);报错
[Microsoft][SQLServer JDBC Driver]The requested data is not available服务器1的存储过程:if_locale_p_matbdfpos读取数据集合。。
服务器2的存储过程:insDDMX(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)。把服务器1的数据逐条插入第二个数据库中。并返回插入的成功的ID标识号
public void testDD() throws Exception{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection cn = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=tsdb","sa","at911");
String ls_sql = "{call insDDMX (218,?,'个',9,null,null,'2006-07-18 16:59:22.000',null,192183767,66154,null,'需求','bfs',null,5,null,null,2.0,92078)}";
java.sql.CallableStatement lp = cn.prepareCall( ls_sql ) ;
lp.registerOutParameter(1, java.sql.Types.VARCHAR );
lp.execute();
System.out.println(lp.getString(1));//这里出错[Microsoft][SQLServer JDBC Driver]The requested data is not available lp.close();
cn.close();
}
}
callly.execute();
id=callly.getString(19);//这行报错[Microsoft][SQLServer JDBC Driver]The requested data is not 上面贴出来了啊。
在xlh=callly.getString(2);报错
[Microsoft][SQLServer JDBC Driver]The requested data is not available服务器1的存储过程:if_locale_p_matbdfpos读取数据集合。。
服务器2的存储过程:insDDMX(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)。把服务器1的数据逐条插入第二个数据库中。并返回插入的成功的ID标识号你参数一会2 一会是19
是不是参数位置没对上
输出参数呢
public void testDD() throws Exception{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection cn = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=tsdb","sa","at911");
String ls_sql = "{call insDDMX (218,?,'个',9,null,null,'2006-07-18 16:59:22.000',null,192183767,66154,null,'需求','bfs',null,5,null,null,2.0,92078)}";
java.sql.CallableStatement lp = cn.prepareCall( ls_sql ) ;
lp.registerOutParameter(1, java.sql.Types.VARCHAR );
lp.execute();
System.out.println(lp.getString(1));//这里出错[Microsoft][SQLServer JDBC Driver]The requested data is not available lp.close();
cn.close();
}
}
lp.registerOutParameter(1, java.sql.Types.VARCHAR );
。。这里应该是2吧
lp.registerOutParameter(2, java.sql.Types.VARCHAR );
我写的其他得存储过程都是select语句。所以可以用execute()方法执行。。