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.

Back to top

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.

Back to top

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.

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

Back to top

Using Statement object having no parameters

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

                 
{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 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.

                 
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 in Listing 3.

                                 
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(). Listing 4 shows the output of the following program.

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

Back to top

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.

                 
{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.

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

                 
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.

                 
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 in Listing 9.

                 
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 as shown in Listing 10.

                 
$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
$
 

Back to top

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.

                 
{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.

                 
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();
    }
}
            

Back to top

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. These two Java samples produce the following output printing the total price for a specified order:

                 
$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.

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

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

                
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();
        rs.next();		
        System.out.println("Total for manufacturer '"+rs.getString(2).trim()+
        " ("+rs.getString(1)+") ' is $"+cstmt.getInt(2));
    }
    catch (Exception e) {
        e.printStackTrace();
    }
}
                

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

                 
$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.

                 
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 in Listing 20.

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

Back to top

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.

                
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;
            

                
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 in Listing 23.

                
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 in Listing 24.

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

Listing 25 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();
    }
}
            

Back to top

Conclusion

This article has shown various ways to access simple and complex stored procedures from a JDBC application. The links to the server and JDBC documentation in the Resources section will provide assistance in exploring on the code examples to help you build efficient stored procedures and the equivalent 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.

Resources

Learn

• "Informix JDBC Driver Guide" is a step-by-step guide to learn JDBC driver APIs.
  
  
• Learn about Informix JDBC and IDS data types.
  
  
• In the Informix 11.70.Information center, get the resources you need to advance your Informix skills.
  
  
• Read the "IBM Informix Developer's Handbook" from IBM Redbooks to get up to speed quickly on IBM Informix product knowledge.
  
  
• Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  
• Follow developerWorks on Twitter.
  
  

Get products and technologies

• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  
• Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
  
  

Discuss

• Check out the developerWorks blogs and get involved in the developerWorks community.
  
  

About the authors

Babita Sonavane is a software developer at IBM India Lab, Bangalore. Babita has more than five years of IT experience. She works actively toward maintaining and developing various features in Informix JDBC and DB2 JDBC drivers.

Javier Sagrera is a software engineer on the Common Client Technologies (CCT) group. He joined the Informix team in 2000 and has over 10 years experience in application development for Informix database servers and Informix clients. Currently based in the IBM UK Bedfont Lab in London, he has extensive knowledge on all the Microsoft® technologies and is considered as a subject matter expert worldwide on all the Informix development tools.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Why use stored procedures?
• Calling stored procedures in JDBC applications
• Using Statement object having no parameters
• Using PreparedStatement having input parameters
• Using CallableStatement having output parameter
• Named parameters in a CallableStatement
• Calling stored procedures with multiple signatures
• Conclusion
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: Informix Ultimate Edition
• Buy: Informix Ultimate Edition

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers