1、我对源代码做了修改,如果调试,请完全copy
//:SqlseverTest.java
import java.sql.*;public class SqlserverTest {
public static void main(String[] args){
//String url="jdbc:microsoft:sqlserver://tsc:1433";
String dbUrl = "jdbc:odbc:CheDui";
Connection con;
String user;
ResultSet result=null;
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}catch(ClassNotFoundException ex){ex.printStackTrace();} try{
con = DriverManager.getConnection(dbUrl,"sa","");
CallableStatement cs = con.prepareCall("{call YongCheDanTJ_DW('2001-1-1','2003-1-1')}");
result=cs.executeQuery();
while (result.next()){
String id=result.getString("用车单位名称");
System.out.println(id);
}
cs.close();
con.close();
}catch(SQLException ex){System.err.println("SQLException: " + ex.getMessage());}
}
}2、我的存储过程如下:
CREATE PROCEDURE YongCheDanTJ_DW
(@b datetime,
@e datetime)
AS
SELECT YongCheDanTJDanWei1.用车单位编码, dbo.YongCheDW.用车单位名称,
YongCheDanTJDanWei1.行驶里程, YongCheDanTJDanWei1.行驶收费,
YongCheDanTJDanWei1.等时里程, YongCheDanTJDanWei1.等时收费,
YongCheDanTJDanWei1.金额合计, dbo.YongCheDW.单位类别编号
FROM dbo.YongCheDW INNER JOIN
dbo.YongCheDanTJDanWei(@b, @e) YongCheDanTJDanWei1 ON
dbo.YongCheDW.用车单位编码 = YongCheDanTJDanWei1.用车单位编码
GO3、使用:
F:\java>javac SqlserverTest.javaF:\java>java SqlserverTest
科研处
质量处
//:SqlseverTest.java
import java.sql.*;public class SqlserverTest {
public static void main(String[] args){
//String url="jdbc:microsoft:sqlserver://tsc:1433";
String dbUrl = "jdbc:odbc:CheDui";
Connection con;
String user;
ResultSet result=null;
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}catch(ClassNotFoundException ex){ex.printStackTrace();} try{
con = DriverManager.getConnection(dbUrl,"sa","");
CallableStatement cs = con.prepareCall("{call YongCheDanTJ_DW('2001-1-1','2003-1-1')}");
result=cs.executeQuery();
while (result.next()){
String id=result.getString("用车单位名称");
System.out.println(id);
}
cs.close();
con.close();
}catch(SQLException ex){System.err.println("SQLException: " + ex.getMessage());}
}
}2、我的存储过程如下:
CREATE PROCEDURE YongCheDanTJ_DW
(@b datetime,
@e datetime)
AS
SELECT YongCheDanTJDanWei1.用车单位编码, dbo.YongCheDW.用车单位名称,
YongCheDanTJDanWei1.行驶里程, YongCheDanTJDanWei1.行驶收费,
YongCheDanTJDanWei1.等时里程, YongCheDanTJDanWei1.等时收费,
YongCheDanTJDanWei1.金额合计, dbo.YongCheDW.单位类别编号
FROM dbo.YongCheDW INNER JOIN
dbo.YongCheDanTJDanWei(@b, @e) YongCheDanTJDanWei1 ON
dbo.YongCheDW.用车单位编码 = YongCheDanTJDanWei1.用车单位编码
GO3、使用:
F:\java>javac SqlserverTest.javaF:\java>java SqlserverTest
科研处
质量处
System.out.println("sql::::"+sql);
两句
放到cs.close();的后面
con.close();的前面就可以了
如果 CallableStatement 对象返回(多个也可)ResultSet 对象(通过调用 execute 方法),在检索 OUT 参数前应先检索所有的结果。这种情况下,为确保对所有的结果都进行了访问,必须对 Statement 方法 getResultSet、getUpdateCount 和 getMoreResults 进行调用,直到不再有结果为止。
检索完所有的结果后,就可用 CallableStatement.getXXX 方法来检索 OUT 参数中的值
如你的情况,你必须在完成数据集操作,也就是rs.getXXX()的操作之后才能
进行cs.getString(5),否则先取了out参数之后,rs就无法取数据了
SQLException: [Microsoft][SQLServer JDBC Driver]The requested data is not available.