Execute Update SQL

Operation name

Execute Update SQL

Function overview

Writes data using a SQL statement.

Data model

The data model of this component is table model type.

Tutorials

For details about how to create a connection to a MySQL database, refer to the following tutorial:

Create a connection to a MySQL database

Properties

= Remarks =

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 a name that is used on the script canvas.
Input data	Optional	Not available	Select a component on the script canvas.	Specify Input data only when a SQL parameter is used in SQL statement.

Required settings

Item name	Required/Optional	Use of variables	Description	Remarks
Destination	Required	Not available	Select a connection resource. Add A new connection resource can be added. Edit list Connection resource settings can be edited in > HULFT INTEGRATE > Connections.
SQL statement	Required	Available	Enter the SQL statement to be issued to the database.	Disabled when Destination isn't set or invalid. SQL parameters can be used. SQL statements that don't return anything, such as INSERT, UPDATE, DELETE, or DDL statements, can be specified. When a SQL parameter isn't included in SQL statement, executeUpdate() method of java.sql.Statement class will be executed, and when a SQL parameter is included, executeUpdate() method or addBatch() method and executeBatch() method of java.sql.PreparedStatement class will be executed. For more details on the executable SQL statements other than the mentioned above, contact the database vendor. The upper limit of the input value size is about 60,000 bytes. However, when there's a size limitation in the connected database or the JDBC driver used, that limitation will be the upper limit. Note You can't specify a SQL statement that consists of multiple queries.
Schema definition	Optional	-	Configure the SQL parameters specified in SQL statement.	Not displayed when a SQL parameter isn't used in SQL statement. The column names displayed in Schema definition will be displayed in Mapper schema.
Schema definition/Column name	Required	Not available	Set the SQL parameter name.	Not displayed when a SQL parameter isn't used in SQL statement.
Schema definition/Type	Required	Not available	Select the type of SQL parameter.	Not displayed when a SQL parameter isn't used in SQL statement. It's displayed in "<Native type> (<Internal data type>)" format. Native type RDBMS specific data type. Internal data type Data type used internally. For details, refer to Internal data types.

Property actions

Item name	Description	Remarks
Launch table browser	Launch the table browser. For details about the operation procedure, refer to Table Browser.	Enabled when Destination is selected.
SQL Wizard	Launch the SQL Wizard. For details about the operation procedure, 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 can't be edited in SQL Wizard. When SQL statement created using SQL Wizard has been manually edited, the edited SQL statement can't be re-edited in SQL Wizard. Start the wizard by selecting in the dialog whether to create a new SQL statement or edit a SQL statement that was created in the wizard.

Item name

Description

Remarks

Launch table browser

Launch the table browser.

For details about the operation procedure, refer to Table Browser.

Enabled when Destination is selected.

SQL Wizard

Launch the SQL Wizard.

For details about the operation procedure, 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 can't be edited in SQL Wizard.
- When SQL statement created using SQL Wizard has been manually edited, the edited SQL statement can't be re-edited in SQL Wizard. Start the wizard by selecting in the dialog whether to create a new SQL statement or edit a SQL statement that was created in the wizard.

Write settings

Item name	Required/Optional	Use of variables	Description	Remarks
Enable query timeout	Required	Not available	Select whether or not to enable the query timeout. Selected Set the query timeout. Not selected (Default) Don't set the query timeout.	When this field isn't selected, the behavior of the JDBC driver when the query timeout isn't set will be applied.
Query timeout (seconds)	Required	Available	Set the query timeout.	Enabled when Enable query timeout is selected. Allowed values are from 0 to 2147483647. The value input in this property will be specified as an argument for JDBC setQueryTimout() method. Generally, the seconds to wait for execution is specified for a query timeout, but the behavior might be different depending on the database or the JDBC driver used.

Item name

Required/Optional

Use of variables

Description

Remarks

Enable query timeout

Required

Not available

Select whether or not to enable the query timeout.

Selected

Set the query timeout.

Not selected

(Default)

Don't set the query timeout.

When this field isn't selected, the behavior of the JDBC driver when the query timeout isn't set will be applied.

Query timeout (seconds)

Required

Available

Set the query timeout.

Enabled when Enable query timeout is selected.
Allowed values are from 0 to 2147483647.
The value input in this property will be specified as an argument for JDBC setQueryTimout() method.
Generally, the seconds to wait for execution is specified for a query timeout, but the behavior might be different depending on the database or the JDBC driver used.

Comment

Item name	Required/Optional	Use of variables	Description	Remarks
Comment	Optional	Not available	You can write a short description of this connector.

Schemas

Input schema

The number of columns varies depending on the Schema definition settings.

= Remarks =

For schema structure, refer to Table model type .

Output schema

None.

Loading schema in Mapper

The schema is loaded automatically.

= Remarks =

For details, refer to Edit Schema.

Transaction

Transaction is supported.

Parallel Stream Processing

PSP is supported.

= Remarks =

For details on PSP, refer to Parallel Stream Processing.

Available component variables

Component variable name	Description	Remarks
count	The number of updated data records is stored.	The default value is null. Note The number of input data records is stored during batch update processing. Example: When multiple data records are updated with one input data record, then 1 is stored in count. Even when it's not updated, the number of input data records is stored.
error_code	When an error (SQLException) occurs, the error code corresponding to the error is stored.	The default value is null. 0 is stored when finishing successfully. Note Error codes are based on the specifications of each driver.
message_category	When an error occurs, the category of the message code corresponding to the error is stored.	The default value is null.
message_code	When an error occurs, the code of the message code corresponding to the error is stored.	The default value is null.
message_level	When an error occurs, the severity of the message code corresponding to the error is stored.	The default value is null.
error_type	When an error occurs, the error type is stored.	The default value is null. The format of the error type is as follows. Example: java.io.FileNotFoundException
error_message	When an error occurs, the error message is stored.	The default value is null.
error_trace	When an error occurs, the trace information for the error is stored.	The default value is null.

SQL parameters

SQL parameters can be used in the Execute Update SQL operation.

In database connectors, when a 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 them process.

Compared to executing SQL statements one by one, the performance is improved.

Note

Depending on the type or what kind of database is used, batch update may not be performed even if a SQL parameter is used.

= Remarks =

For details, refer to Message codes, exception messages, and limitations for each connector.

How to use SQL parameters

A SQL parameter is described as "?{name}". You can specify any value for "name".

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 in Mapper to set values for them.

When specifying a string type value for a SQL parameter, don't enclose it with single quotes.

Note

A SQL statement is precompiled only when a SQL parameter is used in SQL statement.

To use a precompiled SQL statement, use a SQL parameter.

Message codes, exception messages, and limitations

Connector	Message code	Exception message	Limitations
Messages and limitations of the DB2 connector
Messages and limitations of the MySQL connector
Messages and limitations of the Oracle connector
Messages and limitations of the PostgreSQL connector
Messages and limitations of the SQL Server connector
Messages and limitations of the JDBC connector
Messages and limitations of the Amazon Aurora for MySQL connector
Messages and limitations of the Amazon RDS for MySQL connector
Messages and limitations of the Amazon RDS for Oracle connector
Messages and limitations of the Amazon RDS for PostgreSQL connector
Messages and limitations of the Amazon RDS for SQL Server connector
Messages and limitations of the Azure SQL Database connector
Messages and limitations of the Snowflake connector