Execute Update SQL

Operation Name

Execute Update SQL

Function Overview

Write data using SQL statements.

Data Model

Data model of this type of component is table model type.

Properties

For information about using variables, refer to "variables".

Basic settings

Item name	Required / Optional	Use of Variables	Description	Remarks
Name	Required	Not available	Enter the name on the script canvas.
Input data	Optional	Not available	Select the component on the script canvas.	When SQL parameters is not used in [SQL statement], [Input data] is not specified.

Required settings

Item name	Required / Optional	Use of Variables	Description	Remarks
Destination	Required	Not available	Select Global Resources. [Add]: Adds new global resource. [Edit list]: Global resource settings can be edited by "Edit resource list".
SQL statement	Required	Available	Enter the SQL statements to issue to the database.	Disabled if [Destination] is not set or invalid. "SQL parameters" can be used. You can specify SQL sentences such as INSERT , UPDATE, DELETE or DDL sentence which returns nothing. When SQL parameter is not included in [SQL statement], executes executeUpdate() method of java.sql.Statement Class, if SQL parameter is included, executes executeUpdate() method of java.sql.PreparedStatement Class. Please contact database vendor about executable SQL statement other than the above. You are allowed to input characters up to "2147483647". However, in case there is a pre-set limitation in the database or the driver, it would be the upper limit.
Schema definition	Optional	-	Sets SQL parameters specified in [SQL statement].	It is not displayed when [SQL statement] does not use SQL parameter. Column name shown in [Schema definition] is displayed in schema of Mapper.
Schema definition / Column name	Required	Not available	Set the SQL parameter name.	It is not displayed when [SQL statement] does not use SQL parameter.
Schema definition / Type	Required	Not available	Select the SQL parameter type.	It is not displayed when [SQL statement] does not use SQL parameter. Displayed in the format of "Native type (Internal data type)" . Native type: The data type of each specific RDBMS. Internal data type Data type used internally in DataSpider. Refer to Internal data type for details.

Property Action

Item name	Description	Remarks
Launch Table Browser	Launches the Table Browser. For the operating procedure, see Table Browser.	Enabled when [Destination] is selected.
SQL wizard	Start the SQL wizard. For instructions, refer to "Update SQL wizard".	SQL statements specified in [SQL statement] cannot be edited with wizard.

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

Depending on the settings for the [Schema definition] the number of columns may vary.

See "Schema of Table Model" for details regarding schema structure.

Output Schema

None.

Loading schema in Mapper

Schema is loaded automatically.

For any details, please refer to the 'Editing Schema'

Transaction

Transaction is supported.

PSP Usage

PSP is supported.

For details on PSP, refer to "Parallel Stream Processing".

Available Component variables

Component variable name	Description	Remarks
count	Returns the number of updated data.	The value defaults to null. In the batch update, returns the number of input data. e.g.:if multiple records are updated by 1 input data, count is assigned as "1". If no update is made, it also returns the number of input data.
error_code	Stores an error code corresponding to occurred SQL exception (SQLException).	The value defaults to null. Upon successful completion, "0" is returned. The error code is based on the specifications of each driver vendor.
message_category	Stroes the category to which corresponding message code belongs to, when an error occurs.	The value defaults to null.
message_code	Stores its corresponding message code of occured error.	The value defaults to null.
message_level	Stores the severity of the corresponding message code of the occured error.	The value defaults to null.
error_type	Stores the type of the occured error.	The value defaults to null. Error is represented in the format depicted below. Example:java.io.FileNotFoundException The message may vary depending on the DataSpider Servista version.
error_message	Stores the error message for the occured error.	The value defaults to null. The message may vary depending on the DataSpider Servista version.
error_trace	Stores stack trace of the occurred error.	The value defaults to null. The message may vary depending on the DataSpider Servista version or the client application used.

SQL parameters

In Execute SQL Update, SQL parameters can be used.

In database adapters, batch update will be executed when using SQL parameter. Batch update is a function to send multiple SQL statements to the database and process them at once. Performance is better than executing each single SQL statement.

Depending on the kind and the type of database, batch update may not be executed even when using SQL parameter.

For details, refer to "DB adapter Limitations".

Using SQL Parameters

SQL parameter is described as "?{name}"."name "can be specified freely. Mapper sets the SQL parameter values.

For example, when SQL statement is described as "insert into test_table values (?{col1}, ?{col2}, ?{col3})", the 3 SQL parameters (col1, col2, col3) can be changed dynamically. If sets this Execute Update SQL to the output destination of Mapper, the output schema of Mapper shows col1, col2, col3.Values can be set by mapping Mapper to each of col1, col2, col3.

When specifying string type to SQL parameter, encloses it with single quotation.

If script variables are used in [SQL statement], SQL statement will not be pre-compiled.
To use pre-compiled SQL statement, use SQL parameters.

About the error log

If an error occurs during the batch update, information related to the error is output as error log in the dump file.

Destination

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

Output content

The following information will be output to FailureResults element.

key attribute value of result element	Description
dump_error_message	Outputs dump information when the error occurred. [SQL]: Executed SQL statement when an error occurs. [Error Records Range]: The starting line number and ending line number where the error was in the input data. [Batch Insert Count]: The number of records successfully batch updated. [Batch Size]: Batch size. [Error Records]: Data used in the batch update when the error occurred is output.Outputs as much as Error Records Range of input data.
error_code	The error code when SQL exception (SQLException) occurred.

key attribute value of result element

Description

dump_error_message

Outputs dump information when the error occurred.

[SQL]:
Executed SQL statement when an error occurs.
[Error Records Range]:
The starting line number and ending line number where the error was in the input data.
[Batch Insert Count]:
The number of records successfully batch updated.
[Batch Size]:
Batch size.
[Error Records]:
Data used in the batch update when the error occurred is output.Outputs as much as Error Records Range of input data.

error_code

The error code when SQL exception (SQLException) occurred.

Batch update may not be executed depending on database of data type of the writing destination.
Refer to " Write table limitations "in "DB adapter Limitations".
Error log is output when error occurs when executing the query.
If an error is occurred in the Execute update SQL operation in monitoring process in exception monitoring, a dump file will not be output.

Specification Limits

SQL parameter name can not use "?".

Depending on the SQL statements executed, the number of updates successfully performed may not be obtained correctly. (Example:Table creation)

For the specification limit for each database adapter, refer to "DB adapter Limitations".

Main exceptions

Exception name	Causes	Solution
ResourceNotFoundException Resource definition could not be found.Name: []	[Destination] is not specified.	Specify [Destination].
ResourceNotFoundException Resource definition could not be found.Name: [<name of Global Resources>]	Resource definition selected in [Destination] is not found.	Please check the global resource specified in [Destination]
InvalidPropertyConfigurationException	[SQL statement] is not set.	Please specify [SQL statement].
java.sql.SQLException	Database access error or other error occurred.	Check the message of SQLException.SQLException message is based on the specifications of each driver.SQLException messages may outputs cause of the error. For more information about the message, please contact each database vendor.

Notes

When updating columns treated as byte[] type, updates according to the specification of the database.

For the notes for each database adapter, refer to "DB adapter Limitations".