Execute Select SQL

Operation Name

Execute Select SQL

Function Overview

Read 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.
  • When a SQL parameter is not included in [SQL statement], executeQuery() method of java.sql.Statement class will be executed, and when a SQL parameter is not 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, 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.
Input 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 [Input schema definition] will be displayed in Mapper schema.
Input schema definition/Column name Required Not available Set the SQL parameter name.
  • Not displayed when a SQL parameter is not used in [SQL statement].
Input 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.
Output schema definition Required - The schema definition of the SQL statement specified in [SQL statement] is displayed.
  • The column name displayed in [Output schema definition] will be displayed in input schema of Mapper.
Output schema definition/Column name Required Not available A column name specified in [SQL statement] is displayed.  
Output schema definition/Type Required Not available The type of [Column name] is displayed.
  • 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 “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 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.
Extraction test Execute the specified SQL statement. The number of retrieved records will be displayed.
  • When [SQL statement] is not specified, it cannot be executed.
  • When script variables or SQL parameters are specified in [SQL Statement], you can execute it entering values to them.
    • If values are not entered, it will be executed with empty strings.
    • The entered values have no effect on script execution.
  • When a SQL parameter is specified in [SQL statement], it cannot be executed.
Execute SQL statement and set output schema Get output schema information with the SQL statement specified in [SQL statement] and set it in [Output schema definition].
  • When [SQL statement] is not specified, it cannot be executed.
  • When script variables or SQL parameters are specified in [SQL Statement], you can execute it entering values to them.
    • If values are not entered, it will be executed with empty strings.
    • The entered values have no effect on script execution.
  • When a SQL parameter is specified in [SQL statement], it cannot 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 0-2147483647.
  • The value input in this property will be specified at an argument for JDBC setFetchSize() method.
  • Although the number of rows to get from the database at once is generally set for fetch size, the behavior might be different depending on database or using JDBC driver.
  • When omitted or non-numeric value is entered, the behavior of the JDBC driver when fetch size is not set will be applied.
  • There is not this property in the following adapters.
    • Access Adapter
    • ODBC Adapter
    • SQL Server Adapter
    • Amazon RDS for SQL Server Adapter
    • Azure SQL Database Adapter
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
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 schema definition] settings.
Refer to “Schema of table model type” for more details.

Output schema

The number of columns varies depending on the [Output schema definition] 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.

Transaction

Transaction is supported.

Mass Data Processing

Mass Data Processing 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 retrieved data is stored.
  • Default value is null.
  • 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.

SQL Parameter

SQL parameter can be used in 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 parameter

SQL parameter is described as “?{name}”. You can specify “name” arbitrarily. 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 using Mapper.

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 [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

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