Execute Select SQL

Operation Name

Execute Select SQL

Function Overview

Read data using SQL statements.

Data Model

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.  
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.
  • Can be specified with SELECT statement.
  • Executes executeQuery() method of java.sql.Statement Class .Please contact database vendor about executable SQL statements other than SELECT statement.
  • Available to input characters up to "2147483647". However, in case there is a pre-set limitation in the database or the driver, the limitation would be the upper limit.
Schema definition Required - Shows the schema definition of the SQL statement specified in [SQL statement].
  • Column name shown in [Schema definition] is displayed in schema of Mapper.
Schema definition / Column name Required Not available Shows the column name of the SQL statement specified in [SQL statement].  
Schema definition / Type Required Not available Select [Column name] type.
  • 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 Starts the SQL wizard.
For instructions, refer to "Search SQL wizard".
  • SQL statements specified in [SQL statement] cannot be edited with wizard.
Extraction test Executes specified SQL statement.Displays the number of nodes of the retrieved result.
  • Cannot be executed if [SQL statement] is not specified.
  • If script variable is specified in [SQL statement], execution is enebled by entering values to the variable.
    • Executed with empty character, if no value is entered.
    • Entered value does not affect to the script execution.
Execute SQL statement to set schema Gets schema information from the SQL statements specified in [SQL statement] and set to [Schema definition].
  • Cannot be executed if [SQL statement] is not specified.
  • If script variable is specified in [SQL statement], execution is enebled by entering values to the variable.
    • Executed with empty character, if no value is entered.
    • Entered value does not affect to the script execution.
Read settings
Item name Required/Optional Use of variables Description Remarks
Fetch size Optional Available Specify JDBC fetch size.
  • Range of input value is "0~2147483647".
  • In this property, input value is specified as JDBC setFetchSize()method's parameter.
  • Generally fetch size means the number of rows fetched at once, it might mean something different depending on databases or JDBC drivers you use.
  • If omitted or enter other than numerical value, it follows process when fetch size of JDBC driver is not specified.
  • Following adapter do not have this property.
    • Access adapter
    • ODBC adapter
    • SQL Server adapter
    • Amazon RDS for SQL Server adapter
    • Azure SQL database adapter
Data processing method
Item name Required / Optional Use of Variables Description Remarks
Mass data processing Required Not available Select a data processing method.
  • [Use script settings]: (default)
    Apply mass data processing settings of script property to adapter.
  • [Disable]:
    Mass data processing is not performed.
  • [Enable]:
    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 adapter.
The description will be reflected in the specifications.		  

Schema

Input Schema

None.

Output 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.

Loading schema in Mapper

Schema is loaded automatically.
For any details, please refer to the 'Editing Schema'

Mass data processing

Mass data processing 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 Stores the count of the data.
  • The value defaults to null.
  • Null when using Parallel Stream Processing.
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 Stores the category of message code corresponding to the occurred error.
  • The value defaults to null.
message_code Stores the code of message code corresponding to the occurred error.
  • The value defaults to null.
message_level Stores the severity of message code corresponding to the occurred error.
  • The value defaults to null.
error_type Stores the error type of occurred 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 of occurred error.
  • The value defaults to null.
  • The message may vary depending on the DataSpider Servista version.
error_trace Stores the trace information of occurred error.
  • The value defaults to null.
  • The message may vary depending on the DataSpider Servista version or the client application used.

Specification Limits

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

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