Stored Procedures

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

2.9 Stored Procedures

The DB Access Module for Sybase includes full support for Adaptive Server stored procedures, including support for creating and dropping stored procedures, retrieving stored procedure text and parameter definitions, executing stored procedures, processing multiple result sets, processing output parameters, and processing return values.

Stored procedures are encapsulated by the class RWDBStoredProc. The implementation uses the Client-Library RPC commands rather than an execute language statement, which allow parameters to be passed as native data types instead of character data within the text of the language command. The Client-Library documentation indicates that using RPC commands is the most efficient way to handle stored procedures.

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, which looks up a royalty percentage and returns the royalty schedule based on title and sales level:

RWCString body =
   "declare @total int                                    \n"
   "select @total = titles.total_sales                    \n"
   "    from titles where title_id = @title_id            \n"
   "if ( @total > 0 )                                     \n"
   "  begin                                               \n"
   "    select @total = @total + @newsales                \n"
   "    select @pc = royalty 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"
   "    return 1                                          \n"
   "  end                                                 \n"
   "else                                                  \n"
   " return 99                                              ";

RWDBSchema params;
RWDBColumn aParameter;

aParameter.name("@title_id").type(RWDBValue::Long);
aParameter.paramType(RWDBColumn::inParameter);
params.appendColumn(aParameter);

aParameter.name("@newsales").type(RWDBValue::Long); 
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);

2.9.2 Executing Stored Procedures

With Sybase stored procedures, an application has the option of processing return parameters, a return status, and/or the results of selects performed by the stored procedure.

It is very important that the application performs the correct sequence of processing in order to successfully process each part of a stored procedure as it is returned from the server.

Because of the way that Sybase Client-Library handles result sets and return values, you must always process the result set before calling RWDBStoredProc::returnValue() or RWDBStoredProc::fetchReturnParams(). Calling either of these functions before processing the result set generates an empty reader, even for result sets generated by nested stored procedures.

Here's an example of processing all of the different parts of the stored procedure. We'll use the procedure shown in Section 2.9.1, "Creating a Stored Procedure."

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
}
if (royalty.returnValue().asInt() == 1)
{
    // continue processing with the royalty percentage   
    // returned
    royalty.fetchReturnParams();
    if (percentage > 10.0)
   ...
}
else
   cout << "invalid title id" << endl;

Please note that when retrieving stored procedures from Adaptive Server Enterprise 12.5.1 or earlier, you won't be able to determine if a parameter is an input, an output, or an in-out parameter. The Sybase system table does not carry this information.

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. Since all stored procedures in Sybase return an integer by default, the last parameter of type RWDBColumn that specifies the return value type is ignored. For example, the code in Section 2.9.1 uses the schema params to create the stored procedure; params could be used to instantiate an RWDBStoredProc. The paramType field is not necessary, but the name of each column /parameter in the schema must match the stored procedure parameter name exactly.

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.