Execute Select SQL

Operation Name

Execute Select SQL

Function overview

Reads Dr.Sum 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.
Refer to "Global Resource Properties" for global resource settings .
  • [Add]:
    Adds new global resource.
  • [Edit list]:
    Global resource settings can be managed from "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.
  • Can be specified with SELECT statement.
Sort encoding Required Not available Select the sorting order of data when output by specifying ORDER BY.
  • [UTF-8]:(default)
    Sorts in UTF-8.
  • [Shift_JIS]:
    Sorts in Shift_JIS.
  • Select [Shift_JIS] when sorting with 2.x compatible.
Input 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 [Input schema definition] is displayed in output schema of Mapper.
Input schema definition / Column name Required Not available Set the SQL parameter name.
  • It is not displayed when [SQL statement] does not use SQL parameter.
Input 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:
      Dr.Sum specific data type.
    • Internal data type
      Data type used internally in DataSpider.
      Refer to "here" for details .
Output schema definition Required - Shows the schema definition of the SQL statement specified in [SQL statement].
  • Column name shown in [Output schema definition] is displayed in input schema of Mapper.
Output schema Definition / Column name Required Not available Shows the column name of the SQL statement specified in [SQL statement].  
Output schema definition / Type Required Not available Select [Column name] type.
  • Displayed in the format of "Native type (Internal data type)" .
    • Native type:
      Dr.Sum specific data type.
    • Internal data type
      Data type used internally in DataSpider.
      Refer to "here" for details .
Property Action
Item name Description Remarks
Refresh connection Refresh the connection to the newest status.  
SQL Wizard Start the SQL Wizard.
For instructions, refer to "SQL Wizard-Help ".
  • SQL wizard can create a new SQL statement and edit SQL statement generated by the SQL wizard.
    • SQL wizard can not edit SQL statement that has been specified in [SQL statement] without using the SQL wizard.
    • If the SQL statement created by the SQL wizard has been modified manually, it cannot be edited in the wizard. Select whether to edit the SQL statement before modification, or to create a new SQL statement in a dialog.
  • Distributor will not be displayed.
Show table information Displays the structure, content and related information of tables of the database specified in [Destination].
Refer to "Table Information"for table information .
  • Enabled when [Destination] is selected.
  • Distributor cannot be selected.
Execute SQL statement to set output schema Get output schema information from the SQL statements specified in [SQL statement] and set to [Output schema definition].
  • If [SQL statement] is not specified, it cannot be executed.
  • If script variable or SQL parameter is specified in [SQL statement], execution is enabled by entering values to them.
    • If you do not input value, it will be executed in null character.
    • input value will not effect an execution of script.
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

Depending on the settings for the [Input schema definition] the number of columns may vary.
See "Schema of Table Model" for details regarding schema structure.

Output Schema

Depending on the settings for the [Output 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.
See "Edit Schema"for any details .

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 Returns the number of record read.
  • The value defaults to null.
  • Null when using Parallel Stream Processing and using SQL parameters.
error_code When an error occurs, Dr.Sum returns the error status in decimal value. If no error occurred, returns null.
  • The value defaults to null.
  • The message may vary depending on DataSpider Servista version.
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 DataSpider Servista version.
error_message Stores the error message for the occured error.
  • The value defaults to null.
  • The message may vary depending on DataSpider Servista version.
error_trace Stores stack trace of the occurred error.
  • The value defaults to null.
  • The message may vary depending on DataSpider Servista version or the client application used.

SQL parameters

In Execute Select SQL, SQL parameters can be used.
Uses SQL parameters to use pre-compiled SQL statements.Performance can be improved by using pre-compiled SQL statements when executing the same SQL statement for multiple times.

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 "select * from test_table where col1 = ?{col1} and col2 = ?{col2} and col3 = ?{col3}", the 3 SQL parameters (col1, col2, col3) can be changed dynamically. If sets this Execute Select 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.

Specification Limits

About specifications, please refer to the following page.

Main exceptions

Exception name Cause 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. Check the global resource specified in [Destination].
InvalidPropertyConfigurationException [SQL statement] is not specified. Please specify [SQL statement].

Notes

About specifications, please refer to the following page.