Execute Stored Procedure

Operation Name

Execute Stored Procedure

Function Overview

Execute a stored procedure.

Data Model

Table model type.

Properties

For details on use of variables, refer to “Variables”.
Basic settings
Item name Required/Optional Use of variables Description Remarks
Name Required Not available Enter the name to show on the script canvas.  
Input data Optional Not available Select a component on the script canvas.
  • Specify when an input parameter is defined in the specified stored procedure.
Required settings
Item name Required/Optional Use of variables Description Remarks
Destination Required Not available Select a global resource.
  • [Add]:
    Add a new global resource.
  • [Edit list]:
    Global resource settings can be edited in “Edit resource list” screen.
  
Procedure Required Available Select or enter a procedure name to be executed.
  • Procedures of the number specified in global resource's list settings are displayed.
Input Optional - The input parameters of the stored procedure specified in [Procedure] are displayed.
  • Displayed when an input parameter is defined in the specified stored procedure.
  • The parameter names displayed in [Input] will be displayed in Mapper schema.
Input/Parameter name Required Not available The input parameter name is displayed.
  • Displayed when an input parameter is defined in the specified stored procedure.
Input/Type Required Not available The input parameter type is displayed.
  • Displayed when an input parameter is defined in the specified stored procedure.
  • It is displayed in “<Native type> (<Internal data type>)” format.
    • Native type:
      RDBMS specific data type.
    • Internal data type:
      Data type used internally within DataSpider.
      Refer to “Internal Data Types” for more details.
Output Optional - The output parameters of the stored procedure specified in [Procedure] are displayed.
  • Displayed when an output parameter is defined in the specified stored procedure.
  • The parameter names displayed in [Output] will be displayed in Mapper schema.
Output/Parameter name Required Not available The output parameter name is displayed.
  • Displayed when an output parameter is defined in the specified stored procedure.
Output/Type Required Not available The output parameter type is displayed.
  • Displayed when an output parameter is defined in the specified stored procedure.
  • It is displayed in “<Native type> (<Internal data type>)” format.
    • Native type:
      RDBMS specific data type.
    • Internal data type:
      Data type used internally within DataSpider.
      Refer to “Internal Data Types” for more details.
Property actions
Item name Description Remarks
Launch table browser Launch the table browser.
For details on how to operate, refer to “Table Browser”.
  • Enabled when [Destination] is selected.
Update parameters Update input/output parameters of the stored procedure specified in [Procedure].
  • It cannot be executed in the following cases.
    • When the specified stored procedure does not exist
    • When a variable is specified in [Procedure]
    • When [Procedure] is not entered
    • When an error has occurred in [Procedure]
Execution settings
Item name Required/Optional Use of variables Description Remarks
Enable query timeout Required Not available Select whether or not to enable query timeout.
  • [Checked]:
    Set query timeout.
  • [Not checked]: (default)
    Do not set query timeout.
  • When you select [Not checked], the behavior of the JDBC driver when query timeout is not set will be applied.
Query timeout (seconds) Required Available Set query timeout.
  • Enabled when [Enable query timeout] is [Checked].
  • Allowed values are 0-2147483647.
  • The value input in this property will be specified at an argument for JDBC setQueryTimout() method.
  • Although the seconds to wait execution is generally set for query timeout, the behavior might be different depending on database or using JDBC driver.
Data processing method
Item name Required/Optional Use of variables Description Remarks
Data processing method Required Not available Select data processing method.
  • [Apply script settings]: (default)
    Apply the mass data processing settings of the script property to the adapter.
  • [Do not perform mass data processing]:
    Mass data processing will not be performed.
  • [Perform mass data processing]:
    Mass data processing will be performed.
  
Comment
Item name Required/Optional Use of variables Description Remarks
Comment Optional Not available You can write a short description of this adapter.
The description will be reflected in the specifications.		  

Schema

Input schema

The number of columns varies depending on the [Input] settings.
Refer to “Schema of table model type” for more details.

Output schema

The number of columns varies depending on the [Output] settings.
Refer to “Schema of table model type” for more details.

Loading schema in Mapper

Schema is loaded automatically.
Refer to “Edit Schema” for more details.

Mass Data Processing

Mass Data Processing is supported.

Transaction

Transaction is supported.
It depends on the operation details of the procedure to be executed. Data committed in stored procedure will not be rolled back.

PSP Usage

PSP is supported.
Refer to “Parallel Stream Processing” for more details.

Available Component Variables

Component variable name Description Remarks
count The number of executed stored procedures is stored.
  • Default value is null.
  • When input parameters are defined in the specified stored procedure, stored procedures will be executed by the number of input data.
  • Value is not stored in PSP.
error_code When an error (SQLException) occurs, the corresponding error code is stored.
  • Default value is null.
  • 0 is stored when finishing normally.
  • Error code is based on each driver specifications.
message_category When an error occurs, the corresponding message code's category is stored.
  • Default value is null.
message_code When an error occurs, the corresponding message code is stored.
  • Default value is null.
message_level When an error occurs, the corresponding message code's severity is stored.
  • Default value is null.
error_type When an error occurs, the error type is stored.
  • Default value is null.
  • The error type will be in the following format.
    Example: “java.io.FileNotFoundException”
  • The contents that will be stored may vary depending on DataSpider Servista versions.
error_message When an error occurs, the error message is stored.
  • Default value is null.
  • The contents that will be stored may vary depending on DataSpider Servista versions.
error_trace When an error occurs, the error's trace information is stored.
  • Default value is null.
  • The contents that will be stored may vary depending on DataSpider Servista versions and client applications.

Specification Limits

For more details on the specific limitations of each database adapter, refer to “Database Adapter Limitations”.

Major Exceptions

Exception name Cause Solution
ResourceNotFoundException
Resource definition is not found. Name: []		 [Destination] is not specified. Specify [Destination].
ResourceNotFoundException
Resource definition is not found. Name: [<global resource name>]		 The resource definition selected in [Destination] is not found. Check the global resource specified in [Destination].
InvalidPropertyConfigurationException [Procedure] is not set. Specify [Procedure].
java.sql.SQLException A database access error or some other one has occurred. Check the SQLException message. SQLException message is based on the specifications of the driver. The cause of the error might be output in SQLException message.
For more details on messages, contact the database vendor.

Notes

For more details on the specific limitations of each database adapter, refer to “Database Adapter Limitations”.