Execute Select SQL

Operation name

Execute Select SQL

Function overview

Reads 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. When a SQL parameter isn't included in SQL statement, executeQuery() method of java.sql.Statement class will be executed, and when a SQL parameter is included, executeQuery() method of java.sql.PreparedStatement class will be executed. The SELECT statement that returns java.sql.ResultSet class can be specified. For more details on the executable SQL statements other than SELECT statement, 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.
Input 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 Input schema definition will be displayed in the output schema of Mapper.
Input schema definition/Column name	Required	Not available	Set the SQL parameter name.	Not displayed when a SQL parameter isn't used in SQL statement.
Input 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.
Output schema definition	Required	-	The schema definition of the SQL statement specified in SQL statement is displayed.	The column names displayed in Output schema definition will be displayed in the input schema of Mapper.
Output schema definition/Column name	Required	Not available	The column name specified in SQL statement is displayed.
Output schema definition/Type	Required	Not available	The type of Column name is displayed.	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 Select 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.
Extraction test	Execute the specified SQL statement. The number of retrieved records will be displayed as a result.	When SQL statement isn't specified, it can't be executed. When a SQL parameter is specified in SQL statement, it can't be executed.
Execute SQL statement and set output schema	Get output schema information from the SQL statement specified in SQL statement and set it in Output schema definition.	When SQL statement isn't specified, it can't be executed. When a SQL parameter is specified in SQL statement, it can't be executed.

Read settings

Item name	Required/Optional	Use of variables	Description	Remarks
Fetch size	Optional	Available	Specify the JDBC fetch size.	Allowed values are from 0 to 2147483647. The value input in this property will be specified as an argument for JDBC setFetchSize() method. Generally, the number of rows to get from the database at once is specified for fetch size, but the behavior might be different depending on the database or the JDBC driver used. When omitted or when a non-numeric value is entered, the behavior of the JDBC driver when the fetch size isn't set will be applied. Note The following connectors don't have this property: SQL Server Connector Amazon RDS for SQL Server Connector Azure SQL Database Connector
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 setQueryTimeout() 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

Fetch size

Optional

Available

Specify the JDBC fetch size.

Allowed values are from 0 to 2147483647.
The value input in this property will be specified as an argument for JDBC setFetchSize() method.
Generally, the number of rows to get from the database at once is specified for fetch size, but the behavior might be different depending on the database or the JDBC driver used.
When omitted or when a non-numeric value is entered, the behavior of the JDBC driver when the fetch size isn't set will be applied.

Note

The following connectors don't have this property:

SQL Server Connector
Amazon RDS for SQL Server Connector
Azure SQL Database Connector

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 setQueryTimeout() 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.

Data processing method

Item name	Required/Optional	Use of variables	Description	Remarks
Data processing method	Required	Not available	Select a data processing method. Apply script settings (Default) The setting for mass data processing in the script properties is applied to the connector. Do not perform mass data processing Mass data processing isn't performed. Perform mass data processing Mass data processing is performed.

Item name

Required/Optional

Use of variables

Description

Remarks

Data processing method

Required

Not available

Select a data processing method.

Apply script settings

(Default)

The setting for mass data processing in the script properties is applied to the connector.

Do not perform mass data processing

Mass data processing isn't performed.

Perform mass data processing

Mass data processing is performed.

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 Input schema definition settings.

= Remarks =

For schema structure, refer to Table model type .

Output schema

The number of columns varies depending on the Output schema definition settings.

= Remarks =

For schema structure, refer to Table model type .

Loading schema in Mapper

The schema is loaded automatically.

= Remarks =

For details, refer to Edit Schema.

Transaction

Transaction is supported.

Mass data processing

Mass data processing 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 retrieved data records is stored.	The default value is null. Note Value isn't stored when using PSP.
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 Select SQL operation.

To use a precompiled SQL statement, use SQL parameters. If a precompiled SQL statement is used when executing the same SQL statement for multiple times, the performance will be improved.

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 "select * from test_table where col1 = ?{col1} and col2 = ?{col2} and col3 = ?{col3}" is described in SQL statement, three SQL parameters (col1, col2, and col3) can be assigned dynamically.

When this Execute Select 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 Athena 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