This section describes how to capture ResultSet objects by calling executeQuery() to execute the CALL statement.
There two ways to execute the CALL statement enclosed in the CallableStatement object:
rs.executeUpdate() - Execute the CALL statement without collecting any returning ResultSet.
Use this method when the stored procedure is not returning any result sets or you want to ignore them.
res = rs.executQuery() - Execute the CALL statement and collect the returning ResultSet objects.
Use this method when the stored procedure is returning result sets and you are want to collect them.
Here is a sample program that show you how to collect the returning ResultSet of the HelloWorld() procedure:
/**
* MySqlCallExecuteQuery.java
* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
*/
import java.sql.*;
public class MySqlCallExecuteQuery {
public static void main(String [] args) {
Connection con = null;
try {
com.mysql.jdbc.jdbc2.optional.MysqlDataSource ds
= new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();
ds.setServerName("localhost");
ds.setPortNumber(3306);
ds.setDatabaseName("HerongDB");
ds.setUser("Herong");
ds.setPassword("TopSecret");
con = ds.getConnection();
// Create CallableStatement
CallableStatement cs = con.prepareCall("CALL HelloWorld()");
// Execute the call statement and collect the ResultSet
ResultSet res = cs.executeQuery();
// Retrieving the result
res.next();
System.out.println("Result: "+res.getString("Message"));
// Close resource
cs.close();
con.close();
} catch (Exception e) {
System.err.println("Exception: "+e.getMessage());
e.printStackTrace();
}
}
}
This time the result of the query "SELECT 'Hello world!' AS Message;" in HelloWorld() procedure
was successfully collected:
