How to create a map of a stored procedure result set

1	In Navigator, select the appropriate project.

2	Choose File→New→Map.

3	In Data Source:

Select the appropriate data source.

Select Create maps by selecting stored procedures, as shown in Figure 2-36.

Figure 2-36

Creating a map by selecting a stored procedure

Choose Next.

4

In Maps:

In Catalog, select the appropriate catalog.

In Filter:

To display stored procedures from a particular schema, type the first few characters of the schema name in Schema name prefix, for example, dbo. Do not append an asterisk, for example, dbo*. This filter is case-sensitive.

To display only stored procedures whose names begin with a particular string, type the string in Stored procedure name prefix, for example, ac. Do not append an asterisk, for example, ac*. This filter is case-sensitive.

Choose Apply filter.

Move the appropriate stored procedure name from Available to Selected.

Parameters For Stored Procedure appears.

In Parameters For Stored Procedure:

In Statement, shown in Figure 2-37, correct the syntax if it is incorrect.

If you are using a configurable database type, check your JDBC driver documentation for the correct syntax.

Figure 2-37

Correcting the syntax

In Parameters, for the stored procedure’s input and input/output parameters:

If necessary, choose the correct data type for each parameter from the Data type drop-down list. The Actuate SQL data type must be compatible with the native SQL data type.

If necessary, choose the correct parameter mode for each parameter from the Parameter mode drop-down list.

Type the values, as shown in Figure 2-38.

Figure 2-38

Specifying parameter values for a stored procedure

Choose OK. The name of the stored procedure appears in Selected, as shown in Figure 2-39.

Figure 2-39

Result of specifying input parameters for a stored procedure

Choose Next.

5

In Maps:

Move the appropriate result set from Available to Selected.

Result Set Name appears.

In Name, type the name of the result set, as shown in Figure 2-40.

Figure 2-40

Naming the stored procedure’s result set

Choose OK. The result set name appears in Selected. The result set columns appear in Data column preview, as shown in Figure 2-41.

Figure 2-41

Viewing the list of result set columns

Choose Finish. A reminder appears. Choose OK.

The .epr and .sma file names appear in Navigator.

6	In Output Columns:

To rename a column, type the new name in Name.

Decide on column names before you build an information object from this map. Changing a column name after you build an information object results in a compiler error in the information object.

If necessary, choose the correct data type from the Data type drop-down list.

The Actuate SQL data type must be compatible with the native SQL data type.

To create a filter on a column, set the column’s Filter property to Predefined and choose Prompt editor to specify the filter’s prompt properties. Figure 2-42 shows Output Columns and the location of the Prompt editor button for the first column.

To define other column properties, select the column and define the properties in Properties.

Figure 2-42

Columns created by the selected result set of a stored procedure

Copyright Actuate Corporation 2012