Sidebar

When calling a stored procedure should I use execute, executeUpdate, executeQuery, or executeBatch?

+1 vote
4.2K views
asked Jul 23, 2019 by rich-c-2789 (15,250 points)
edited Jul 23, 2019 by rich-c-2789
I need to call a stored procedure but I don't know which method to use to execute it. Does it matter?

1 Answer

+1 vote
 
Best answer

First, these methods are not directly related to stored procedures. They are methods on the JDBC API's Statement interface. See the javadoc for Statement. The following recommendations apply any time a statement or anything that extends statement (PreparedStatement, CallableStatement) is used. It makes no difference Whether it is used to execute DML queries like SELECT, UPDATE, DELETE or to execute a stored procedure.

However, stored procedures add some confusion in determining what to use. If someone else wrote the stored procedure we may have to do a bit of research to find out the implementation details in which to consider how to use the following guidelines.

 

executeBatch - returns an int[] of update counts.

  • Use this to send multiple commands to the database as a batch. It returns an array of ints with the update counts when all commands execute successfully. Batching can be useful to increase performance if several commands can be sent to the database for execution at that same time.  Batching also requires other code changes not covered in this answer.

 

executeUpdate - returns an int for the update count

  • For queries: Use for queries like INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, ETC. that return an status like rows affected.
  • For stored procedures: Use when the stored procedure does not return a result set.

 

executeQuery - returns a ResultSet.

  • For queries: Use when executing SELECT queries that return a result set.
  • For stored procedures: Use when the stored procedure returns one result set.

For an example of using executeQuery in QIE see this KB article.

execute - The return value is a boolean. When true the query returned one or more ResultSets. When false an int or nothing is returned.

  • For queries: Used for any kind of query or when the more the one ResultSet or update count is returned.
  • For stored procedures: Use this method if you do not know what a stored procedure returns or when a stored procedure returns multiple ResultSets or update counts. If the execute method returns true you can then check for returned result sets etc.

NOTE: When a stored procedure returns multiple ResultSets or UpdateCounts it is recommened to '...retrieve all ResultSet(s) and Update Counts first followed by retrieving the OUT type parameters and return values' as per "The Stored Procedure" section of this reference.

 

This example demonstrates using execute with multiple ResultSets in QIE. It is a conversion of the "Using Muliple Result Sets" example into a QIE script.

NOTE: How the "results" variable stores a boolean that is used to determine if there are ResultSets to be processed. Compare that to the example mentioned above for executeQuery.

//STEP 1: Obtain a connection
var connection = qie.getDbConnection('AdventureWorks2012');

var statement;
try {
   //STEP 2: Create a statement
   qie.debug("Creating statement...");
   statement = connection.prepareCall("{CALL [dbo].[storedProcedureWithTwoResults]}");
   // statement = connection.prepareCall("EXEC [dbo].[storedProcedureWithTwoResults] ");

   //STEP 3: Execute stored procedure
   var results = statement.execute();

   //STEP 4: Loop through the available result sets.
   var rsCount = 0;
   do {
      if (results) {
         var rs = statement.getResultSet();
         rsCount++;

         //STEP 5: Show data from the result set.
         qie.debug("RESULT SET #" + rsCount);
         while (rs.next()) {
            qie.debug(rs.getString("LastName") + ", " + rs.getString("FirstName"));
         }
      }
      results = statement.getMoreResults();
   } while (results);

   //STEP 6: Clean-up environment
   statement.close();
   connection.close();
} catch(e) {
   qie.debug(e);
} finally {
   try{
      if (statement !== null) {
         statement.close();
      }
   } catch (e) {
      qie.debug(e);
   }
   try{
      if (connection !== null) {
         connection.close();
      }
   } catch (e) {
      qie.debug(e);
   }
}

For more about the details of the JDBC API see the JDBC javadocs and the JDBC tutorial.

Also see the javadocs for the database vendor specific JDBC driver. Here is Microsoft's JDBC driver for SQL Server documentation. This section covers Using Statements with stored procedures.

Also see these topics:

Topic Description
Using a Stored Procedure with No Parameters Describes how to use the JDBC driver to run stored procedures that contain no input or output parameters.
Using a Stored Procedure with Input Parameters Describes how to use the JDBC driver to run stored procedures that contain input parameters.
Using a Stored Procedure with Output Parameters Describes how to use the JDBC driver to run stored procedures that contain output parameters.
Using a Stored Procedure with a Return Status Describes how to use the JDBC driver to run stored procedures that contain return status values.
Using a Stored Procedure with an Update Count Describes how to use the JDBC driver to run stored procedures that return update counts.
answered Jul 23, 2019 by rich-c-2789 (15,250 points)
edited Jul 23, 2019 by rich-c-2789
...