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 238. 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);