Execute Update SQL

Operation Name

Execute Update SQL

Function Overview

Write data using a SQL statement.

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.	Do not specify [Input data] when SQL parameter is not used in [SQL Statement].

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.
SQL statement	Required	Available	Enter the SQL statement to be issued to the database.	Disabled when [Destination] is not set or invalid. “SQL Parameter” can be used. SQL statements that does not return anything, such as INSERT, UPDATE, DELETE, or DDL, can be specified. You cannot specify SQL statement joined with multiple queries. When a SQL parameter is not included in [SQL statement], executeUpdate() method of java.sql.Statement class will be executed, and when a SQL parameter is not included, executeUpdate() method, or addBatch() and executeBatch() method of java.sql.PreparedStatement class will be executed. For more details on the executable SQL statements other than the mentioned above, inquire the database vendor. The upper limit of the input value size is about 60,000 bytes. However, when there is a size limitation in the destination database or the JDBC driver, the limitation will be the upper limit.
Schema definition	Optional	-	Configure SQL parameters specified in [SQL Statement].	Not displayed when a SQL parameter is not used in [SQL statement]. The column names displayed in [Schema definition] will be displayed in the Mapper schema.
Schema definition/Column name	Required	Not available	Set the SQL parameter name.	Not displayed when a SQL parameter is not used in [SQL statement].
Schema definition/Type	Required	Not available	Select the type of SQL parameter.	Not displayed when a SQL parameter is not used in [SQL statement]. 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.
Launch SQL Wizard	Launch the SQL Wizard. For details on how to operate, refer to “Update SQL Wizard”.	In SQL Wizard, you can create a new SQL statement and edit a SQL statement which was created in it. When [SQL statement] is specified manually without using SQL Wizard, the specified SQL Statement cannot be edited in SQL Wizard. When [SQL Statement] created using SQL Wizard is edited manually, the edited SQL statement cannot be re-edited in SQL Wizard. Select whether to create newly or to edit the SQL statement created in SQL wizard from the dialog and launch it.

Write 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. There is not this property in the following adapters. Access Adapter
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. There is not this property in the following adapters. Access Adapter

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 [Schema definition] settings.

Refer to “Schema of table model type” for more details.

Output schema

None.

Loading schema in Mapper

Schema is loaded automatically.

Refer to “Edit Schema” for more details.

Transaction

Transaction is supported.

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 updated data is stored.	Default value is null. The number of input data is stored in batch update processing. Example: When multiple data are updated with one input data record, then 1 is stored in count. Even when it is not updated, the number of input data is stored.
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.

SQL Parameter

SQL parameter can be used in Execute Update SQL operation.

In database adapters, when SQL parameter is used, batch update will be performed. Batch update is a function to send multiple SQL statements to a database at once and make it process. Compared to executing SQL statements one by one, this enhances the performance.

According to the database type, there might be cases that batch update is not performed even if SQL parameter is used.

Refer to “Database Adapter Limitations” for more details.

How to use SQL parameter

SQL parameter is described as “?{name}”. You can specify “name” arbitrarily. Values are set to SQL parameters using Mapper.

For instance, when “insert into test_table values (?{col1}, ?{col2}, ?{col3})” is described in SQL statement, three SQL parameters (col1, col2 and col3) can be assigned dynamically. When this Execute Update SQL operation is set to the output destination of Mapper, col1, col2, and col3 will be displayed in the output schema of Mapper. You can map values to col1, col2, and col3 using Mapper.

When specifying string type in SQL parameter, it is not enclosed with single quotes.

When SQL parameter is not used in [SQL statement], the SQL statement will not be precompiled.
To use a precompiled SQL statement, use SQL parameters.

Error Log

When an error occurs during batch updating, information about the error is output in Dump file as error log.

Output destination

$DATASPIDER_HOME/server/logs/<date directory>/execution/<execution ID directory>/dump.xml

Output contents

Outputs the following information to failureResults element.

key attribute value of result element	Description
dump_error_message	Outputs the dump information of when the error occurred. [SQL]: The SQL statement executed when the error occurred. [Error Records Range]: The starting and ending line numbers where the error was in the input data. [Batch Insert Count]: The number of data succeeded to batch update. [Batch Size]: The batch size. [Error Records]: The data used in batch update when the error occurred. The data of Error Records Range in the input data is output.
error_code	The error code when a SQL Exception occurred.

key attribute value of result element

Description

dump_error_message

Outputs the dump information of when the error occurred.

[SQL]:
The SQL statement executed when the error occurred.
[Error Records Range]:
The starting and ending line numbers where the error was in the input data.
[Batch Insert Count]:
The number of data succeeded to batch update.
[Batch Size]:
The batch size.
[Error Records]:
The data used in batch update when the error occurred. The data of Error Records Range in the input data is output.

error_code

The error code when a SQL Exception occurred.

Depending on database or data type to write to, batch update will not be executed.
For more details , refer to “Write Table” of “Database Adapter Limitations”.
When an error occurs during executing a query, error log is output.
When an error occurs in Execute Update SQL operation inside Try operation of Monitor Exception operation, the dump file of that operation is not output.

Specification Limits

“?” cannot be used in SQL parameter name.

Depending on the SQL to be executed, the number of update data after successful completion may not be retrieved properly. (Example: Create table)

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	[SQL statement] is not set.	Specify [SQL Statement].
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

When updating a column handled as byte[] type, it will be updated based on the database specification.

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