IBM Cloud Satellite: Build faster. Securely. Anywhere. Read more

Stored procedures used in callable and prepared statements


In a relational database application, the main advantage of using stored procedures over single SQL statements is that the query plan (or execution plan) is generated when the stored procedure is created, and the same query plan is reused with every execution of the stored procedure, saving a considerable amount of resources in the database server. When created once, they can be called by any database client, such as JDBC applications, as many times as it wants without the need for a new execution plan.

How to use stored procedures varies from one database server to another. A Database Management System (DBMS) such as Informix and DB2®, have different SQL syntax to execute stored procedures. This makes things difficult for application developers when they need to write code targeted to several DBMSes. A callable statement provides a method to execute stored procedures using the same SQL syntax in all DBMS systems.

Why use stored procedures

Suppose we have a JDBC application that needs to efficiently repeat a sequence of tasks again and again. We might think of using a Java™ method, but how many times do we want to do client/server communication to send and receive data? The database server will prepare and generate a query plan for every SQL statement sent by the application, which will consume some CPU time. While taking performance into consideration, using simple Java methods with single SQL statements may be a bad idea.

How about using a stored procedure, which is just a one-time task? Create a single SQL call, and you can call it from the JDBC application using a CallableStatement object, which acts as caller to the stored procedure on the server. Most of the business logic will reside on the stored procedure. This will help simplify the client code and will speed up the execution because the SQL statements included in the stored procedure were prepared and optimized when the stored procedure was created.

Calling stored procedures in JDBC applications

The Informix JDBC driver provides the Statement, PreparedStatement, and CallableStatement methods, which can be used to execute stored procedures. Which method you use depends on the characteristic of the stored procedure. For example, if the stored procedure returns a single value, you should use a JDBC Statement object. The following table provides some guidelines for what method to use for which stored procedure type.

Table 1. Table shows what JDBC method to use based on the respective stored procedure type
Stored procedure type JDBC method
Stored procedure requires no IN or OUT parameters Use a Statement object
Stored procedure with IN parameters Use a PreparedStatement object
Stored procedure with IN and OUT parameters Use a CallableStatement object

We are going to provide a sample of executing a stored procedure using the Informix JDBC methods in following cases:

  • Using Statement having no parameters
  • Using PreparedStatement having Input parameters
  • Using CallableStatement having output parameters
  • Named parameters in a CallableStatement
  • Overloaded stored procedures

Each topic mentioned above will be having the following details:

  • Syntax used to call the stored procedure within the Informix JDBC driver procedure
  • The schema of the stored procedure on the database
  • JDBC driver sample program with the output

Using Statement object having no parameters

The syntax used for executing a stored procedure without IN or OUT parameters is shown in Listing 1.

Listing 1. Syntax used for executing a stored procedure without IN or OUT parameters

There is no need to use the CallableStatement object with this type of stored procedure; you can use a simple JDBC statement. The code in Listing 2 shows the definition of the stored procedure using Informix SQL. The following example, GetCustName, returns a single result set that contains one column of data, which is a combination of the first name and last name of the first five contacts in the customer table.

Listing 2. Definition of the stored procedure using Statement object having no parameters

NOTE: This stored procedure sample (and all the others in this article) uses the tables created in the demonstration database stores_demo, which is a selectable option during the Informix IDS installation.

The JDBC Java code for the execution of the stored procedure is as shown in Listing 3.

Listing 3. JDBC code using Statement object having no parameters

Because the stored procedure returns a result set, we must use the Statement.executeQuery() method and fetch through the results using Listing 4 shows the output of the following program.

Listing 4. Program output from Listing 3
$java sample_Stored procedure_1
Ludwig          Pauli
Carole          Sadler
Philip          Currie
Anthony         Higgins
Raymond         Vector
George          Watson

Using PreparedStatement having input parameters

This is the most common of all stored procedures. A stored procedure requires parameters to pass data into the stored procedure for internal processing. You should use a PreparedStatement to deal with this type of stored procedure. The syntax is shown in Listing 5.

Listing 5. Syntax used for execution of stored procedures using prepared statements having input parameters

While you can use the Informix SQL syntax to execute the stored procedure (e.g., execute procedure procedure_name(?,?)), it is recommended to stick with the SQL escape sequence syntax.

The question mark characters (?) correspond to each of the input parameters required by the stored procedure. It acts as a placeholder for the values passed to the stored procedure.

To specify a value for a parameter, you can use one of the setter methods of the IfxPreparedStatement class. (e.g., pstmt.setInt()). The setter method you can use is determined by the data type of the IN parameter. In addition to the value of the parameter, the setter method takes the position of the parameter in the SQL statement.

Listing 6. Example showing a stored procedure with a signature

Listing 7. Java code snippet should be used in case of example shown in Listing 6
pstmt = con.prepareStatement("{call updmanu(?,?,?)}");
pstmt.setInt(1, manu_id);
pstmt.setString(2, manu_code);
pstmt.setDate(3, manu_date);

Listing 8 shows the following stored procedure is used to demonstrate how to use IN parameters.

Listing 8. Stored procedure example demonstrates how to use IN parameters

This stored procedure accepts a single input parameter named CUS_ID, which is an integer value, and it returns a list of orders for that customer_id. The Java code for calling this stored procedure is shown in Listing 9.

Listing 9. Java code for execution of stored procedure using prepared statements having input parameters

Executing the executeSprocInParams() method, we can see the output as shown in Listing 10.

Listing 10. Output of the program shown in Listing 9
$java sample_Stored procedure_2
Order Number    : 1001
Order Date      : 2008‑05‑20
Instructions    : express
Order Number    : 1003
Order Date      : 2008‑05‑22
Instructions    : express
Order Number    : 1011
Order Date      : 2008‑06‑18
Instructions    : express
Order Number    : 1013
Order Date      : 2008‑06‑22
Instructions    : express

Using CallableStatement having output parameter

If the stored procedure requires the use of IN or OUT parameters, you need to use a JDBC CallableStatement to handle the parameters. Only the IfxCallableStatement class (which extends from the Java CallableStatement) can deal with IN and OUT parameters.

Next, we’ll demonstrate how to call a stored procedure that returns one or more OUT parameters. These are the parameters that the stored procedure uses to return data to the calling application as single values, not as a result set as we saw earlier. The SQL syntax used for IN/OUT stored procedures is similar to what we showed before in Listing 5.

Listing 11. Syntax used for execution of stored procedures using callable statements having output parameters
{call procedure‑name(?,?,...)}

You must follow the correct order for the parameter (IN and OUT). Values for OUT parameters must be registered using the registerOutParameter() method of the CallableStatement class. Each OUT parameter must be specified in the correct order. As with the IN parameter, the first parameter of this method is the ordinal (or position) for the parameter —cstmt.registerOutParameter(2, Types.INTEGER);, for example.

The value you specify for the OUT parameter in the registerOutParameter method must be one of the Informix JDBC data types contained in java.sql.Types, which is internally converted to one of the native IDS data types.

For this example, we are going to use the following stored procedure, which uses the “items” table from the stores_demo database, as shown in Listing 12.

Listing 12. Stored procedure used to demonstrate how to use OUT parameters

This stored procedure returns a single OUT parameter (TotalPrice), which is an integer, based on the specified IN parameter (Order_ID), which is also an integer. The value returned in the OUT parameter is the sum of all the items from a specific order number contained in the items table.

Listing 13. Java code for execution of stored procedure using callable statements having output parameters

Named parameters in a CallableStatement

In the previous example, we used the position to identify each one of the parameters in the stored procedure. You can identify parameters by name, making the application code cleaner and easier to read.

The following example demonstrates how to use named parameters in a Java application. Note that parameter names correspond to the parameter names in the stored procedure’s definition.

Listing 14. How to use named parameters in a Java application

Parameters have to be indicated by index or name; you can’t mix both methods. These two Java samples produce the following output printing the total price for a specified order:

Listing 15. Output of two java samples in Listing 13 and 14
$java sample_Stored procedure_3
Total price for order 1002 is $1200

NOTE: These examples use the execute() method of the CallableStatement class to run the stored procedure. This is used because the stored procedure did not return a result set. If it did, the executeQuery() method should be used, as in the following example.

Listing 16. Stored procedure used to illustrate when to use executeQuery() method

The method in Listing 17 uses the executeQuery() to invoke the GetTotalByManu stored procedure.

Listing 17. Java code used to illustrate use of executeQuery() method
public static void executeStoredProcOUTParamsResulset(Connection con,String manu_id) {
    try {
        CallableStatement cstmt = con.prepareCall("{ call gettotalbymanu(?,?)}");
        cstmt.setString(1, manu_id);         
        cstmt.registerOutParameter(2, Types.CHAR);
        ResultSet rs = cstmt.executeQuery();;     
        System.out.println("Total for manufacturer '"+rs.getString(2).trim()+
        " ("+rs.getString(1)+") ' is $"+cstmt.getInt(2));
    catch (Exception e) {

The output of the program shown in Listing 17 is shown in Listing 18.

Listing 18. Program output for Java example in Listing 17
$java sample_Stored procedure_4
Total for manufacturer 'Hero (HRO)' is $2882

NOTE: If you don’t know how the stored procedure was defined, you can use the JDBC metadata routines to get information about the stored procedure, such as the name and type of the parameters it takes.

The following example uses the getProcedureColumns() method to get the name and the type of the gettotalbymanu procedure.

Listing 19. Java code

Alternately, you can check if the stored procedure has OUT parameters using the CallableStatement.hasOutParameter() method. If it was defined with OUT parameters, it will return TRUE, as shown in Listing 20.

Listing 20. Sample Java code

Calling stored procedures with multiple signatures

The Informix database server supports the overloaded stored procedures. You can have stored procedures with the same name, but different parameters (or signatures) to perform different operations based on the parameter it takes. A basic example could be these two procedures as shown in Listing 21 and Listing 22.

Listing 21. Stored procedure definition No. 1

Listing 22. Stored procedure definition No. 2

They both have the same name (GETORDERS), but the first one uses an INT parameter to get the orders for a specific customer, and the second has a DATE parameter to return the orders for a specific date, as shown in Listing 23.

Listing 23. Example for multiple signatures

To execute these stored procedures from a JDBC application, you must provide the parameter type in the SQL syntax so the Informix engine will know which stored procedure you want to run. Use the ::datatype prefix in the placeholder, as shown in Listing 24.

Listing 24. Examples for placeholder

Listing 25 shows how to execute the GETORDERS(DATE) procedure.

Listing 25. Java code to demonstrate how to use stored procedure with multiple signatures


This article has shown various ways to access simple and complex stored procedures from a JDBC application. The understanding gained from this article should help you transfer complex business logic into stored procedures and export them from your JDBC application.