Post Reply 
 
Thread Rating:
  • 1 Votes - 4 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Stored procedure with Input/Output parms and a ResultSet
12-21-2010, 08:13 AM
Post: #1
Stored procedure with Input/Output parms and a ResultSet
public static int storedProcWithResultSet (String parms) throws Exception
// Stored procedure to be called.
CallableStatement cs = conn.prepareCall
("{? = call mypackage.p_astoreproc (?,?,?,?,?,?,?)}");

// register input parameters
cs.setString(2, "");
cs.setString(3, "");
cs.setString(4, parms);
// regsiter ouput parameters
cs.registerOutParameter(5, java.sql.Types.CHAR);
cs.registerOutParameter(6, java.sql.Types.CHAR);
cs.registerOutParameter(7, java.sql.Types.CHAR);

// Procedure execution
ResultSet rs = cs.executeQuery();

// Note that you need to retrieve the ResultSet _before_ retrieving
// OUTPUT parameters.
if ( rs == null)
System.out.println( "No resultSet!");
else {
// To retrieve columns info.
ResultSetMetaData rsmd = rs.getMetaData();
int nbCol = rsmd.getColumnCount();

int i = 0;
int j = 1;

Vector vResSet = new Vector();
while (rs.next ()) {
System.out.println( "Record " + (i+1));
// putting the ResultSet columns in a vector
for ( j = 1; j <= nbCol ; j++){
vResSet.insertElementAt(rs.getString(j), 0);
vResSet.addElement(rs.getString(j));
}

// Reading vector to print ResultSet data
for ( int k = 0 ; k < nbColonnes ; k++ ) {
if ( vResSet.elementAt(k) != null)
System.out.println( vResSet.elementAt(k).toString());
else
System.out.println( "Column " + (k+1) + " Null");
}
i++;
}
}

// Retrieving Strored Procedure OUTPUT parameters
System.out.println( "return code of Stored procedure = : "
+ cs.getInt(1));

// Retrieving OUTPUT parameters
for ( int i = 5 ; i <= 7; i ++)
System.out.println( "parameter " + i + " : "
+ cs.getString(i));

return cs.getInt(1);
}
Find all posts by this user
Quote this message in a reply
Post Reply 


Forum Jump:



Send your comments, Suggestions or Queries regarding this site at info@tutorials4u.net

Copyright © 2010 Tutorials4u.net All Rights Reserved