Stored Procedures
The DB Access Module for MySQL includes support for MySQL stored procedures, including support for creating and dropping stored procedures, retrieving stored procedure text, and parameter definitions.
Creating Stored Procedures
Create stored procedures 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. You can create the procedure by using the
RWDBDatabase::createProcedure() call. In this case, the parameter list and body of the stored procedure must be supplied. The body should contain the following:
BEGIN
<declarations>
<statements>
END;
This simple example looks up a royalty percentage and returns the royalty schedule based on title and sales level:
RWCString body =
"begin"
" declare total int \n"
" select titles.total_sales into total \n"
" from titles where title_id = title_id; \n"
" if ( total > 0 ) \n"
" set total = total + newsales; \n"
" select royalty into pc from roysched \n"
" where total >= roysched.lowrange and \n"
" total < roysched.highrange \n"
" and roysched.title_id = title_id; \n"
" select royalty,lowrange,highrange from roysched \n"
" where roysched.title_id = @title_id; \n"
"end; "
RWDBSchema params;
RWDBColumn aParameter;
aParameter.name("title_id").type(RWDBValue::Int);
aParameter.paramType(RWDBColumn::inParameter);
params.appendColumn(aParameter);
aParameter.name("newsales").type(RWDBValue::Int);
aParameter.paramType(RWDBColumn::inParameter);
params.appendColumn(aParameter);
aParameter.name("pc").type(RWDBValue::Float);
aParameter.paramType(RWDBColumn::outParameter);
params.appendColumn(aParameter);
aDb.createProcedure("royalty", body, params);
Executing Stored Procedures
Stored procedures are executed using the MySQL CALL syntax.
Here’s an example of processing all of the different parts of the stored procedure. We'll use the procedure shown in
“Creating Stored Procedures.”
RWDBStoredProc royalty = aDb.storedProc("royalty");
float percentage;
royalty << myTitleId; // from application input
royalty << myNewSales; // from application input
royalty << &percentage; // will process on return
RWDBTable royaltyTable = royalty.execute().table();
RWDBReader reader = royaltyTable.reader();
while (reader())
{
// save or display the royalties
}
// retrieve the output parameters
royalty.fetchReturnParams();
cout << percentage << endl;
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 instantiation. The schema must include the RWDBColumn::ParamType of each parameter in the stored procedure.
For example, the code in
“Creating Stored Procedures” uses the schema
params to create the stored procedure;
params could be used to instantiate an
RWDBStoredProc. Note that the
RWDBColumn parameter for specifying the return value is not used by the DB Access Module for MySQL.