public ResultSet getResultSet(CallableStatement ps) throws SQLException {
boolean isResultSet = ps.execute();
while (!isResultSet && ps.getUpdateCount() != -1) {
isResultSet = ps.getMoreResults();
}
return ps.getResultSet();
}
@Override
public void performAction(Connection connection, OperationObserver observer)
throws SQLException, Exception {
ProcedureTranslator transl = createTranslator(connection);
CallableStatement statement = (CallableStatement) transl.createStatement();
try {
// stored procedure may contain a mixture of update counts and result sets,
// and out parameters. Read out parameters first, then
// iterate until we exhaust all results
boolean hasResultSet = statement.execute();
// local observer to cache results and provide them to the external observer
// in the order consistent with other adapters.
Observer localObserver = new Observer(observer);
// read query, using local observer
while (true) {
if (hasResultSet) {
ResultSet rs = statement.getResultSet();
try {
RowDescriptor descriptor = describeResultSet(rs, processedResultSets++);
readResultSet(rs, descriptor, query, localObserver);
} finally {
try {
rs.close();
} catch (SQLException ex) {
}
}
} else {
int updateCount = statement.getUpdateCount();
if (updateCount == -1) {
break;
}
dataNode.getJdbcEventLogger().logUpdateCount(updateCount);
localObserver.nextCount(query, updateCount);
}
hasResultSet = statement.getMoreResults();
}
// read out parameters to the main observer ... AFTER the main result set
// TODO: I hope SQLServer does not support ResultSets as OUT parameters,
// otherwise
// the order of custom result descriptors will be messed up
readProcedureOutParameters(statement, observer);
// add results back to main observer
localObserver.flushResults(query);
} finally {
try {
statement.close();
} catch (SQLException ex) {
}
}
}
public ResultSet getResultSet(CallableStatement ps) throws SQLException {
boolean isResultSet = ps.execute();
// This assumes you will want to ignore any update counts
while (!isResultSet && ps.getUpdateCount() != -1) {
isResultSet = ps.getMoreResults();
}
// You may still have other ResultSets or update counts left to process here
// but you can't do it now or the ResultSet you just got will be closed
return ps.getResultSet();
}
/** @since 3.0 */
@Override
protected long longPkFromDatabase(DataNode node, DbEntity entity) throws Exception {
// handle CAY-588 - get connection that is separate from the connection in the
// current transaction.
// TODO (andrus, 7/6/2006) Note that this will still work in a pool with a single
// connection, as PK generator is invoked early in the transaction, before the
// connection is grabbed for commit... So maybe promote this to other adapters in
// 3.0?
Transaction transaction = Transaction.getThreadTransaction();
Transaction.bindThreadTransaction(null);
try {
Connection connection = node.getDataSource().getConnection();
try {
CallableStatement statement = connection.prepareCall("{call auto_pk_for_table(?, ?)}");
try {
statement.setString(1, entity.getName());
statement.setInt(2, super.getPkCacheSize());
// can't use "executeQuery"
// per http://jtds.sourceforge.net/faq.html#expectingResultSet
statement.execute();
if (statement.getMoreResults()) {
ResultSet rs = statement.getResultSet();
try {
if (rs.next()) {
return rs.getLong(1);
} else {
throw new CayenneRuntimeException(
"Error generating pk for DbEntity " + entity.getName());
}
} finally {
rs.close();
}
} else {
throw new CayenneRuntimeException(
"Error generating pk for DbEntity "
+ entity.getName()
+ ", no result set from stored procedure.");
}
} finally {
statement.close();
}
} finally {
connection.close();
}
} finally {
Transaction.bindThreadTransaction(transaction);
}
}
public void testProc1() throws Exception {
Statement stmt = con.createStatement();
dropProcedure(stmt, "#spTestExec");
dropProcedure(stmt, "#spTestExec2");
stmt.executeUpdate(" create procedure #spTestExec2 as " + "select 'Did it work?' as Result");
stmt.executeUpdate(
"create procedure #spTestExec as "
+ "set nocount off "
+ "create table #tmp ( Result varchar(50) ) "
+ "insert #tmp execute #spTestExec2 "
+ "select * from #tmp");
stmt.close();
CallableStatement cstmt = con.prepareCall("#spTestExec");
assertFalse(cstmt.execute());
assertEquals(1, cstmt.getUpdateCount());
// The JDBC-ODBC driver does not return update counts from stored
// procedures so we won't, either.
//
// SAfe Yes, we will. It seems like that's how it should work. The idea
// however is to only return valid update counts (e.g. not from
// SET, EXEC or such).
assertTrue(cstmt.getMoreResults());
boolean passed = false;
ResultSet rs = cstmt.getResultSet();
while (rs.next()) {
passed = true;
}
assertTrue("Expecting at least one result row", passed);
assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == -1);
cstmt.close();
// stmt.executeQuery("execute spTestExec");
}
/**
* Execute the desired Callable statement and then call dispResultSet to display the rows and
* columns
*
* @param stmt CallableStatement object to be processed
* @exception SQLException .
*/
public void displayRows(CallableStatement stmt) throws SQLException {
boolean results = stmt.execute();
int rsnum = 0; // Number of Result Sets processed
int rowsAffected = 0;
do {
if (results) {
ResultSet rs = stmt.getResultSet();
output("\n\nDisplaying ResultSet: " + rsnum + "\n");
dispResultSet(rs);
rsnum++;
rs.close();
} else {
rowsAffected = stmt.getUpdateCount();
if (rowsAffected >= 0) output(rowsAffected + " rows Affected.\n");
}
results = stmt.getMoreResults();
} while (results || rowsAffected != -1);
}
/** @param args */
public static void main(String[] args) throws Exception {
// Chargement du driver
Class.forName("com.mysql.jdbc.Driver");
// Driver driver = new com.mysql.jdbc.Driver();
// DriverManager.registerDriver(driver);
// Connexion au SGBD
java.sql.Connection connection =
DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "");
// Creation de la procedure
Statement statement = connection.createStatement();
statement.executeUpdate("DROP PROCEDURE IF EXISTS rechercherNom");
statement.executeUpdate(
"CREATE PROCEDURE rechercherNom(IN leNom VARCHAR(50))\n"
+ "BEGIN\n"
+ " SELECT * FROM Annuaire WHERE nom = leNom;\n"
+ " SELECT COUNT(*) FROM Annuaire WHERE nom = leNom;\n"
+ "END\n");
// Appel a la procedure stockee
CallableStatement callableStatement = connection.prepareCall("{call rechercherNom(?)}");
// Passage de kane comme valeur du premier parametre
callableStatement.setString(1, "kane");
boolean resultOK = callableStatement.execute();
if (resultOK) {
// Recuperation des ResultSet
ResultSet resultSet1 = callableStatement.getResultSet();
callableStatement.getMoreResults(Statement.KEEP_CURRENT_RESULT);
ResultSet resultSet2 = callableStatement.getResultSet();
// Traitement des informations
while (resultSet1.next()) {
for (int i = 0; i < resultSet1.getMetaData().getColumnCount(); i++) {
System.out.print(resultSet1.getObject(i + 1) + " ,");
}
System.out.println("");
}
resultSet2.next();
System.out.println("Nombre de lignes = " + resultSet2.getObject(1));
resultSet1.close();
resultSet2.close();
}
}
public static void main(String[] args) {
Connection conn = null;
ResultSet rs = null;
CallableStatement cStmt = null;
String dbURL = "jdbc:mysql://localhost/data_mining_assignment";
try {
// Load our driver into memory
Class.forName("com.mysql.jdbc.Driver");
// load credentials for SQL connection
Properties config = new Properties();
FileInputStream in = new FileInputStream("C:\\data-minning-config.properties");
config.load(in);
// establish connection to DB
conn = DriverManager.getConnection(dbURL, config);
// Create our procedure call and execute
cStmt = conn.prepareCall("{CALL getBinnedPoints()}");
boolean hadResults = cStmt.execute();
// while we have results to process
while (hadResults) {
// load the results into a result set
rs = cStmt.getResultSet();
// while data still in the result set
while (rs.next()) {
// read in each column
String team = rs.getString("Team");
String league = rs.getString("League");
String season = rs.getString("Season");
int spend = rs.getInt("Spend");
int income = rs.getInt("Income");
int nett = rs.getInt("Nett");
String points = rs.getString("Points");
int position = rs.getInt("Position");
// just print the data for now
System.out.print("Team: " + team);
System.out.print(" League: " + league);
System.out.print(" Season: " + season);
System.out.print(" Spend: " + spend);
System.out.print(" Income: " + income);
System.out.print(" Nett: " + nett);
System.out.print(" Points: " + points);
System.out.println(" Position: " + position);
}
// check if the procedure returns any more results... it never will :p
hadResults = cStmt.getMoreResults();
}
}
// catch any SQL exceptions
catch (SQLException ex) {
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
}
// catch any exception with loading driver
catch (Exception e) {
e.printStackTrace();
}
// clean up
finally {
// close the result set
if (rs != null) {
try {
rs.close();
System.out.println("Closed Result Set");
} catch (SQLException sqlEx) {
}
}
rs = null;
// close our procedure
if (cStmt != null) {
try {
cStmt.close();
System.out.println("Closed Statment");
} catch (SQLException sqlEx) {
}
cStmt = null;
}
// close our connection
if (conn != null) {
try {
conn.close();
System.out.println("Closed Connection");
} catch (SQLException sqlEx) {
}
conn = null;
}
}
}
/** @see java.sql.Statement#getMoreResults(int) */
public boolean getMoreResults(int current) throws SQLException {
return original.getMoreResults(current);
}
/** @see java.sql.Statement#getMoreResults() */
public boolean getMoreResults() throws SQLException {
return original.getMoreResults();
}
public void testProc2() throws Exception {
Statement stmt = con.createStatement();
String sqlwithcount =
"create procedure #multi1withcount as "
+ " set nocount off "
+ " select 'a' "
+ " select 'b' "
+ " create table #multi1withcountt (A VARCHAR(20)) "
+ " insert into #multi1withcountt VALUES ('a') "
+ " insert into #multi1withcountt VALUES ('a') "
+ " insert into #multi1withcountt VALUES ('a') "
+ " select 'a' "
+ " select 'b' ";
String sqlnocount =
"create procedure #multi1nocount as "
+ " set nocount on "
+ " select 'a' "
+ " select 'b' "
+ " create table #multi1nocountt (A VARCHAR(20)) "
+ " insert into #multi1nocountt VALUES ('a') "
+ " insert into #multi1nocountt VALUES ('a') "
+ " insert into #multi1nocountt VALUES ('a') "
+ " select 'a' "
+ " select 'b' ";
dropProcedure(stmt, "#multi1withcount");
dropProcedure(stmt, "#multi1nocount");
stmt.executeUpdate(sqlwithcount);
stmt.executeUpdate(sqlnocount);
stmt.close();
CallableStatement cstmt = con.prepareCall("#multi1nocount");
assertTrue(cstmt.execute());
ResultSet rs = cstmt.getResultSet();
assertTrue(rs.next());
assertTrue(rs.getString(1).equals("a"));
assertTrue(!rs.next());
assertTrue(cstmt.getMoreResults());
rs = cstmt.getResultSet();
assertTrue(rs.next());
assertTrue(rs.getString(1).equals("b"));
assertTrue(!rs.next());
assertTrue(cstmt.getMoreResults());
rs = cstmt.getResultSet();
assertTrue(rs.next());
assertTrue(!rs.next());
assertTrue(cstmt.getMoreResults());
rs = cstmt.getResultSet();
assertTrue(rs.next());
assertTrue(!rs.next());
assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == -1);
cstmt.close();
cstmt = con.prepareCall("#multi1withcount");
// The JDBC-ODBC driver does not return update counts from stored
// procedures so we won't, either.
//
// SAfe Yes, we will. It seems like that's how it should work. The idea
// however is to only return valid update counts (e.g. not from
// SET, EXEC or such).
assertTrue(cstmt.execute());
rs = cstmt.getResultSet();
assertTrue(rs.next());
assertTrue(rs.getString(1).equals("a"));
assertTrue(!rs.next());
assertTrue(cstmt.getMoreResults());
rs = cstmt.getResultSet();
assertTrue(rs.next());
assertTrue(rs.getString(1).equals("b"));
assertTrue(!rs.next());
assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == 1); // insert
assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == 1); // insert
assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == 1); // insert
assertTrue(cstmt.getMoreResults()); // select
rs = cstmt.getResultSet();
assertTrue(rs.next());
assertTrue(!rs.next());
assertTrue(cstmt.getMoreResults());
rs = cstmt.getResultSet();
assertTrue(rs.next());
assertTrue(!rs.next());
assertTrue(!cstmt.getMoreResults() && cstmt.getUpdateCount() == -1);
cstmt.close();
}
/**
* WL#6406 - Stacked diagnostic areas
*
* <p>"STACKED" in "GET [CURRENT | STACKED] DIAGNOSTICS" syntax was added in 5.7.0. Final behavior
* was implemented in version 5.7.2, by WL#5928 - Most statements should clear the diagnostic
* area.
*
* @throws SQLException
*/
public void testGetStackedDiagnostics() throws Exception {
if (!versionMeetsMinimum(5, 7, 2)) {
return;
}
// test calling GET STACKED DIAGNOSTICS outside an handler
final Statement locallyScopedStmt = this.stmt;
assertThrows(
SQLException.class,
"GET STACKED DIAGNOSTICS when handler not active",
new Callable<Void>() {
public Void call() throws Exception {
locallyScopedStmt.executeQuery("GET STACKED DIAGNOSTICS @num = NUMBER");
return null;
}
});
// test calling GET STACKED DIAGNOSTICS inside an handler
// (stored procedure is based on documentation example)
createTable("testGetStackedDiagnosticsTbl", "(c VARCHAR(8) NOT NULL)");
createProcedure(
"testGetStackedDiagnosticsSP",
"() BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN "
+ "GET CURRENT DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT; "
+ "SELECT 'current DA before insert in handler' AS op, @errno AS errno, @msg AS msg; " // 1st result
+ "GET STACKED DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT; "
+ "SELECT 'stacked DA before insert in handler' AS op, @errno AS errno, @msg AS msg; " // 2nd result
+ "INSERT INTO testGetStackedDiagnosticsTbl (c) VALUES('gnitset'); "
+ "GET CURRENT DIAGNOSTICS @num = NUMBER; "
+ "IF @num = 0 THEN SELECT 'INSERT succeeded, current DA is empty' AS op; " // 3rd
// result
+ "ELSE GET CURRENT DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT; "
+ "SELECT 'current DA after insert in handler' AS op, @errno AS errno, @msg AS msg; END IF; "
+ "GET STACKED DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO, @msg = MESSAGE_TEXT; "
+ "SELECT 'stacked DA after insert in handler' AS op, @errno AS errno, @msg AS msg; END; " // 4th result
+ "INSERT INTO testGetStackedDiagnosticsTbl (c) VALUES ('testing');INSERT INTO testGetStackedDiagnosticsTbl (c) VALUES (NULL); END");
CallableStatement cStmt = this.conn.prepareCall("CALL testGetStackedDiagnosticsSP()");
assertTrue(cStmt.execute());
// test 1st ResultSet
this.rs = cStmt.getResultSet();
assertTrue(this.rs.next());
assertEquals("current DA before insert in handler", this.rs.getString(1));
assertEquals(1048, this.rs.getInt(2));
assertEquals("Column 'c' cannot be null", this.rs.getString(3));
assertFalse(this.rs.next());
this.rs.close();
// test 2nd ResultSet
assertTrue(cStmt.getMoreResults());
this.rs = cStmt.getResultSet();
assertTrue(this.rs.next());
assertEquals("stacked DA before insert in handler", this.rs.getString(1));
assertEquals(1048, this.rs.getInt(2));
assertEquals("Column 'c' cannot be null", this.rs.getString(3));
assertFalse(this.rs.next());
this.rs.close();
// test 3rd ResultSet
assertTrue(cStmt.getMoreResults());
this.rs = cStmt.getResultSet();
assertTrue(this.rs.next());
assertEquals("INSERT succeeded, current DA is empty", this.rs.getString(1));
assertFalse(this.rs.next());
this.rs.close();
// test 4th ResultSet
assertTrue(cStmt.getMoreResults());
this.rs = cStmt.getResultSet();
assertTrue(this.rs.next());
assertEquals("stacked DA after insert in handler", this.rs.getString(1));
assertEquals(1048, this.rs.getInt(2));
assertEquals("Column 'c' cannot be null", this.rs.getString(3));
assertFalse(this.rs.next());
this.rs.close();
// no more ResultSets
assertFalse(cStmt.getMoreResults());
cStmt.close();
// test table contents
this.rs = this.stmt.executeQuery("SELECT * FROM testGetStackedDiagnosticsTbl");
assertTrue(this.rs.next());
assertEquals("testing", this.rs.getString(1));
assertTrue(this.rs.next());
assertEquals("gnitset", this.rs.getString(1));
assertFalse(this.rs.next());
this.rs.close();
}
public void sampleCode() {
// Variables for simple test case
String procname = "sp_timestampSample";
/* *CREATE PROCEDURE*
String createQuery1 =
"create table spt_timestampSample(f1 int, f2 char(5), f3 timestamp )";
String insertQuery1 =
"insert spt_timestampSample(f1,f2) values(1, 'Hello')";
// Sample Stored Procedure
String dropProc = "drop proc " + procname;
String createProc =
"create proc " + procname +
"(@p1 int, @p2 timestamp out)" +
" as " +
"select 'p1=' + convert(varchar(10),@p1) " +
"select @p2 = f3 from spt_timestampSample where f1=1" +
"select * from spt_timestampSample " +
"return 21";
*/
String sproc = "{? = call " + procname + "(?,?)}";
try {
/* *CREATE PROCEDURE*
// We will create a temp table which contains a timestamp column
// and we will insert a row. We will then execute a stored
// procedure which will returnt the timestamp column as an OUTPUT
// parameter
// Create our table
execDDL( createQuery1);
// Insert our row
execDDL( insertQuery1);
// Now create the Proc
execDDL( createProc);
*/
// Now execute our Sproc
CallableStatement cstmt = _con.prepareCall(sproc);
output("Executing: " + sproc + "\n");
// Declare the IN Params. Note, you must skip the Return Status
cstmt.setInt(2, 1961);
// Now declare our OUT Params
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.registerOutParameter(3, Types.VARBINARY);
boolean results = cstmt.execute();
int rsnum = 0; // Number of Result Sets processed
int rowsAffected = 0;
do {
if (results) {
ResultSet rs = cstmt.getResultSet();
output("\n\nDisplaying ResultSet: " + rsnum + "\n");
dispResultSet(rs);
rsnum++;
rs.close();
} else {
rowsAffected = cstmt.getUpdateCount();
if (rowsAffected >= 0) output(rowsAffected + " rows Affected.\n");
}
results = cstmt.getMoreResults();
} while (results || rowsAffected != -1);
String s = cstmt.getString(1);
String s2 = cstmt.getString(3);
// Now grab the same output parameter as VARBINARY
byte[] ts = cstmt.getBytes(3);
// Display the Output Parameters
output("OUT Param1=" + s + "\n");
output("OUT Param2 as String=" + s2 + "\n");
output("OUT Param2 as byte[]=" + toHexString(ts) + "\n");
cstmt.close();
/* *CREATE PROCEDURE*
// Drop our sproc
execDDL( dropProc);
*/
} catch (SQLException ex) {
displaySQLEx(ex);
} catch (java.lang.Exception ex) {
// Got some other type of exception. Dump it.
ex.printStackTrace();
}
}
