The Stored Procedure Call Editor is used to edit Stored Procedure Calls in Rhapsody. It allows the procedure call to be edited, output parameters to be defined, and result substitution to be configured.
For details on the syntax for stored procedure SQL, refer to Stored Procedures.
To open the Stored Procedure Call Editor, double click on the statement you want to maintain from the Rhapsody Database Configuration Editor. Alternatively, select the stored procedure and then select the Edit Statement icon 
from the Statement Toolbar. The Stored Procedure Call Editor is then displayed:
The Stored Procedure Call Editor comprises the following key sections:
Section |
Description |
|---|---|
Statement |
The name of the SQL Statement. Click the Rename link to display the Rename Statement screen, which enables you to enter a new name for the statement. By default, when the statement is created, the statement is assigned a name |
Comments |
Enter any comments for this statement. Comments are shown on the Rhapsody Database Configuration Editor and enable users to quickly identify the purpose of the statement. |
Output |
Many stored procedures use output parameters, allowing values to be returned back to the caller. Refer to Output Parameters for details. |
Stored Procedure Call Editor Pane |
The Stored Procedure Call Editor is a color-coded text editor that supports SQL. It allows the SQL statements to be written, field paths to be easily inserted, and results from previous statements to be accessed. In addition, it allows SQL comments to be placed within the SQL code itself, making it easier to document the exact behavior of a particular query. Refer to SQL Editor for details. |
Result Substitution Panel |
Once queries have been executed, the results can be inserted into either message properties, fields, or both. Result substitution is primarily used within a Database Lookup filter, where a message is being updated with some values retrieved via one or more database queries. Refer to Result Substitution for details. |
Server Explorer |
Displays the database tables, views, and stored procedures. This allows easy reference to the database. |
Message Structure |
Displays the messages structure. This allows easy reference to the message definition and enables you to drag and drop message properties into your query. |
Calling Stored Procedures
The syntax for stored procedure SQL used with the Database communication point and filters takes one of the following two general forms:
{ call <procedure-name>[<arg1>,<arg2>, ...] }
{ <returnValue> = call <procedure-name>[<arg1>,<arg2>, ...] }
A procedure may take zero or more parameters and, if a function, also return a value. The parameters for a stored procedure may be IN parameters or OUT parameters.
Output Parameters
Many stored procedures use output parameters, allowing values to be returned back to the caller. In the Rhapsody Database Configuration Editor, output variables must be declared prior to use so that the caller knows the parameter type and can marshal it appropriately. The output parameter editor is shown below:
The following tables describes the fields associated with Output Parameters.
Field |
Description |
|---|---|
Name |
The user-defined name of the output parameter. This is the name as it is used within the stored procedure call. It does not have to be the same name that the stored procedure uses internally for the parameter. |
Type |
The type of the output parameter. The type is required and must correspond to either one of the types available by default in the drop-down list, or a vendor-specific type that is specific to the JDBC database driver that is being used. |
Description |
An optional user-defined description for this output parameter. This has no effect on the stored procedure call and is present purely for documentation purposes. |
Result Set |
When using Oracle®-style result sets that are returned as cursors, this flag allows one output parameter to be defined as the result set parameter. The effect of this is that Rhapsody will use this parameter as a result set to retrieve the available columns. Only one such parameter may be designated the result set parameter for each stored procedure call, and the type of this parameter should be the appropriate vendor-specific cursor type. |
An output parameter can be removed by clearing the parameter name.
Generating Stored Procedure Calls
While stored procedure calls can always be manually configured, the Rhapsody Database Configuration Editor can automatically generate a stored procedure or function call. This is done by selecting a stored procedure in the Server Explorer, then clicking the Generate SQL button as shown in the following diagram:
Stored procedure calls can be generated as a procedure or function call (stored procedures whose metadata indicates that they do not have a return value can only be generated as procedure calls). This inserts the appropriate call into the text editor, and replaces the existing output parameters that are declared with the output parameters required to call the selected procedure.
Alternatively, stored procedure calls can be generated by right-clicking and draging a stored procedure from the Server Explorer onto the text editor. A context menu is then displayed as shown in the following diagram:
