2.8 Stored Procedures

Stored procedures are encapsulated by the class RWDBStoredProc. The implementation of stored procedures by the DBTools.h++ access library for Informix is subject to many restrictions. These restrictions are due to a combination of factors dictated by the Informix SQL grammar and the capabilities of the Informix ODBC Driver:

The DBTools.h++ access library for Informix implements only a subset of the RWDBStoredProc functionality declared in the DBTools.h++ core. In particular, Informix stored procedures does not support output or input/output parameters; instead, parameters are returned as a set of results. See Section 2.8.2 below.
Return values from stored procedures are not supported. The function RWDBStoredProc::returnValue() returns a NULL RWDBValue.
RWDBStoredProc::operator[]( const RWCString& paramName ) is not available, since this information is not stored in the Informix system tables.

2.8.1 Creating a Stored Procedure

Use the createProcedure method of RWDBDatabase to create a stored procedure. DBTools.h++ creates the parameter list from a schema you supply. You must supply the text of the stored procedure. Here is a simple example of creating an Informix stored procedure using DBTools.h++:

RWCString body =
" RETURNING INT, REAL, VARCHAR(20);\n"      
" DEFINE xint INTEGER;\n"                   
" DEFINE xflt REAL;\n"                      
" DEFINE xstr VARCHAR(20);\n"               
" FOREACH\n"                                
" select intcol, floatcol, strcol "         
" into xint, xflt, xstr "                   
" from " + simpleTableName + "\n"           
" RETURN xint, xflt, xstr WITH RESUME;\n"   
" END FOREACH;\n"                           
" END PROCEDURE";
RWDBSchema params;
RWDBColumn aParameter;
aParameter.name("id").type(RWDBValue::Int);
params.appendColumn(aParameter);
aParameter.name("value").type(RWDBValue::Float);
params.appendColumn(aParameter);
aParameter.name("searchString").type(RWDBValue::String)
.storageLength(20);
params.appendColumn(aParameter);
aDB.createProcedure("StoredProcDemo", body, params);

Note that Informix does not support In/Out parameters, so you do not need to specify a parameter type in the schema you pass to createProcedure().

2.8.2 Executing Stored Procedures

An Informix stored procedure returns zero or more rows of values. If you execute a stored procedure that does not return any values, it is executed immediately when your application calls RWDBStoredProc::execute().

Here is an example of executing a stored procedure that does not return values:

RWDBStoredProc example1 = db.storedProc("StoredProcDemo");
if ( example1.execute().isValid() )
cout << "success" << endl;
else
cout << "failure" << endl;

If you execute a stored procedure that does return rows, you must first fetch a row. This can be done by obtaining a reader from the execute method. The first time a row is fetched, the procedure is actually executed in the database.

RWDBStoredProc example2 = db.storedProc("StoredProcDemo");
RWDBReader rdr = example2.execute().table().reader();
if ( rdr.isValid() && rdr() ) {
rdr >> xint >> xfloat >> xstring;
cout << xint.asString() 
<< xfloat.asString() 
<< xstring.asString() 
<< endl;
  }
else
cout << "failure" << endl;

If you want to execute a stored procedure that returns values, and you are not interested in using the values returned, you can write something like this:

RWDBStoredProc example3 = db.storedProc("StoredProcDemo");
RWDBReader rdr = example3.execute().table().reader();
if ( rdr.isValid() && rdr() )
cout << "success" << endl;
else
cout << "failure" << endl;

2.8.3 Instantiating an RWDBStoredProcedure Using Schema Data

The member function:

RWDBDatabase.storedProc(RWCString, RWDBConnection, RWDBSchema)

increases the performance of stored procedures by eliminating the usual parameter fetch required for instantiation. The schema must include the RWDBColumn::ParamType of each parameter in the stored procedure. For example, the code in Section 2.8.1 uses the schema params to create the stored procedure; params could be used to instantiate an RWDBStoredProc.