The stored procedure used in the prepared callable statement

Explore the execution of stored procedures that use callable statements for the Informix JDBC driver.

Free**: IBM Informix 117 Trial versions (including Ultimate Edition, Developer Edition, and Innovator-C Edition)**More IBM software trials, and join the IBM Software & Technology group to participate**. In a relational database application, the main advantage of using a stored procedure over a single SQL statement is that the query plan (or execution plan) is generated when the stored procedure is created, and the same query plan needs to be reused for each execution of the stored procedure, which saves a lot of resources in the database server. Once stored procedures are created, they can be invoked by any database client, such as a JDBC application, without the need for a new execution plan.

The way you use stored procedures varies from database server to database. Database management systems (DBMS), such as Informix and DB2, use different SQL syntax to execute stored procedures. This makes things more difficult when an application developer needs to write a ** that targets multiple DBMS. Invokable statements provide a way to execute stored procedures using the same SQL statements across all DBMS systems.

Let's say we have a JDBC application that needs to efficiently repeat a certain sequence of tasks over and over again. We may want to use the j**a method, but how many times do we want to have client-server communication to send and receive data?The database server prepares and generates a query plan for each SQL statement sent by the application, which will take up some CPU time. Despite performance considerations, it may not be good to use a simple j**a approach to a SQL statement.

What about using a stored procedure, which is just a one-time task?Create a SQL call, and you can use itcallablestatementThe object calls it from within the JDBC application, and the CallableStatement object acts as a caller to the stored procedure on the server. Most of the business logic will reside on stored procedures. This helps simplify the client** and speeds up execution because the SQL statements contained in the stored procedure are prepared and optimized when the stored procedure is created.

The Informix JDBC driver provides a tool that can be used to execute stored proceduresstatementpreparedstatementwithcallablestatementMethod. Which method to use depends on the nature of the stored procedure. For example, if a stored procedure returns a value, then jdbc should be usedstatementObject. The following table provides some guidance on which method is used for which stored procedure type.

Displays a table of which JDBC method to use based on each stored procedure type.

The stored procedure type jdbc method is not required for the stored procedureinoroutparameter usagestatementObject stored procedures haveinparameter usagepreparedstatementObject stored procedures haveinwithoutparameter usagecallablestatementObject.

We'll provide an example of executing a stored procedure using the Informix JDBC method in the following cases:

Use the one without parametersstatementUse the one with input parameterspreparedstatementUse the one with output parameterscallablestatementcallablestatementEach of the topics mentioned above in a stored procedure that has been overloaded with named parameters will contain the following details:

The schema of the stored procedure on the database that uses the syntax used to invoke stored procedures during the Informix JDBC driver is shown in Listing 1 of the JDBC driver sample program that has outputinoroutThe syntax used by the stored procedure for the parameter.

Execution does notinoroutThe syntax used by the stored procedure for the parameter.

Use the one without parametersstatementThe definition of the object's stored procedure.

create procedure getcustname() returning lvarchar as name;define w_name lvarchar;foreachselect first 5 fname ||' ' ||lname into w_namefrom customerreturn w_name with resume;end foreach;end procedure;

The jdbc j**a used to execute the stored procedure is shown in Listing 3.

Use the one without parametersstatementThe jdbc of the object

public static void executestoredprocnoparams(connection con) "); while (rs.next())rs.close();stmt.close();catch (exception e) }

Program output from Listing 3.

$j**a sample_stored procedure_1ludwig paulicarole sadlerphilip currieanthony higginsraymond vectorgeorge watson$

The syntax used to execute a stored procedure that uses a prepared statement with input parameters.

The question mark character () corresponds to each input parameter required by the stored procedure. It acts as a placeholder for the value passed to the stored procedure.

To specify a value for a parameter, you can use itifxpreparedstatementOne of the setter methods of the class. (For example.)pstmt.setint()）。The setter methods you can use depend oninThe data type of the parameter. In addition to the value of the parameter, the setter method will also get the location of the parameter in the SQL statement.

Shows an example of a stored procedure with a signature.

updmanu(int mn_code, char(10) mn_name, date mn_upd);

pstmt = con.preparestatement("");pstmt.setint(1, manu_id);pstmt.setstring(2, manu_code);pstmt.setdate(3, manu_date);

Demonstrate how to use itinAn example of a stored procedure for a parameter.

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;

Execute ja of the stored procedure with a prepared statement with input parameters

public static void executesprocinparams(connection con, int c_id) "); pstmt.setint(1, c_id); resultset rs = pstmt.executequery();while (rs.next())rs.close();pstmt.close();catch (exception e) }

Output of the program shown in Listing 9.

$j**a sample_stored procedure_2order number : 1001order date : 2008-05-20instructions : expressorder number : 1003order date : 2008-05-22instructions : expressorder number : 1011order date : 2008-06-18instructions : expressorder number : 1013order date : 2008-06-22instructions : express$

Next, we'll demonstrate how to call return one or moreoutStored procedures for parameters. The stored procedure uses these parameters to return the data to the calling application in the form of a single value, rather than the result set we saw earlier. Used for:in/outThe SQL syntax for stored procedures is similar to the one we showed earlier in Listing 5. The syntax used to execute stored procedures that use callable statements without output parameters.

You areregisteroutparametermethodoutThe value specified by the parameter must be j**asql.Types contains one of the Informix JDBC data types, which is internally converted to one of the native IDS data types.

For this example, we'll use the following stored procedure, which uses the items table in the stores demo database, as shown in Listing 12.

A stored procedure that demonstrates how to use the out parameter.

create procedure gettotal(order_id int, out totalprice money);let totalprice=(select sum(total_price) from items where order_num=order_id);end procedure;

Executes ja for a stored procedure that uses a callable statement with output parameters

public static void executestoredprocoutparams(connection con,int o_id) "); 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) }

The following example shows how to use named parameters in a j**a application. Note that the parameter name corresponds to the parameter name in the stored procedure definition.

How to use named parameters in a j**a application.

public static void executestoredprocoutparams(connection con,int o_id) "); 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) }

Output of the two j**a examples from Listing 13 and Listing 14.

$j**a sample_stored procedure_3total price for order 1002 is $1200$

Used to demonstrate when to use itexecutequery()Stored procedure for the method.

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_nameinto w_manu_code,w_manu_name from manufact where manu_code=code;return w_manu_code,w_manu_name;end procedure;

For presentationsexecutequery()j**a** for method usage

public static void executestoredprocoutparamsresulset(connection con,string manu_id) "); 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) }

Program output from the j**a example in Listing 17.

$j**a sample_stored procedure_4total for manufacturer 'hero (hro)' is $2882$

The following example is usedgetprocedurecolumns()method to getgettotalbymanuThe name and type of the process.

j**a **

public static void executestoredgetoutparams(connection con,string procname) catch (exception e) }

Example j**a**

callablestatement cstmt = con.preparecall("");if ((ifxcallablestatement) cstmt).hasoutparameter())system.out.println("stored procedure has out parameters ");// perform the logic

create procedure getorders(cus_id int) returning intas 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_shipfrom 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 intas 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_shipfrom orders where orders.order_date=ord_date return w_ordern,w_orderd,w_ship with resume; end foreach;end procedure;

Example of multiple signatures.

create procedure getorders(cus_id int) returning intas order_num, date as order_date, lvarchar as shinstruc;create procedure getorders(ord_date date) returning intas order_num, date as order_date, lvarchar as shinstruc;

An example of a placeholder.

Demonstrates how to use ja for stored procedures with multiple signatures

public static void executesprocinparams_date(connection con, string o_date) "); pstmt.setstring(1, o_date); resultset rs = pstmt.executequery();while (rs.next())rs.close();pstmt.close();catch (exception e) }