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