DataLayer.SP
Developers working with SQL databases often prefer to use Stored Procedures rather than direct SQL statements in their applications. This topic introduces the developer to the DataLayer.SP element, which provides the capability of running stored procedures on SQL database servers.
Stored procedures executed using this element must return a rowset. Ensure that they end with a SELECT statement.
Oracle Note: Connection strings to Oracle databases must include the statement "PLSQLRSet=1" in order to use stored procedures.
Attributes
The DataLayer.SP element has the following attributes:
| Attribute | Descriptionj |
|---|---|
ID | (Required through v10.1.46) A unique element ID. |
| Command | (Required) The name of the stored procedure (i.e. the name called to execute it). |
| Connection ID | The ID of a datasource connection defined in the _Settings definition. If left blank, the datalayer will use the first connection in the _Settings definition. For clarity, developers are advised to enter an ID here in all cases. |
| Maximum Rows | The maximum number of results rows to retrieve. Default: no limit. |
Working with DataLayer.SP
In most respects, DataLayer.SP functions exactly as other datalayer elements do and its data can be filtered and conditioned using appropriate elements.
As shown above, a DataLayer.SP element is added as a child element to a data table or other data container element, and its attributes as set so that is calls the desired stored procedure on the database server.
Note that the only text in the Command attribute value is the name of the stored procedure; you do not need to enter "EXECUTE" or other SQL commands here. For most major database servers, you can click the arrow at the end of this attribute to retrieve a list of available stored procedure names and then select the one you want. Input and output parameters are handled using special elements that are children of the datalayer and they're discussed in the next section, below.
Result set data returned into the datalayer can be referenced using @Data tokens.
Stored Procedure Parameters
Stored procedures return result sets but developers can also pass values into, and receive them back from, stored procedures as parameters by using SP Parameter elements.
As shown above, an SP Parameters element has been added as the container for one or more SP Parameter elements. The SP Parameter element has the following attributes:
| Attribute | Description |
|---|---|
Direction | The direction or type of parameter: Input parameters are used to send values to the stored procedure but cannot be modified by it. Output parameters can be modified by the stored procedure and the returned values read in a Logi Process definition (not in a Report definition). Return parameters provide the stored procedure's native Return value or the results of a RETURN statement in the procedure (also in Process definitions only). |
| ID | An arbitrary ID for this parameter. Input parameters sent to the stored procedure are recognized sequentially; the identifier assigned here is not associated with any parameter or local variable name in the stored procedure. |
| Size | Specifies the size or length of the parameter in bytes. If this is set to 0, the size will be automatically determined at runtime based on the length of the actual data used. |
| Type | Specifies the data type of the data used or expected. The data types used with stored procedures are discussed in detail in Stored Procedure Data Types. |
| Value | Specifies the actual value to be sent as an Input parameter; should be left blank for Output or Return parameters. Tokens can be used here to represent the value. |
Multiple SP Parameter elements are used if there are multiple parameters but input parameters should be contiguous in the element tree and organized sequentially to match the order of the parameters declared in the stored procedure.
Studio includes a wizard that will add all of the parameters required for a stored procedure. With the DataLayer.SP element selected in the Workspace editor, click the wizard link, shown above, in the Element Toolbox. You must have a valid connection to the database defined at this time.
This example further illustrates the correlation between Input-type SP parameters and the stored procedures variables.
Note that the SP Parameter element IDs in the procedure do not need to match the SP variables; they're assigned sequentially to variables in the stored procedure.
Using Studio's DataLayer Wizard
Beginning in v10.0.299, Studio includes a wizard that can assist you in configuring DataLayer.SP. The wizard assumes that you have already added an appropriate database Connection element in the _Settings definition and configured it.
As shown above, the wizard can be started by selecting and then right-clicking the parent element under which you want to add the datalayer, and using the context menus to select "Add a Stored Procedure DataLayer." The wizard will open; use it as follows:
- Select the ID of the Connection element from the drop-down list of available connections. Click Next to continue.
- Select the desired stored procedure from the drop-down list of available stored procedures. Click Next to continue.
- Click Next to have the wizard insert the DataLayer.SP and any SP Parameter elements it requires.
- Click Finish to close the wizard.
- The wizard has inserted the datalayer and configured its attributes, as shown above, and has inserted and configured the required parameters. The input parameters are configured assuming that a @Request token will be used to provide their values.