Design

Back Next

Configuring output fields in stored procedures

Perform the following steps to configure output fields when you use a stored procedure in a data access service connector:

On the

SQL Binding

tab, click the

icon to add an SQL entry.

Click the

Edit

icon to edit the SQL query.

The

Create SQL

page to configure multiple SQL queries appears.

Enter the SQL query name.

In the

Type

list, select

Stored Procedure

The

Create SQL

page gets renamed to

Call Stored Procedure

as shown in the following image:

Iin the

Stored Procedure

field, enter the query in the following format:

<SQL statement> <stored procedure name>(?);

For example, enter the query as shown in the following sample:

CALL GetTotalSales(?)

The number of ? symbols that you provide in the Stored Procedure text box must match the number of output fields that you define in the Output Fields section. You can’t provide INOUT parameters in a stored procedure.

In the Output Fields section, click

Configure the following properties for each output field:

Name

. The name of the output field.

Type

. The type of the output field.

Move

. Click the arrows adjacent to the output field to move the field a level up or down in the

Stored Procedure

table.

Select the column case.

Enter the maximum number of rows returned by the SQL query that can be displayed in the SQL response payload.

Enter the maximum length of time in seconds that a database client can wait for a response from the database before timing out the session.

Click

Create

The following image shows the

Call Stored Procedure

page with sample values provided for a stored procedure:

In the above example, the user has entered the following query in the Stored Procedure field:

CALL GetTotalSales(?);

The corresponding output field is provided in the

Output Fields

section. This output field is then mapped to the output field of the service connector on the

Output

tab of the data access service connector properties page as shown in the following image:

You can configure the following list of data types for the output fields:

Number

Percent

Phone

Picklist

Rich Text Area

Text

Text Area

Time

URL

Attachment

Attachments

Checkbox

Currency

Data

Date Time

Formatted Text

Image

Integer

Multi-Select Picklist

The following snippet shows a sample response in the output when the CALL GetTotalSales(?) stored procedure is run:


{
"outSPProcess":"<multiDataAccessResponse xmlns:aetgt=\"http:\/\/schemas.informatica.com\/socrates\/data-services\/2014\/05\/business-connector-model.xsd\" xmlns:bconn=\"http:\/\/schemas.informatica.com\/socrates\/data-services\/2014\/05\/business-connector-model.xsd\"><dataAccessResponse><result><statementId>StoredProc_WithOutputFields<\/statementId><row><TotalSales>3.00<\/TotalSales><\/row><\/result><\/dataAccessResponse><\/multiDataAccessResponse>"
}

Defining Actions

Download Guide

Watch

Comments

Cloud Application Integration Homepage