Contents

DB Stored Procedure Definitions

Displays and allows you to edit the definition of a stored procedure. This is either the definition as passed in by the ODBC driver, or the definition that you have modified previously in this dialog box.

Use this dialog to augment a stored procedure definition if the ODBC driver did not pass in a complete definition. ODBC drivers differ in the amount of information that they pass to Interaction Designer. Some will pass all input and output parameters, some won't. Use this dialog if you realize that your ODBC driver is passing an incomplete definition.

Note: The definition that you create here is stored on the CIC server. When you select this definition in a DB Stored Procedure step and then save that handler document, the definition will also be stored within the handler. It is possible that the definition stored on the CIC server will become different from the definition stored with the handler. This can occur if you make a change to the stored procedure itself on the database server, and then only update the copy on the CIC server or the copy in the handler. If you open a handler and Interaction Designer detects that there is a difference in the definition in the handler and the definition on the CIC server, you will be presented with a dialog asking you to select the current definition (the one you want to keep).

Note: Refer to the release notes for last minute notes and cautions for using this tool.

DSN (Catalog =)

The name of the data source on which the procedure was created. The concept of the catalog varies from one RDBMS vendor to the next. It usually corresponds to the physical database, but there are exceptions. For example, with dBase & FoxPro it usually corresponds to the directory in which the data files reside. While some RDBMSs allow you to dynamically which catalog contexts within a connection, CIC currently doesn’t support this, and assumes the catalog is tied to the ODBC DSN. So, if access to multiple catalogs is required, you must create and configure multiple ODBC DSNs.

Schema

The schema in which the stored procedure resides. The "schema" is the official ANSI term for what used to be commonly known as the "qualifier". It is usually the creator/owner of the database object. Worth noting is that Sybase and MS SQL Server have a special schema name that is used when the owner of the object is also the database owner: "dbo".

Stored Procedure

The name of the stored procedure as it is stored on the DB server. You cannot change the name of the stored procedure from this field.

Parameters

After you have specified a stored procedure in the previous parameter, this list is populated with any parameters passed in by the ODBC driver, and any parameters that you have previously added to this definition. Add or edit parameters in this list as necessary. Adding or editing a parameter does not change the actual stored procedure (on the database server), only Interaction Designer's definition of it. Any change to the actual procedure must be performed on the database server where the stored procedure resides.

Result Columns

After you have specified a stored procedure, this list is populated with any result columns passed in by the ODBC driver, and any result columns that you have previously added to this definition. Add or edit result columns in this list. Adding or editing a result column does not change the actual stored procedure, only Interaction Designer's definition. Any change to the actual procedure must be performed on the database server where the stored procedure resides.

Return Value Type

A value returned as a result of the processing performed by the stored procedure. The type of value returned depends on the RDBMS. For example, Oracle and DB2 allow you to return most legal SQL data types (LONG or BLOB types usually being the exceptions), but Sybase and MS SQL Server both limit return types to INTEGERs. To my knowledge, none of the RDBMSs allow complex types, like lists, to be returned.

Return Value Type Size

The amount of memory to reserve for the returned value. This field will only be enabled for SQL Types that do not have fixed size.

Return Value Type Decimal Digits

For values that are SQL Float type, this specifies the number of digits to store. This is applicable only to Decimal and Numeric types.