Calling Database Stored Procedures in Scheduled Tasks

4.1.3

Allowed calling stored procedures if the data source you set in a scheduled task was a MySQL, Oracle, or SQL Server database.

4.1.11.2

• Allowed calling void-returning stored procedures from HP Vertica and GaussDB 200 data sources using the SQL Script node.

• Allowed using a single query result set returned by a GaussDB 200 stored procedure as an input table in the Data Synchronization/Data Transformation/Parameter Assignment node. Returning cursor result sets was not supported by GaussDB 200.

4.1.12.2

• Allowed calling void-returning stored procedures from IBM Db2 and SAP HANA data sources using the SQL Script node.

• Allowed returning query result sets through cursors for IBM Db2 stored procedures in Data Synchronization, Data Transformation, and Parameter Assignment.

• Allowed using a single query result set returned by an SAP HANA stored procedure as an input table in Data Synchronization/Data Transformation/Parameter Assignment. Returning cursor result sets was not supported by SAP HANA.

4.2.4.1

Allowed previewing result set data if Return Result Set was set to Query Result Set in Data Synchronization, DB Table Input in Data Transformation, and Parameter Assignment.

4.1.3

MySQL

Not supported

Supported

Oracle

Supported

Not supported

SQL Server

Not supported

Supported

4.1.11.2

HP Vertica

Not supported

Not supported

GaussDB 200

Not supported

Supported (a single query result set)

4.1.12.2

IBM Db2

Not supported

Supported

SAP HANA

Not supported

Supported (a single query result set)

SQL Script

You can call void-returning stored procedures.

Data Synchronization

You can use a result set returned by a stored procedure (excluding those in HP Vertica, DB2, and SAP HANA) as the input table).

DB Table Input in Data Transformation

Parameter Assignment

Configuration Method

Select Stored Procedure. The following configuration items will be displayed.

Stored Procedure

Select an existing void-returning stored procedure.

Parameter Configuration

Parameter Type/Parameter Name: They are automatically obtained.

Parameter Value: The system automatically fills in the default values of the stored procedure parameters. You can change the values and reference parameters. You can input the symbol $ for associative parameter selection/handwriting, and use existing parameters as input/output parameters. You can also select Null Value.

You can use ${Parameter name} to reference parameters. For details, see Parameter Configuration and Use.

Note:

1. IBM Db2 supports IN, OUT, and INOUT parameters. OUT parameters can be used to return output values or status values.

2. INOUT parameters in SAP HANA only support scalar values and need to be parameterized. Non-parameterized calling of stored procedures with INOUT parameters is not supported.

Note:

• The order of parameters should correspond to that of the input and output parameters of the stored procedure. The order in which parameters are passed into the stored procedure is from top to bottom.

• The parameter direction should be consistent with that of the parameters in the database stored procedure.

Configuration Method

Select Stored Procedure. The following configuration items will be displayed.

Stored Procedure

Select an existing stored procedure that will return a result set.

Returned Result Set/Result Set Selection

1. After selecting a stored procedure in Stored Procedure, the result set type is automatically displayed in Returned Result Set as Query Result Set or Cursor Result Set.

2. For FineDataLink of V4.2.4.1 and later versions, when the result set type is Query Result Set, you can click Select in Result Set Selection to preview the result set data and select the result set.

Note:

If previewing a result set fails, you cannot select the result set.

Parameter Configuration

Note:

1. Only input parameters are displayed in the parameter list. Output parameters are not displayed currently.

2. Oracle and SQL Server stored procedures can return result sets through OUT parameters, which is of the cursor type. Cursor-type parameters are not displayed in the parameter list.

3. You can preview all types of parameters in GaussDB 200 stored procedures. However, during the configuration of Field Mapping in Data Destination and Mapping, only IN and INOUT parameters can be mapped.

4. IBM Db2 supports IN, OUT, and INOUT parameters. OUT parameters can be used to return output values or status values.

5. You need to use the DECLARE CURSOR WITH RETURN statement to declare cursors for IBM Db2 stored procedures, through which a single result set, multiple result sets, or a restricted number of result sets can be returned.

IBM Db2 supports nested calling between stored procedures, and the called stored procedures can return parameters or result sets.

6. For SAP HANA, you can use cursors to call stored procedures to process and determine output data and read read-only data.

Parameter Type/Parameter Name: They are automatically obtained.

Parameter Value: The system automatically fills in the default values of the stored procedure parameters. You can change the values and reference parameters. You can input the symbol $ for associative parameter selection/handwriting, and use existing parameters as input/output parameters. You can also select Null Value.

You can use ${Parameter name} to reference parameters. For details, see Parameter Configuration and Use.

Note:

• The order of parameters should correspond to that of the input and output parameters of the stored procedure. The order in which parameters are passed into the stored procedure is from top to bottom.

• The parameter direction should be consistent with that of the parameters in the database stored procedure.