Stored Procedures

DB Access Module for PostgreSQL User's Guide
Rogue Wave web site: Home Page | Main Documentation Page

2.9 Stored Procedures

Stored procedures are encapsulated by the class RWDBStoredProc. The implementation of stored procedures in the DB Access Module for PostgreSQL provides full support for PostgreSQL user-defined functions written in PL/pgSQL or SQL languages. This includes support for creating and dropping functions, retrieving function text and parameters, executing functions, and processing result sets.

Refcursors in PL/pgSQL language functions are supported.

Composite return types in SQL language functions are not supported.

2.9.1 Creating a Stored Procedure

Stored procedures can be created by calling the createProcedure() method of RWDBDatabase. The DB Interface Module will create the parameter list from a schema you supply, while the body of the stored procedure is supplied by the application. Here is a simple example of creating a PostgreSQL stored procedure using the DB Interface Module. This example takes a string and an integer and inserts a new row into the table, if the string is not already in the table.

Example 4: Creating a stored procedure

RWCString body = 
    "RETURNS BOOL AS '                                                  \n"
    "    DECLARE                                                        \n"
    "        empname ALIAS FOR $1;                                      \n"
    "        payscale ALIAS FOR $2;                                     \n"
    "        foundname varchar;                                         \n"
    "BEGIN                                                              \n"
    "    select into foundname name from employee where name = empname; \n"
    "    IF NOT FOUND THEN                                              \n"
    "        insert into employee values(empname, payscale);            \n"
    "        RETURN true;                                               \n"
    "    END IF;                                                        \n"
    "    RETURN false;                                                  \n"
    "END;                                                               \n"
    "' LANGUAGE 'plpgsql'";

  RWDBSchema params;
  RWDBColumn col1, col2;
  col1.type(RWDBValue::String);
  col1.storageLength(50);
  col1.paramType(RWDBColumn::inParameter);
  params.appendColumn(col1);
  col2.type(RWDBValue::Int);
  col2.paramType(RWDBColumn::inParameter);
  params.appendColumn(col2);
  db.createProcedure("add_employee", body, params);

2.9.2 Executing a Stored Procedure

The DB Access Module for PostgreSQL uses the "select * from procName(<args>)" SQL syntax to execute stored procedures. Result sets composed of complex types written in the SQL stored procedure language are not supported. However, the refcursor type in the PL/pgSQL language is supported. Execution is accomplished by using the above syntax along with the SQL statement FETCH to retrieve the results. The return value of the stored procedure is fetched as part of the result set. Here is an example executing the procedure created in Example 4.

Example 5: Executing a stored procedure

RWDBStoredProc add_employee = aDb.storedProc("add_employee");
RWCString newname("Sarah");
int payscale = 5;
add_employee << newname << payscale;
RWDBTable results = add_employee.execute().table();
RWDBReader rdr = results.reader();
while (rdr()) {
    // save or display results 
}

2.9.3 Instantiating an RWDBStoredProc Using Schema Data

The member functions:

RWDBDatabase::storedProc(RWCString, RWDBSchema, RWDBColumn) 
RWDBDatabase::storedProc(RWCString, RWDBConnection, RWDBSchema,
                      RWDBColumn)

increase the performance of stored procedures by eliminating the usual parameter fetch required for instantiations. The schema must include the name and type of each parameter in the stored procedure. The RWDBColumn parameter is ignored as the return value of the stored procedure is fetched as part of the result set.

The Rogue Wave name and logo, and SourcePro, are registered trademarks of Rogue Wave Software. All other trademarks are the property of their respective owners.
Provide feedback to Rogue Wave about its documentation.