Read Table

Operation Name

Read Table

Function Overview

Read data stored in a table.

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.  
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.
  
Table name Required Available Select or enter a table name to be read.
  • Tables of the number specified in the global resource's list settings are displayed.
  • When a schema or table name in the database includes “.”, you can handle the name properly by enclosing it with quotation marks.
    For example, when the quotation mark is ["] and the schema and table name are “A.B” and “C.D” respectively, the proper way to enclose is ["A.B"."C.D"].
Enclose table name with quotation marks Optional Not available Select whether or not to enclose the specified table name with quotation marks.
  • [Checked]:
    Table name displayed in [Table name] is enclosed with quotation marks.
  • [Not checked]: (default)
    Table name displayed in [Table name] is not enclosed with quotation marks.
If the table name includes characters like space, you need to enclose the table name with quotation marks. For details on characters that need to be enclosed with quotation marks, inquire the database vendor.
  • Quotation mark which is set in each database adapter is as follows.
    • Oracle, DB2, PostgreSQL, Amazon RDS for Oracle, Amazon RDS for PostgreSQL: [""] (double quotations)
    • Access, SQL Server, Amazon RDS for SQL Server, Azure SQL database: “[]” (box brackets)
    • MySQL, Amazon RDS for MySQL, Amazon Aurora for MySQL: “``” (backquote)
  • There isn't this property in JDBC Adapter, ODBC Adapter and Thunderbus DB Agent Adapter.
  • At the time of execution, the table name input in [Table name] is applied as is.
    If the table name entered in [Table name] is enclosed with quotation marks, a query will be issued with the table name enclosed with quotation marks also at the time of execution.
    If the table name entered in [Table Name] is not enclosed with quotation marks, this property will be checked at the time of execution, and if this property is enabled, a query will be issued with the table name enclosed with quotation marks.
  • When environment variables are used in [Table name], the value is deployed when enabling/disabling this property.
Schema definition Required - The schema definition of the table specified in [Table name] is displayed.
  • Among the columns displayed in [Schema definition], the column names for which [Target] is checked will be displayed in Mapper schema.
  • Schema definition cannot be displayed in the following cases.
    • When the user do not have read permission to the specified table
    • When the specified table does not exist
    • When a variable is specified in [Table name]
    • When [Table name] is not entered
    • When an error has occurred in [Table name]
Schema definition/Target Optional Not available Select whether to include each column of the table specified in [Table Name] in the target or not.
  • [Checked]:(default)
    Include in target.
  • [Not checked]:
    Do not include in target.
  • The checked column names will be displayed in Mapper schema.
  • Batch operation can be performed by clicking the checkbox displayed in the header.
  • One or more columns should be checked.
Schema definition/Column name Required Not available Column name in the table specified in [Table name] is displayed.  
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
Get table list Get the table name from the database and set to [Table name].
  • Enabled when [Destination] is selected.
  • This property will be displayed only in Thunderbus DB Agent Adapter.
Launch table browser Launch the table browser.
For details on how to operate, refer to “Table Browser“.
  • Enabled when [Destination] is selected.
  • Enabled when [Get table list] is succeeded only in Thunderbus DB Agent Adapter.
  • The table specified in [Table name] is displayed initially.
Extraction test Issue a SELECT statement for the specified table. The number of retrieved records will be displayed.
  • It cannot be executed in the following cases.
    • When the user do not have read permission to the specified table
    • When the specified table does not exist
    • When a variable is specified in [Table name]
    • When [Table name] is not entered
    • When an error has occurred in [Table name]
Update schema definition Update the schema definition of the table specified in [Table Name].
  • It cannot be executed in the following cases.
    • When the specified table does not exist
    • When a variable is specified in [Table name]
    • When [Table name] is not entered
    • When an error has occurred in [Table name]
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

None.

Output schema

The number of columns varies depending on the [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 read 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.

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 [Table name] is not set. Specify [Table name].
InvalidPropertyConfigurationException
Target column is not selected.		 [Target] for [Schema definition] is not selected. Select one or more [Target] for [Schema definition].
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”.