I am working with a stored procedure that I do not have access to change. The stored procedure takes an input array but has NO output parameters as it simply inserts data into 2 tables. How would I go about retrieving the number of records inserted (if any)?
For now, the old code looks similar to this:
String sqlQuery = "{call storedprocedure(?)}";
Array input = methodForBuildingInput();
CallableStatement statement;
Connection conn;
//error checking
statement = conn.prepareCall(sqlQuery);
statement.setArray(1, input);
int result = statement.executeUpdate();
log.write(result + " records were written");
The result is currently "1" every time since I think this functionality of executeUpdate doesn't work with stored procedures. I think I'll need to use the execute method with registerOutParameters, but have no idea how to properly use those when there are NO out parameters for me to retrieve. Is what I'm asking for possible without changing the stored procedure?
"sql%Rowcount" is the PL/SQL statement returning the number of rows affected by latest executed statement, since nobody knows what your "storedprocedure" does and how (apart that you say it 's inserting into 2 tables, implying you will need to get the "sql%Rowcount" at least twice inside the procedure itself...), it's impossible to give you an answer that, in any case, will not be a very smart way of doing things. The only piece of code knowing or in position to collect efficiently how many rows have been inserted is the procedure itself, so any efficient solution implies that you will have to modify it to return the result.
(diff of "# rows after" and "# of rows before" is for sure not of the smart class, but - if you have PK or unique indexes on all concerned tables - may not be as inefficient as it may look...)
(And the standard way to invoke a CallableStatement is "execute()", not "executeUpdate()" which is for DML "insert/update/delete".)