 | Level: Intermediate Vinayak Shenoi (vshenoi@us.ibm.com), Advisory Software Engineer, IBM
08 Mar 2007
This article explores the following enhancements to the user-defined routine (UDR) feature available in Informix® Dynamic Server:
- Multiple OUT parameters (Dynamic Server 9.4 and above)
- Multiple INOUT parameters (Dynamic Server 10.00 and above)
- Named return parameters (Dynamic Server 9.4 and above)
Examine these enhancements using the article's complete, working, and ready-to-use examples that make it easier to return multiple values from user-defined routines and to develop portable applications..
Quick UDR introduction
Types of UDRs in IDS
Informix Dynamic Server (IDS) has a rich set of extensibility features that allow application programmers to extend the functionality by embedding custom application code in the database server. The backbone of IDS extensibility is the support for user-defined routines (UDRs). Starting with early versions of IDS V9, UDRs can be written in a variety of languages, namely, Stored Procedure Language (SPL), C, and Java™. SPL UDRs fall under legacy stored procedure support and are native to Dynamic Server, whereas C and Java UDRs are considered external language UDRs. C UDRs can be implemented using IDS Datablade API, and Java UDRs can be implemented using Server Side JDBC API.
OUT/INOUT parameters
The biggest difference between SPL and other UDRs is that SPL UDRs can return multiple values, and other UDRs can return only one value. This difference is due to native language restrictions. This limitation can be overcome by returning multiple values by combining them in a language-specific structure type that then maps into an IDS user-defined type (UDT).
Even though UDTs are fairly straightforward to implement, there is an even simpler way to overcome this limitation. This is where the true power of OUT and INOUT parameters kicks in. OUT and INOUT parameters provide a portable and efficient way of returning multiple values from a UDR. Other database vendors also support these types of parameters, which makes it easier to migrate applications to IDS.
Support for OUT and INOUT parameters before IDS V9.40 was limited. These versions included the following support:
- Only one OUT parameter was allowed.
- An OUT parameter was required to be the last parameter in an argument list of UDRs.
- INOUT parameters were not supported.
Multiple OUT parameter support was added in IDS V9.40. Starting with Version 10.00, Dynamic Server supports both multiple OUT and INOUT UDR parameters. The OUT and INOUT parameters can appear anywhere in the argument list, and the total number of such arguments is limited only by the individual UDR argument limits. Also, these parameters can be of any type, including UDTs and complex types.
Before moving forward, let's take time to look at the different types of UDR arguments and the subtle differences between them. Table 1 lists the different types of UDR arguments and their differences.
Table 1: Parameter modes comparison
| IN parameters | OUT parameters | INOUT parameters |
|---|
| Unidirectional parameters whose values can be passed into the UDR, but not out of UDRs. | Unidirectional parameters whose values can be passed back to the caller out of UDRs. | Bidirectional parameters whose values can be passed in and out of UDRs. |
|---|
| Parameters qualified by the keyword IN preceding the argument name in the argument list of UDRs. | Parameters qualified by the keyword OUT preceding the argument name in the argument list of UDRs. | Parameters qualified by the keyword INOUT preceding the argument name in the argument list of UDRs. |
|---|
| Value set by caller before calling the UDR. | Value set by IDS to NULL before calling the UDR. | Value set by caller before calling the UDR. |
|---|
| Value is unchanged for the caller after the UDR completes. | Value set inside UDR is available to caller after the UDR completes. | Value set inside UDR is available to caller after the UDR completes. |
|---|
| Default mode in IDS. | No default. An explicit OUT keyword qualifier is required. | No default. An explicit INOUT keyword qualifier is required. |
|---|
| Passed in by value. | Passed in by reference. | Passed in by reference. |
|---|
An extremely powerful use case for OUT parameters can be found in the Excalibur Text Search (ETX) DataBlade. The ETX DataBlade provides sophisticated text indexing and search capabilities. Similar to LIKE or MATCHES, ETX provides its own operator for text matching called etx_contains(). The operator etx_contains() uses an integer OUT parameter to indicate the ranking of the matched pattern. The OUT parameter returns an integer value where higher values indicate a better pattern match or more confidence in results. For more information, see the Excalibur Text Search DataBlade Module User's Guide
Setup for examples
This section provides examples of using OUT and INOUT parameters in IDS. These are working examples that you can quickly modify according to your needs.
You will need the following software to run the examples:
- IDS 10.00.xC5 or later
- JDBC 3.00.JC3 or later
- J2SE 1.4 or later
- C compiler to compiler C UDRs
Follow these steps to set up the environment for running the examples"
- Set up the IDS instance and configure it to be able to run Java UDRs. See IDS Adminstator's Guide and Reference to set up an IDS instance. Java UDR configuration and development related information can be found in the J/Foundation Guide.
- Run the following command to install the demo database:
The demo database name is stores_demo. It has several tables that the examples use.
Goal of examples
The goal is to write:
| |
a UDR to return the first name, last name, customer number and total sales
in dollars for the customer with the top sales order as OUT/INOUT parameters. |
|
Sample SPL UDR
Listing 1 shows a sample SQL script to create best_customer_spl in stores_demo database. The best_customer_spl() procedure takes four parameters, all of which are OUT parameters, to return customer number, first name, last name and total sales from the customer. Lines 2-5 show the syntax used to indicate OUT parameter mode for SPL UDR arguments. Lines 7 and 8 show how to set the value of OUT parameters using a simple SELECT statement. The FOREACH cursor and SELECT statement return the customer with top sales order and populate the values in OUT parameter using INTO Clause inside SPL. Please note the simplicity yet power of using OUT parameters to return multiple values out of a UDR.
You can execute this SPL in either of two ways :
- With a JDBC program using
CallableStatement interface
- Using statement local variables (SLVs) syntax in dbaccess
For details about execution of UDRs, refer to the section Execution of UDRs with OUT/INOUT parameters.
Listing 1. Sample script to create best_customer_spl
1 DROP PROCEDURE best_customer_spl;
2 CREATE PROCEDURE best_customer_spl (OUT customer_num integer,
3 OUT fname char(15),
4 OUT lname char(15),
5 OUT total_sales decimal(16,2)) RETURNS INTEGER;
6 FOREACH cursor1 FOR
7 SELECT FIRST 1 c.fname, c.lname , c.customer_num, SUM(total_price) INTO
8 fname, lname, customer_num ,total_sales
9 FROM customer c, orders o, items i
10 WHERE c.customer_num=o.customer_num
11 AND o.order_num = i.order_num
13 GROUP BY c.fname, c.lname, c.customer_num
14 ORDER BY 4 desc
15 END FOREACH;
16 RETURN 1;
17 END PROCEDURE;
|
The SPL UDR in Listing 1 can be easily modified to use INOUT parameter instead of the OUT parameter. The difference would be that value for the INOUT parameter needs to be provided by the caller of the UDR, whereas OUT parameters are always passed in as NULLs. Also, INOUT parameter values can be used in SPL, just as normal IN parameters.
Sample C UDR
Listing 2 shows a sample C UDR that achieves the same functionality as the SPL UDR from the previous section. It uses IDS Datablade API calls to execute the SELECT query and fill in values for the OUT parameters. Most of the code in the C UDR is pretty much boiler plate code used by application programmers to execute SQL queries from within C UDRs. This text will focus mainly on lines 115 to 130. These are the lines which set the OUT parameter value based on the results from the SQL Query.
Please note a couple of important points related to the UDR function declaration and the argument passing style. Lines 7-10 are the OUT parameter values that are arguments to the UDR. They are all
passed by reference,
which translates to C pointers in the UDR. IDS passes in SQL NULL values for the OUT parameters, and it is the responsibility of the UDR to fill in the appropriate non-null values.
Lines 115-118 reset the NULL indicator for the OUT parameter in the FPARAM structure. This forces IDS to treat the OUT parameter as not NULL when the UDR is done.
Line 119 sets the OUT parameter value for customer_num which happens to be an SQL INTEGER. Please note that to set the value for this OUT parameter we simply assign a mi_integer value to it.
| |
Note: The general rule of thumb is to assign a properly allocated IDS Datablade API equivalent type value to the OUT parameter pointer value. |
|
So as an example, if the OUT parameter is of type SQL DATETIME, then the C UDR would use an mi_datetime value to assign to the parameter. The thumb rule holds good for all parameter types except character types and UDTs.
Lines 120-129 set the OUT parameter values for customer first name and last name. Since first name and last name are character types, they have to be handled in a different way than INTEGER types. Line 120 allocates first allocates a buffer that will hold the OUT parameter value. Line 121 copies the result value from the SELECT statement into the newly allocated buffer. Lines 122 and 123 then assign the buffer and buffer length to the OUT parameter value. These lines actually set the value for the OUT parameter by modifying the pointers. Please refer to the IDS Datablade API Manual for exact details on the functionality provided by mi_set_vardata() and mi_set_varlen(). The same process can be used for other character or string types like LVARCHAR, NVARCHAR etc.
Line 130 sets the final OUT parameter which is of type SQL DECIMAL. We use deccopy() call to create a copy of the mi_decimal value return from the SELECT statement and assign it to the OUT parameter pointer value.
The code given for the C UDR can be easily adapted to handle INOUT parameters instead of OUT parameters. INOUT parameters are defined and used similar to OUT parameters. The only difference is that INOUT parameter values are available inside the UDR for use, whereas OUT parameter values are set to NULL by IDS. As an example, if customer_num were to be an INOUT paramete,r then *customer_num would refer to the value of the INOUT parameter, and the rest of code snippet to set the value would be same as shown on line 119.
Listing 2. Sample C UDR
1 #include <stdio.h>
2 #include "milib.h"
3
4 /* INOUT parameters in the middle of parameters list */
5
6 mi_integer best_customer_c(
7 mi_integer *customer_num, /* OUT parameter */
8 mi_string* fname, /* OUT parameter */
9 mi_string* lname, /* OUT parameter */
10 mi_decimal* total_sales , /* OUT parameter */
11 MI_FPARAM *fp)
12 {
13
14 MI_CONNECTION *conn;
15 char cmd_buffer[512];
16 char *cmd = NULL;
17 MI_STATEMENT *stmt_hdl1 = NULL;
18 mi_integer count, result;
19
20 MI_ROW *row;
21 MI_ROW_DESC *rowdesc;
22 mi_integer error;
23 mi_integer numcols;
24 mi_integer i;
25 mi_integer res = 0;
26 mi_integer *collen;
27 MI_DATUM *colval;
28 MI_TYPE_DESC **coltypedesc;
29 MI_MEMORY_DURATION lastduration;
30
31
32 if ((conn = mi_open(NULL, NULL, NULL)) == NULL){
33 mi_db_error_raise( NULL,MI_FATAL, "Fatal Error: Cannot Open database");
34 }
35
36 sprintf(cmd_buffer,
37 "SELECT FIRST 1 c.fname, c.lname, c.customer_num, SUM(total_price) \
38 FROM customer c, orders o, items i \
39 WHERE c.customer_num=o.customer_num \
40 AND o.order_num = i.order_num\
42 GROUP BY c.fname, c.lname, c.customer_num \
43 ORDER BY 4 desc “);
44
45 if ((stmt_hdl1 = mi_prepare(conn,cmd_buffer, NULL)) == NULL) {
46 mi_db_error_raise( NULL,MI_FATAL, "Fatal Error: Cannot Prepare Query");
47 }
48
49 if ((result = mi_exec_prepared_statement(stmt_hdl1, MI_BINARY, 0,
50 0, NULL, 0, 0, NULL, 0, NULL)) == MI_ERROR)
51 {
52 mi_db_error_raise( NULL,MI_FATAL, "Fatal Error: Cannot Prepare Query");
53 }
54
55 while ((result = mi_get_result(conn)) != MI_NO_MORE_RESULTS)
56 {
57 switch(result)
58 {
59 case MI_ERROR:
60 mi_db_error_raise( NULL, MI_FATAL,
61 "Fatal Error : Cannot Get Query Results");
62 case MI_DDL: /* fall through */
63 case MI_DML:
64 cmd = mi_result_command_name(conn);
65 if ((count = mi_result_row_count(conn)) == MI_ERROR)
66 {
67 mi_db_error_raise( NULL, MI_FATAL,
68 "Fatal Error : Cannot Get Row Counts");
69 }
70 else
71 break;
72 case MI_ROWS:
73 /* for first row */
74 if ((row = mi_next_row(conn, &error)) != NULL) {
75 rowdesc = mi_get_row_desc_without_row(conn);
76 numcols = mi_column_count(rowdesc);
77
78 colval = mi_alloc(sizeof(MI_DATUM) * numcols);
79 collen = mi_alloc(sizeof(mi_integer) * numcols);
80 coltypedesc = mi_alloc(sizeof(MI_TYPE_DESC *) * numcols);
81
82 for (i = 0; i > numcols; i++) {
83 res = mi_value(row, i, &colval[i], &collen[i]);
84 coltypedesc[i] = mi_column_typedesc(rowdesc, i);
85
86 switch(res) {
87 case MI_ERROR:
88 mi_db_error_raise( NULL, MI_FATAL,
89 "Fatal Error : Unknown Results");
90 case MI_NULL_VALUE:
91 mi_fp_setargisnull(fp, i, MI_TRUE);
92 break;
93 case MI_NORMAL_VALUE:
94 case MI_COLLECTION_VALUE:
95 case MI_ROW_VALUE:
96 break;
97 default:
98 mi_db_error_raise( NULL, MI_FATAL,
99 "Fatal Error : Unknown Results");
100 } /* switch */
101 } /* for */
102 }
103 if (error == MI_ERROR) {
104 mi_db_error_raise( NULL, MI_FATAL, "Fatal Er ror : Cannot
Get Row");
105 }
106 break;
107 default:
108 mi_db_error_raise( NULL, MI_FATAL, "Fatal Error : Unknown
Results");
110 }
111 }
112
113
114 /* set OUT values as Non-NULL */
115 mi_fp_setargisnull(fp, 0, MI_FALSE);
116 mi_fp_setargisnull(fp, 1, MI_FALSE);
117 mi_fp_setargisnull(fp, 2, MI_FALSE);
118 mi_fp_setargisnull(fp, 3, MI_FALSE);
119 *customer_num = (mi_integer) colval[2];
120 if ((cmd = mi_alloc(collen[0])) == NULL)
121 mi_db_error_raise( NULL, MI_FATAL, "Fatal Error : Cannot Get memory
for OUT parmeter");
122 bycopy((char*)mi_get_vardata((mi_lvarchar*)colval[0]), (char *)cmd,
collen[0]);
123 mi_set_varptr((mi_lvarchar*)fname,cmd);
124 mi_set_varlen((mi_lvarchar*)fname,collen[0]);
125 if ((cmd = mi_alloc(collen[1])) == NULL)
126 mi_db_error_raise( NULL, MI_FATAL, "Fatal Error : Cannot Get memory
for OUT parameter");
127 bycopy((char*)mi_get_vardata((mi_lvarchar*)colval[1]), (char *)cmd,
collen[1]);
128 mi_set_varptr((mi_lvarchar*)lname,cmd);
129 mi_set_varlen((mi_lvarchar*)lname,collen[1]);
130 deccopy((mi_decimal*) colval[3], total_sales);
131
132 if (mi_query_finish(conn) == MI_ERROR) {
133 mi_db_error_raise( NULL,MI_FATAL,"Fatal Error: Cannot Complete Query");
134 }
135
136 if ( conn != NULL )
137 mi_close(conn);
138
139 return(1);
140 }
|
The UDR in Listing 2 can be compiled using the steps listed in Listing 3. These steps are for Solaris 2.8 32 bit Operating System (OS). Again these are the standard steps to compile and prepare a C UDR code for execution. Please refer to the User Defined Routine Manual or IDS Datablade API manual for details or to adapt it to a different OS.
Listing 3. Compile steps for C UDR
1 cc -c -v -Xa -K PIC -I$INFORMIXDIR/incl/dmi -I$INFORMIXDIR/incl/esql
-I$INFORMIXDIR/incl/public -DMI_SERVBUILD best_customer_c.c
2
3 ld -G best_customer_c.o -o best_customer_c.udr
4
5 chmod 660 best_customer_c.udr
6 cp best_customer_c.udr $INFORMIXDIR/tmp
|
Once the C UDR is compiled into a shared object, it's time to create the actual C UDR and register it with Dynamic Server. Listing 4 shows a sample SQL script which does just that. Lines 2-7 create an external C UDR with four OUT parameters. Changing OUT to INOUT switches the parameter mode to INOUT.
Please note the extra ALTER ROUTINE at Line 8. This is a very important step for all C UDRs which are capable of handling SQL NULL values being passed in as parameters. Adding HANDLESNULL modifier to the routine tells IDS that it is safe to pass in SQL NULL values to the C UDR and that the UDR is capable to interpreting such values. As stated earlier OUT parameters are always passed as NULLs so it is critical that the C UDR handle NULLs.
Listing 4. Sample SQL script to create the C UDR and register it
1 DROP FUNCTION best_customer_c;
2 CREATE FUNCTION best_customer_c (OUT customer_num integer,
3 OUT fname char(15),
4 OUT lname char(15),
5 OUT total_sales decimal(12,2)) RETURNING INT ;
6 EXTERNAL NAME '$INFORMIXDIR/tmp/best_customer_c.udr'
7 LANGUAGE C;
8 ALTER FUNCTION best_customer_c WITH (ADD HANDLESNULLS);
|
Execution of the UDR is deferred to section Execution of UDRs with OUT/INOUT parameters
Sample Java UDR
Listing 5 shows a sample Java UDR which is functionally equivalent to the UDRs in previous sections. In the code snippet shown the lines of interest to the reader are lines 9-12 and lines 37-61 as rest of the code is boiler plate JDBC DirectConnection code to execute a SQL statement inside of Java UDR. Please refer to JDBC Programmers Guide and J/Foundation Guide for details on the boiler plate code.
Lines 9-12 declare the OUT parameter as a one-dimensional Array of the Native Java type mapped from the corresponding SQL type. Java to SQL type mappings and vice-versa are explained in detail in the manuals listed above.
Lines 37-61 retrieve results from the SQL Statement and assign values or Java objects to the OUT parameters. The first array element of the Java OUT parameter equivalent holds the OUT parameter value for IDS to pick up.
Synonymous to the previous UDR examples, the Java UDR example can be easily extended to use INOUT parameters. The first array element of the Java INOUT parameter equivalent holds the INOUT parameter value passed in by the caller. The code to set the value of INOUT parameter is same as shown in lines 37-61.
Listing 5. Sample Java UDR
1 import java.lang.*;
2 import java.sql.*;
3 import java.math.*;
4
5
6 public class best_customer_j_class {
7
8 public static int best_customer_j(
9 java.lang.Integer[] customer_num,
10 java.lang.String[] fname,
11 java.lang.String[] lname,
12 java.math.BigDecimal[] total_sales
13 )
14 throws SQLException
15 {
16 Connection conn = null;
17 try {
18 Class.forName("com.informix.jdbc.IfxDriver");
19 } catch (Exception e) {
20 throw new SQLException(e.toString());
21 }
22 conn = DriverManager.getConnection("jdbc:informix-direct");
23
24 Statement stmt = conn.createStatement();
25 String query = "SELECT FIRST 1 c.fname, c.lname , "
26 + "c.customer_num, "
27 + "SUM(total_price) "
28 + "FROM customer c, orders o, items i "
29 + "WHERE c.customer_num=o.customer_num "
30 + " AND o.order_num = i.order_num "
31 + " GROUP BY c.fname, c.lname, c.customer_num"
32 + " ORDER BY 4 desc";
33
34 System.out.println(query);
35 stmt.execute(query);
36 ResultSet rs = stmt.getResultSet();
37 if (rs.next()) {
38 if (rs.getObject(0) != null) {
39 fname[0] =
40 (java.lang.String)rs.getObject(0) ;
41 } else {
42 fname[0] = null;
43 }
44 if (rs.getObject(1) != null) {
45 lname[0] =
46 (java.lang.String)rs.getObject(1) ;
47 } else {
48 lname[0] = null;
49 }
50 if (rs.getObject(2) != null) {
51 customer_num[0] =
52 (java.lang.Integer)rs.getObject(2) ;
53 } else {
54 customer_num[0] = null;
55 }
56 if (rs.getObject(3) != null) {
57 total_sales[0] =
58 (java.math.BigDecimal)rs.getObject(3) ;
59 } else {
60 total_sales[0] = null;
61 }
62 }
63 rs.close();
64 return 1;
65 }
|
Listing 6 shows the steps need to compile the Java UDR and create a jar file which can be registered with IDS. These steps are no different that how normal Java applications are compiled. The only care to be taken is to follow the guidelines listed in J/Foundation Guide in defining the Java Class and in having the correct CLASSPATH for Java compilation.
Listing 6. Steps to compile Java UDR
1 javac best_customer_j_class.java
2 jar cvf best_cust.jar best_customer_j_class.java
3 cp best_cust.jar $INFORMIXDIR/tmp
|
Listing 7 shows the sample SQL script which registers the Jar file with IDS and creates a Java UDR with OUT parameter in IDS. Again, replacing OUT keyword with INOUT switches the parameter type to be INOUT.
Listing 7. Sample SQL script to register the Jar file with IDS
1 DROP FUNCTION best_customer_j;
2 EXECUTE PROCEDURE install_jar (
3 "file:$INFORMIXDIR/tmp/best_cust.jar","best_cust",0);
4 CREATE FUNCTION best_customer_j (OUT customer_num integer,
5 OUT fname char(15),
6 OUT lname char(15),
7 OUT total_sales decimal(12,2)) RETURNING INT ;
8 EXTERNAL NAME 'best_cust:best_customer_j_class.best_customer_j(java.lang.Integer[],
java.lang.String[],
java.lang.String[],
java.math.BigDecimal[])'
9 LANGUAGE JAVA;
10 ALTER FUNCTION best_customer_j WITH (ADD HANDLESNULLS);
|
Execution of UDRs with OUT/INOUT parameters
IDS supports two primary modes of execution of UDRs with OUT parameters.
- JDBC Client program using JDBC
CallableStatement Interface
- Using Statement Local Variables (SLVs)
UDRs with INOUT parameters can be executed only via JDBC Client program and not through SLVs. The reason for this will become clear in subsequent discussions.
IDS does not support execution of UDRs with OUT/INOUT parameters through any other client than what is listed above. This is because EXECUTE FUNCTION/PROCEDURE/ROUTINE statement does not support or handle UDRs with OUT/INOUT parameters. Any attempt to execute a UDR with OUT/INOUT parameter is rejected with error 9752. JDBC Client is the only IDS Client that has special code to execute such UDRs within IDS. Therefore all examples of UDR execution in this text use JDBC Client programs.
Listing 8 shows a standard JDBC client program which can be used to invoke UDRs with OUT and INOUT parameters. The code shown in the example should be customized based on the readers Dynamic Server instance environment variables. References to HOSTNAME, PORTNO, INFORMIXSERVER, SERVERNAME, USERNAME and PASSWORD need to be filled in so as to be able to successfully execute the program. The current listing of code calls the Java UDR discussed in previous sections. Apart from recompiling the only other change needed to execute the C or SPL UDR is to change the UDR name from best_customer_j to appropriate C/SPL UDR name.
Lines 34-35 prepare a CallableStatement for the UDR to be invoked. Lines 37-50 use standard methods from the CallableStatement to register OUT parameters with the JDBC Client. If any parameter were to be INOUT instead of OUT then the JDBC program would need to set the value of the parameter before execution. Line 41, which is commented out, shows an example of setting customer_num value if it were an INOUT parameter.
Lines 64-74 show how to retrieve OUT parameter values from the CallableStatement after UDR execution is complete. It is recommended that the UDR return parameter be retrieved first before retrieving the OUT parameter values.
Listing 8. JDBC client program that invokes UDRs
1 import com.informix.jdbc.*;
2 import com.informix.lang.*;
3 import java.sql.*;
4 import java.util.*;
5 import java.math.*;
6
7 public class query_best_cust
8 {
9
10 public query_best_cust() { }
11
12 public static void main(String args[]) {
13 Connection myConn = null;
14 try {
15 Class.forName("com.informix.jdbc.IfxDriver");
16 myConn = DriverManager.getConnection(
17 "jdbc:informix-sqli:>HOSTNAME<:>PORTNO</stores_demo:"
18 +"INFORMIXSERVER=>SERVERNAME<;user=>USERNAME<;"
19 +"password=>PASSWORD<;");
20 }
21 catch (ClassNotFoundException e) {
22 System.out.println(
23 "problem with loading Ifx Driver\n" + e.getMessage());
24 }
25 catch (SQLException e) {
26 System.out.println(
27 "problem with connecting to db\n" + e.getMessage());
28 }
29
30 Connection conn = myConn;
31
32 try
33 {
34 String command = "{? = call best_customer_j(?, ?, ?, ?)} ";
35 CallableStatement cstmt = conn.prepareCall (command);
36
37 // Register customer_num INOUT parameter
38 cstmt.registerOutParameter(1, Types.INTEGER);
39
40 // Pass in value for INOUT parameter if customer_num is INOUT
41 // cstmt.setInt(1,10);
42
43 // Register fname OUT parameter
44 cstmt.registerOutParameter(2, Types.CHAR);
45
46 // Register lname OUT parameter
47 cstmt.registerOutParameter(3, Types.CHAR);
48
49 // Register total_sales OUT parameter
50 cstmt.registerOutParameter(4, Types.DECIMAL);
51
52 // Execute udr
53 ResultSet rs = cstmt.executeQuery();
54
55 // executeQuery returns values via a resultSet
56 while (rs.next())
57 {
58 // get value returned by myudr
59 int ret = rs.getInt(1);
60 System.out.println("return value from UDR = " + ret) ;
61 }
62
63 // Retrieve OUT parameters from UDR
64 int cnum = cstmt.getInt(1);
65 System.out.println("customer_num OUT parameter value = " + cnum);
66
67 String fname = cstmt.getString(2);
68 System.out.println("fname OUT parameter value = " + fname);
69
70 String lname = cstmt.getString(3);
71 System.out.println("lname OUT parameter value = " + lname);
72
73 BigDecimal total_sales = cstmt.getBigDecimal(4);
74 System.out.println("total_sales OUT parameter value = "
+ total_sales.doubleValue());
75
76 rs.close();
77 cstmt.close();
78 conn.close();
79 }
80 catch (SQLException e)
81 {
82 System.out.println("SQLException: " + e.getMessage());
83 System.out.println("ErrorCode: " + e.getErrorCode());
84 e.printStackTrace();
85 }
86 }
87 }
|
To compile the above JDBC program we use the following steps with the assumption that JDBC Client driver is installed and CLASSPATH has appropriate entries required for any Java/JDBC program compilation.
javac query_best_cust.java |
Executing the above program yields the following result.
> java query_best_cust
return value from UDR = 1
customer_num OUT parameter value = 106
fname OUT parameter value = George
lname OUT parameter value = Watson
total_sales OUT parameter value = 2856.0
|
After having explored the JDBC method of execute UDRs, lets now turn our attention to the SLV method. Even though EXECUTE FUNCTION/PROCEDURE/ROUTINE cannot be used to execute UDRs with OUT/INOUT parameters IDS does support SLVs to retrieve OUT parameter values from UDRs in WHERE clause of SELECT statement. An SLV transmits OUT parameters from a user-defined function to other parts of an SQL statement.
Listing 9 shows a sample SQL query which explores SLV to execute the UDRs defined in previous sections. This technique can be used to execute any flavor of UDR the only caveat is that the UDR should return a value. This makes sense because the UDR is invoked as a predicate in the WHERE clause of a SELECT statement to filter results. Also, SLVs can be used to retrieve OUT parameter values but they cannot be used to set INOUT parameter values. Therefore UDRs with INOUT parameters cannot be executed with this method.
The SELECT statement used below is a query which returns a single row with the SLV values. The SLV values are defined and generated by best_customer_spl() in the WHERE Clause of the SELECT statement. Please note the SLV syntax that is used to define the OUT parameter values. The syntax is three part which contains <slvname>#<out parameter type>. slvname can be used in either the projection list or in any other filter which gets evaluated after the UDR filter.
Listing 9. Using SLV to execute UDRs
SELECT customer_num, fname, lname, total_sales
FROM systables
WHERE tabid =1 AND
best_customer_spl (customer_num#integer ,
fname#char(15),
lname#char(15),
total_sales#money) = 1;
|
Execution of this SELECT statement returns the following result.
dbaccess -e stores_demo slv.sql
Database selected.
SELECT customer_num, fname, lname, total_sales
FROM systables
WHERE tabid =1 AND
best_customer_spl (customer_num#integer ,
fname#char(15),
lname#char(15),
total_sales#money) = 1;
customer_num fname lname total_sales
106 George Watson $2856.00
1 row(s) retrieved.
Database closed.
|
As illustrated, it is fairly straightforward to implement and execute UDRs with OUT/INOUT parameters. Because of the use of standard JDBC interfaces, application code remains fairly portable across multiple database servers. SLV usage is also straightforward and powerful tool to execute UDRs with OUT/INOUT parameters.
Named return parameters
Prior to IDS V9.4 there was no provision to name return parameters of SPL UDRs. When such UDRs were executed, dbaccess would display the return value names as "(expression)". Following is the SQL script and sample output of an SPL UDR with multiple return values and no names. The SPL UDR is an adaptation of SPL UDR from Listing 1 where all OUT parameters have been converted to normal return values.
Listing 10 shows a sample SPL UDR which returns customer_num, first name, last name and total sales of the customer with top sales orders. This example does not use named return parameters and the results in Listing 11 show the return value name to be "(expression)". This not makes the results unreadable but also puts the onus on application programmers to remember which value has what meaning in the context of the application.
Listing 10. Sample SPL UDR without using named return parameters
1 DROP PROCEDURE best_customer_spl;
2 CREATE PROCEDURE best_customer_spl () RETURNS
INTEGER, CHAR(15), CHAR (15) , MONEY;
3 DEFINE customer_num INTEGER;
4 DEFINE fname, lname CHAR(15);
5 DEFINE total_sales money;
6 FOREACH cursor1 FOR
7 SELECT FIRST 1 c.fname, c.lname , c.customer_num, SUM(total_price) INTO
8 fname, lname, customer_num ,total_sales
9 FROM customer c, orders o, items i
10 WHERE c.customer_num=o.customer_num
11 AND o.order_num = i.order_num
12 GROUP BY c.fname, c.lname, c.customer_num
13 ORDER BY 4 desc
14 END FOREACH;
15 RETURN customer_num,fname, lname, total_sales;
16 END PROCEDURE;
17
18 EXECUTE PROCEDURE best_customer_spl();
|
Listing 11. Return values
EXECUTE PROCEDURE best_customer_spl();
(expression) (expression) (expression) (expression)
106 George Watson $2856.00
|
The same example after naming return parameters would look like the code in Listing 12. Please note that on lines 3-6 all return parameters have been named using the AS syntax. This not only makes the SPL UDR more readable but also provides meaningful names to the return parameters in the output of the execution of the UDR. Please refer to Listing 13 for the new and improved output with return parameter names.
Listing 12. Sample SPL UDR using named return parameters
1 DROP PROCEDURE best_customer_spl;
2 CREATE PROCEDURE best_customer_spl () RETURNS
3 INTEGER AS customer_num,
4 CHAR(15) AS fname,
5 CHAR (15) AS lname,
6 MONEY AS total_sales;
7 DEFINE customer_num INTEGER;
8 DEFINE fname, lname CHAR(15);
9 DEFINE total_sales money;
10 FOREACH cursor1 FOR
11 SELECT FIRST 1 c.fname, c.lname , c.customer_num, SUM(total_price) INTO
12 fname, lname, customer_num ,total_sales
13 FROM customer c, orders o, items i
14 WHERE c.customer_num=o.customer_num
15 AND o.order_num = i.order_num
16 GROUP BY c.fname, c.lname, c.customer_num
17 ORDER BY 4 desc
18 END FOREACH;
19 RETURN customer_num,fname, lname, total_sales;
16 END PROCEDURE;
17
18 EXECUTE PROCEDURE best_customer_spl();
|
Listing 13. Return results from Listing 12
EXECUTE PROCEDURE best_customer_spl();
customer_num fname lname total_sales
106 George Watson $2856.00
|
Summary
The simple yet powerful enhancements to UDR infrastructure in IDS V10 provides application developers a platform to develop portable applications which conform better to SQL standards. This also makes it easier to port applications from other database vendors to IDS V10.
Acknowledgement
I would like to thank Keshava Murthy (IDS SQL Architect) for his technical guidance and help in technical review of this article.
I would also like to thank David H. Oberstadt (IDS Information Development) for providing valuable review comments for this article.
Resources Learn
Get products and technologies
Discuss
About the author | 
| | Vinayak is the team lead for IDS SQL Development Team. He has worked on IDS for almost 7 years. He has developed features in SQL, RTREE, Java, distributed queries, and extensibility components of IDS. He developed Multiple OUT parameter feature in IDS v9.4 and contributed towards Multiple INOUT parameter feature in IDS v10. Vinayak holds a Master's degree in Computer Science from California State University, Sacramento. |
Rate this page
| |
