Introduction

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 below:

{call procedure-name}

There is no need to use the CallableStatement object with this type of stored procedure; you can use a simple JDBC statement. The next code 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:

CREATE PROCEDURE GETCUSTNAME() RETURNING LVARCHAR AS NAME; DEFINE W_NAME LVARCHAR; FOREACH SELECT FIRST 5 FNAME || ' ' || LNAME INTO W_NAME FROM CUSTOMER RETURN W_NAME WITH RESUME; END FOREACH; END PROCEDURE;

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 below:

public static void executeStoredprocNoParams(Connection con) { try { Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("{call GETCUSTNAME}"); while (rs.next()) { System.out.println(rs.getString("Name")); } rs.close(); stmt.close(); } catch (Exception e) { e.printStackTrace(); } }

Because the stored procedure returns a result set, we must use the Statement.executeQuery() method and fetch through the results using ResultSet.next(). The code below shows the output of the following program:

(newline)$java sample_Stored procedure_1(newline)                (newline)Ludwig          Pauli(newline)Carole          Sadler(newline)Philip          Currie(newline)Anthony         Higgins(newline)Raymond         Vector(newline)George          Watson(newline)                (newline)$

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 below:

{call procedure-name(?,?,...)}

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:

(newline)UPDMANU(INT MN_CODE, CHAR(10) MN_NAME, DATE MN_UPD);

(newline)pstmt = con.prepareStatement("{call updmanu(?,?,?)}");(newline)pstmt.setInt(1, manu_id);(newline)pstmt.setString(2, manu_code);(newline)pstmt.setDate(3, manu_date);

The code below shows the following stored procedure is used to demonstrate how to use IN parameters:

CREATE PROCEDURE GETORDERS(CUS_ID INT) RETURNING INT AS ORDER_NUM, DATE AS ORDER_DATE, LVARCHAR AS SHINSTRUC; DEFINE W_ORDERN INT; DEFINE W_ORDERD DATE; DEFINE W_SHIP LVARCHAR; FOREACH SELECT ORDER_NUM,ORDER_DATE,SHIP_INSTRUCT INTO W_ORDERN,W_ORDERD,W_SHIP FROM ORDERS WHERE ORDERS.CUSTOMER_NUM=CUS_ID RETURN W_ORDERN,W_ORDERD,W_SHIP WITH RESUME; END FOREACH; END PROCEDURE;

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 below:

public static void executeSprocInParams(Connection con, int c_id) { try { PreparedStatement pstmt = con.prepareStatement("{call getorders(?)}"); pstmt.setInt(1, c_id); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { System.out.println("Order Number\t: " rs.getString("order_num")); System.out.println("Order Date\t: " rs.getString("order_date")); System.out.println("Instructions\t: " rs.getString("shinstruc")); System.out.println(); } rs.close(); pstmt.close(); } catch (Exception e) { e.printStackTrace(); } }

Executing the executeSprocInParams() method, we can see the output below:

(newline)$java sample_Stored procedure_2(newline)Order Number    : 1001(newline)Order Date      : 2008‑05‑20(newline)Instructions    : express(newline)                (newline)Order Number    : 1003(newline)Order Date      : 2008‑05‑22(newline)Instructions    : express(newline)                (newline)Order Number    : 1011(newline)Order Date      : 2008‑06‑18(newline)Instructions    : express(newline)                (newline)Order Number    : 1013(newline)Order Date      : 2008‑06‑22(newline)Instructions    : express(newline)$

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 earlier:

(newline){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 below:

CREATE PROCEDURE GETTOTAL(ORDER_ID INT, OUT TOTALPRICE MONEY); LET TOTALPRICE=(SELECT SUM(TOTAL_PRICE) FROM ITEMS WHERE ORDER_NUM=ORDER_ID); END PROCEDURE;

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:

public static void executeStoredProcOUTParams(Connection con,int o_id) { try { CallableStatement cstmt = con.prepareCall("{call GetTotal(?, ?)}"); cstmt.setInt(1, o_id); cstmt.registerOutParameter(2, Types.INTEGER); cstmt.execute(); System.out.println("Total price for order" o_id "is $" cstmt.getInt(2)); } catch (Exception e) { e.printStackTrace(); } }

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:

public static void executeStoredProcOUTParams(Connection con,int o_id) { try { CallableStatement cstmt = con.prepareCall("{call GetTotal(?, ?)}"); cstmt.setInt("Order_ID", o_id); cstmt.registerOutParameter("TotalPrice", Types.INTEGER); cstmt.execute(); System.out.println("Total price for order" o_id "is $" cstmt.getInt("TotalPrice")); } catch (Exception e) { e.printStackTrace(); } }

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

(newline)$java sample_Stored procedure_3(newline)Total price for order 1002 is $1200(newline)$

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:

(newline)CREATE PROCEDURE GETTOTALBYMANU(CODE CHAR(3),  OUT TOTAL MONEY) (newline)RETURNING CHAR(3) AS MANU_CODE, CHAR(10) AS MANU_NAME;  (newline)DEFINE W_MANU_CODE CHAR(3);(newline)DEFINE W_MANU_NAME CHAR(10); (newline)LET TOTAL=(SELECT SUM(TOTAL_PRICE) FROM ITEMS WHERE MANU_CODE=CODE);   (newline)SELECT MANU_CODE,MANU_NAME(newline)  INTO W_MANU_CODE,W_MANU_NAME FROM MANUFACT WHERE MANU_CODE=CODE;(newline)RETURN W_MANU_CODE,W_MANU_NAME;              (newline)END PROCEDURE;

The method below uses the executeQuery() to invoke the GetTotalByManu stored procedure"

(newline)public static void executeStoredProcOUTParamsResulset(Connection con,String manu_id) {(newline)    try {(newline)        CallableStatement cstmt = con.prepareCall("{ call gettotalbymanu(?,?)}");(newline)        cstmt.setString(1, manu_id);         (newline)        cstmt.registerOutParameter(2, Types.CHAR);(newline)        ResultSet rs = cstmt.executeQuery();(newline)        rs.next();     (newline)        System.out.println("Total for manufacturer '"+rs.getString(2).trim()+(newline)        " ("+rs.getString(1)+") ' is $"+cstmt.getInt(2));(newline)    }(newline)    catch (Exception e) {(newline)        e.printStackTrace();(newline)    }(newline)}

The output of the program is shown below:

(newline)$java sample_Stored procedure_4(newline)Total for manufacturer 'Hero (HRO)' is $2882(newline)$

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:

public static void executeStoredGetOutParams(Connection con,String procname) { try { DatabaseMetaData dbmd = con.getMetaData(); ResultSet rs = dbmd.getProcedureColumns("stores7","",procname.toUpperCase(),null); while (rs.next()) if (rs.getShort("COLUMN_TYPE")==DatabaseMetaData.procedureColumnOut) { System.out.println("OUT Parame: " rs.getString("COLUMN_NAME")); System.out.println("Type: " rs.getString("DATA_TYPE") ); } } catch (Exception e) { e.printStackTrace(); } }

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 below:

CallableStatement cstmt = con.prepareCall("{ call gettotalbymanu(?,?)}"); if (((IfxCallableStatement) cstmt).hasOutParameter()) System.out.println("Stored procedure has OUT parameters "); // perform the logic

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 the following two procedures:

(newline)CREATE PROCEDURE GETORDERS(CUS_ID INT) RETURNING INT (newline)AS ORDER_NUM, DATE AS ORDER_DATE, LVARCHAR AS SHINSTRUC;(newline)    DEFINE W_ORDERN INT;(newline)    DEFINE W_ORDERD DATE;(newline)    DEFINE W_SHIP LVARCHAR;  (newline)    FOREACH(newline)        SELECT ORDER_NUM,ORDER_DATE,SHIP_INSTRUCT (newline)        INTO W_ORDERN,W_ORDERD,W_SHIP(newline)FROM ORDERS WHERE ORDERS.CUSTOMER_NUM=CUS_ID(newline)        RETURN W_ORDERN,W_ORDERD,W_SHIP WITH RESUME;(newline)    END FOREACH;(newline)END PROCEDURE;

CREATE PROCEDURE GETORDERS(ORD_DATE DATE) RETURNING INT AS ORDER_NUM, DATE AS ORDER_DATE, LVARCHAR AS SHINSTRUC; DEFINE W_ORDERN INT; DEFINE W_ORDERD DATE; DEFINE W_SHIP LVARCHAR; FOREACH SELECT ORDER_NUM,ORDER_DATE,SHIP_INSTRUCT INTO W_ORDERN,W_ORDERD,W_SHIP FROM ORDERS WHERE ORDERS.ORDER_DATE=ORD_DATE RETURN W_ORDERN,W_ORDERD,W_SHIP WITH RESUME; END FOREACH; END PROCEDURE;

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 below:

CREATE PROCEDURE GETORDERS(CUS_ID INT) RETURNING INT AS ORDER_NUM, DATE AS ORDER_DATE, LVARCHAR AS SHINSTRUC; CREATE PROCEDURE GETORDERS(ORD_DATE DATE) RETURNING INT AS ORDER_NUM, DATE AS ORDER_DATE, LVARCHAR AS SHINSTRUC;

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 below:

{call getorders(?::INT)} {call getorders(?::DATE)}

The following code shows how to execute the GETORDERS(DATE) procedure:

public static void executeSprocInParams_date(Connection con, String o_date) { try { PreparedStatement pstmt = con.prepareStatement("{call getorders(?::DATE)}"); pstmt.setString(1, o_date); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { System.out.println("Order Number\t: " rs.getString("order_num")); System.out.println("Order Date\t: " rs.getString("order_date")); System.out.println("Instructions\t: " rs.getString("shinstruc")); System.out.println(); } rs.close(); pstmt.close(); } catch (Exception e) { e.printStackTrace(); } }

Summary

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.

Stored procedures used in callable and prepared statements

Stored procedures used in callable and prepared statements