java如何执行ms sql2000的存储过程

[vagrant_zy]

java中如何执行ms sql2000的存储过程

[loulou82]

<%
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@192.168.1.6:1521:db";

Class.forName(driver);
Connection conn = DriverManager.getConnection(strUrl, "scott", "tiger");

String procedure = "{call p_test2 (?) }";
CallableStatement cstmt = conn.prepareCall(procedure);
cstmt.setInt(1,33);
cstmt.executeUpdate();
%>

把驱动改为sqlserver即可~

[柯本]

我的一个java调用系统过程sp_tables并返回光标的过程,供你参考
------------------------------------------------------
import java.sql.*;

import sun.jdbc.*;

public class sqltest {

/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
try{
Connection conn;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn=DriverManager.getConnection("jdbc:odbc:Driver={SQL Server};Server=vpc98;uid=sa;pwd=;Database=master");
// String sql="";
Statement st=conn.createStatement();
ResultSet rs;
/* sql="select * from test";
rs=st.executeQuery(sql);
while (rs.next())
{
String id=rs.getString("postcode");
System.out.println(id);
}
if (rs!=null) rs.close();
*/
CallableStatement cs = conn.prepareCall("dbo.sp_tables");
rs = null;
int updateCount = -1;
boolean flag = cs.execute();
do{
updateCount = cs.getUpdateCount();
if(updateCount != -1){//说明当前行是一个更新计数
//处理.
cs.getMoreResults();
System.out.println(updateCount);
continue;//已经是更新计数了,处理完成后应该移动到下一行
//不再判断是否是ResultSet
}
rs = cs.getResultSet();
if(rs != null){//如果到了这里,说明updateCount == -1
//处理rs
System.out.println("ok");
while (rs.next())
{
String id=rs.getString("table_name");
System.out.println(id);
}
rs.close();
rs = null;
//cs.getMoreResults();
continue;
//是结果集,处理完成后应该移动到下一行
}
//如果到了这里,说明updateCount == -1 && rs == null,什么也没的了

}while(!(updateCount == -1 && rs == null));

if (st!=null)st.close();
if (conn!=null) conn.close();
}
catch(Exception e){
System.out.println(e.toString());
}

}

}

[zzhzzh204553]

使用接口CallableStatement接口,它是从Connection的方法得到.
CallableStatement prepareCall(String sql)

The interface used to execute SQL stored procedures. The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs. This escape syntax has one form that includes a result parameter and one that does not. If used, the result parameter must be registered as an OUT parameter. The other parameters can be used for input, output or both. Parameters are referred to sequentially, by number, with the first parameter being 1.

{?= call <procedure-name>[<arg1>,<arg2>, ...]}
{call <procedure-name>[<arg1>,<arg2>, ...]}

IN parameter values are set using the set methods inherited from PreparedStatement. The type of all OUT parameters must be registered prior to executing the stored procedure; their values are retrieved after execution via the get methods provided here.

A CallableStatement can return one ResultSet object or multiple ResultSet objects. Multiple ResultSet objects are handled using operations inherited from Statement.

For maximum portability, a call's ResultSet objects and update counts should be processed prior to getting the values of output parameters.