Java Reference
In-Depth Information
Calling stored procedures that return ResultSets is just as easy. For example, a simple stored
procedure, GET_LOGIN_FOR_USER, which gets the login data for a given UserName, can be defined
as follows:
CREATE PROCEDURE GET_LOGIN_FOR_USER @USERNAME VARCHAR(20)
AS SELECT *
FROM LOGIN
WHERE USERNAME = @USERNAME;
The example in
Listing 13-4
shows how to call the stored procedure GET_LOGIN_FOR_USER.
Listing 13-4: Calling a stored procedure that returns a ResultSet
package JavaDatabaseBible.ch13;
import java.sql.*;
import javax.sql.*;
public class CallableGetLogin{
private static String dbUserName = "sa";
private static String dbPassword = "dba";
public static void main(String args[]){
try {
Class.forName("com.inet.pool.PoolDriver");
com.inet.tds.TdsDataSource tds = new com.inet.tds.TdsDataSource();
tds.setServerName( "JUPITER" );
tds.setDatabaseName( "MEMBERS" );
tds.setUser( dbUserName );
tds.setPassword( dbPassword );
DataSource ds = tds;
Connection con = ds.getConnection(dbUserName,dbPassword);
CallableStatement cs = con.prepareCall("{call GET_LOGIN_FOR_USER(?)}");
cs.setString(1,"garfield");
ResultSet rs = cs.executeQuery();
ResultSetMetaData md = rs.getMetaData();
while(rs.next()){
for(int i=1;i<=md.getColumnCount();i++){
System.out.print(md.getColumnLabel(i)+"\t=\t");
if(md.getColumnType(i)==java.sql.Types.INTEGER)
System.out.println(rs.getInt(i));
else
System.out.println(rs.getString(i));
