Write Table

Operation Name

Write Table

Function Overview

Writes input data to the table.

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	Required	Not available	Select the component 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".
Table name	Required	Available	Select or enter the name of the table to write.	The maximum umber of tables listed is determined by Global Resource settings. When schema name and table name contains ".", it can be properly handled by enclosing the name of the schema and table by quotation marks. For example, when quotes is ", the schema name is A.B and the table name is C.D, the correct way to quote will be: "A.B"."C.D" .
Enclose table name with quotation marks	Optional	Not available	Select whether enclosed the specified table name with quotation marks. [Checked]: Enclose table name displayed in [Table name] in quotation marks. [Not Checked]: (default) Do not enclose table name displayed in [Table name] in quotation marks. Or if the table name contains characters such as spaces, it must be enclosed in quotation marks. For characters that require quotes, please contact the database vendor.	Quotation marks set with each database adapter are as follows: Oracle, DB2, PostgreSQL, Amazon RDS for Oracle, Amazon RDS for PostgreSQL: """" (double quotes) Access, SQL Server, Amazon RDS for SQL Server, Azure SQL Database: "[]"(square brackets) MySQL, Amazon RDS for MySQL: "``" (back-quote) JDBC adapter and ODBC adapter do not have this property. When executing, uses directly the table name as entered in [Table name]. When enclosing table name entered in [Table name] with quotation marks, query will also be issued with quoted table name during execution. When not enclosing table name entered in [Table name] with quotation marks, the validity of this property will be check first during execution, when it is valid, the query will also be issued with quoted table name. Environment variables dereferenced in [Table name] are expanded when this property is enabled.
Update rows that match the value of the key	Optional	Not available	Select whether to update the selected column name as key, and select whether to insert all the rows. [Checked]: Updates rows that match the key value.Inserts rows that does not match the key value. [Not Checked]: (default) Insert all rows	When [Update rows that matches the value of the key] is checked, if [Key] is not specified in [Schema definition], error occurs. Refer to "About Insert/Update" for details.
Schema definition	Required	-	Shows the schema definition specified in [Table name].	Columns defined in the [Schema definition] with their names that are either [Target] or [Key] checked are displayed in Mapper schema. Schema definition cannot be viewed in the following cases. If do not have read permissions to the specified table If the specified table does not exist If [Table name] is specified by variable If [Table name] is not entered If error occurs in [Table name]
Schema definition/Target	Optional	Not available	Select whether to include the columns defined in the table specified in the [Table name]. [Checked]:(default) Columns are included as write targets. [Not Checked]: Columns are not included.	Column names with checks are reflected in Mapper schema. Click the checkbox that heads all individual columns to have them checked all at once. At least one of the columns needs to be checked. See About columns targeted for any details.
Schema definition / Key	Required	Not available	Select the key included in the update criteria. [Checked]: Includes in update criteria. [Not Checked]: (default) Do not include in update criteria.	Enabled when [Update rows that match the value of the key] is checked. Column names with checks are reflected in Mapper schema. When [Update rows that match the value of the key] is checked, if [Key] is not specified in [Schema definition], error occurs. Refer to "About Insert/Update" for details.
Schema definition / Column name	Required	Not available	Shows the column name of the table specified in [Table name].
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. Table name specified in the [Table name] is displayed initially.
Update schema definition	Updates the schema definition specified in [Table name].	In the following cases it can not be executed. If the specified table does not exist If [Table name] is specified by variable If [Table name] is not entered If error occurs in [Table name]

Write settings

Item name	Required/Optional	Use of variables	Description	Remarks
Insert Mode	Optional	Not available	Select the writing method in case of inserting all matters of the data. [Single row insert]:(Default) Executes SQL statement per one row in the input data. [Batch insert]: Performs batch execution of SQL statement, per number of rows specified in [Batch size].	Following adapter do not have this property. Access adapter DB2 adapter MySQL adapter Oracle adapter PostgreSQL adapter SQL Server adapter Amazon RDS for MySQL adapter Amazon RDS for Oracle adapter Amazon RDS for PostgreSQL adapter Amazon RDS for SQL Server adapter Azure SQL database adapter If the adapter does not have this property, it will be executed as [Batch insert]. if [Update row that match key value] is checked, it will not be displayed. If type that does not support batch execution is included in the type of row to write, it will be executed as [Single row insert]. For type that does not support batch execution, refer to "Data base adapter limitations". Batch execution is a function which data for multiple row is transmitted to data base all at once and execute SQL statement.
Update Mode	Optional	Not available	Select the writing method in case of updating data. [Update before insert]:(Default) Executes UPDATE statement per row, and if row that matches key value does not exist, executes INSERT statement. [Insert before update]: Executes INSERT statement per row, and if row that matches key value exists, executes UPDATE statement. [Batch update before insert]: Do batch execution of UPDATE statement per the number specified in [Batch size], and if row that matches key value does not exist, do batch execution of INSERT statement.	If [Update row that matches key value] is checked, then displayed. For details on difference between [Update before insert] and [Insert before update], please refer to 'Difference between "Update before insert" and "Insert before update"'. Depending on databases, there might be unavailable update mode. For details on unavailable update mode, please refer to "Database Adapter Limitations" If type that does not support batch execution is included in the type of row to write, executes as [Update insert]. For type that does not support batch execution, refer to "Data base adapter limitations". If [Insert update] is specified, it is necessary to set up uniqueness constraint for selected column in [Schema definition/key]. If uniqueness constraint isn't set up, the result can be differenct from that of execution with [Update before insert] or [Batch update before insert] specified.
Batch size	Optional	Available	Specify the number of batch executed SQL statement.	It will be valid if [Batch insert] at [Insert mode] or [Batch update before insert] at [Update mode] has been selected. Default value is "1000" and if ommitted, it will be set up as "1000". Range of value that can be entered is "1~2147483647".

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 [Schema definition] the number of columns may vary.

See "Schema of Table Model" for details regarding schema structure.

Output Schema

None.

Loading schema in Mapper

Schema is loaded automatically.

For any details, please refer to the 'Editing Schema'

Transaction

Transaction 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 records written.	The value defaults to null.
insert_count	The number of records inserted.	The value defaults to null.
update_count	The number of records updated.	The value defaults to null.
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	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 the DataSpider Servista version.
error_message	Stores the error message for the occured error.	The value defaults to null. The message may vary depending on the DataSpider Servista version.
error_trace	Stores stack trace of the occurred error.	The value defaults to null. The message may vary depending on the DataSpider Servista version or the client application used.

About Insert/Update

When updating a table with the writing process, check [Update rows that match the value of the key] and select the [Column name] as key. Update the rows that match the selected column as the key and insert rows that do not match.Column name selected as key is specified in the WHERE clause of UPDATE statement.

Insert/Update

Multiple keys can be specified as update criteria.Rows that match all the columns selected as keys are updated.

About columns targeted

Insert operation

Columns whose [Schema definition/Target] are checked are the columns to which values are inserted in the INSERT statement.

Columns not to be included in the statement will have their default values set.

Columns with NOT NULL constrains need to be included in the statement. Error will be raised otherwise.

Example:

Schema definition

[Target] check Column name

checked COLUMN1

not checked COLUMN2

not checked COLUMN3
Column constrains/settings

COLUMN1 COLUMN2 COLUMN3

not checked Has default value (default value:defaultValue) NULL allowed constraint
Input data

COLUMN1

data
SQL INSERT statement which will be executed

INSERT INTO <Table name> (COLUMN1) VALUES ('data')
The values of the columns after the SQL INSERT statement has been executed

COLUMN1 COLUMN2 COLUMN3

data defaultValue null

[Target] check	Column name
checked	COLUMN1
not checked	COLUMN2
not checked	COLUMN3

COLUMN1	COLUMN2	COLUMN3
not checked	Has default value (default value:defaultValue)	NULL allowed constraint

COLUMN1
data

COLUMN1	COLUMN2	COLUMN3
data	defaultValue	null

Updates of the columns

Columns with their [Schema definition/Target] checked are updated in the UPDATE statement.
Columns not included will be unaffected.

Example:

Schema definition

[Target] check [Key] check Column name

checked not checked COLUMN1

not checked checked COLUMN2

not checked not checked COLUMN3
Data before operation

COLUMN1 COLUMN2 COLUMN3

data1 data2 data3
Input data

COLUMN1 COLUMN2

updateValue data2
Update statement

UPDATE <table name> SET COLUMN1 = 'updateValue' WHERE COLUMN2 = 'data2'
Data after operation:

COLUMN1 COLUMN2 COLUMN3

updateValue data2 data3

[Target] check	[Key] check	Column name
checked	not checked	COLUMN1
not checked	checked	COLUMN2
not checked	not checked	COLUMN3

COLUMN1	COLUMN2	COLUMN3
data1	data2	data3

COLUMN1	COLUMN2
updateValue	data2

COLUMN1	COLUMN2	COLUMN3
updateValue	data2	data3

Difference between "Update before insert" and "Insert before update"

If you run the operation selecting [Update before insert] in [Update mode], it executes UPDATE statement first and then INSERT statement for each row.
Therefore, if there are more records to be updated then inserted in input data, you'd rather select [Update before insert] than [Insert before update] in terms of efficiency.
If you run the operation selecting [Insert before update] in [Update mode], it executes INSERT statement first and then UPDATE statement for each row.
Therefore, if there are more records to be inserted than updated in input data, you'd rather select [Insert before update] than [Update before insert] in terms of efficiency.

About the error log

If error occurred during batch execution, information regarding the error will be output as error log inside a dump file.

Destination

$DATASPIDER_HOME/server/logs/<data directory>/execution/<execution ID directory>/dump.xml

Output content

The following information will be output to FailureResults element.

key attribute value of result element	Description
dump_error_message	Outputs dump information when the error occurred. [SQL]: Executed SQL statement when an error occurs. [Error Records Range]: The starting line number and ending line number where the error was in the input data. [Batch Insert Count]: The number of data that succeeded batch execution. [Batch Size]: Batch size. [Error Records]: Output data used in batch execution when error occured. Output Error Records Range for input data.
error_code	The error code when SQL exception (SQLException) occurred.

key attribute value of result element

Description

dump_error_message

Outputs dump information when the error occurred.

[SQL]:
Executed SQL statement when an error occurs.
[Error Records Range]:
The starting line number and ending line number where the error was in the input data.
[Batch Insert Count]:
The number of data that succeeded batch execution.
[Batch Size]:
Batch size.
[Error Records]:
Output data used in batch execution when error occured. Output Error Records Range for input data.

error_code

The error code when SQL exception (SQLException) occurred.

Depending on database or data type of the writing destination, batch execution will not occur.
Refer to " Write table limitations "in "DB adapter Limitations".
Error log is output when error occurs when executing the query.
When error occurs in the Execute SQL Update in the Monitor Exception, the dump file of the processing is not outputted.

Specification Limits

Depending on the number of tables in the database, it may take some time to open the property settings dialog, if the upper limit for displaying tables is not properly configured.

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

Main exceptions

Exception name	Causes	Solution
InputDataNotFoundException	[Input data] is not specified.	Specify the [Input data],or draw the data flow.
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.	Please check the global resource specified in [Destination]
InvalidPropertyConfigurationException	[Table name] is not set.	Specify [Table name]
InvalidPropertyConfigurationException Columns that are to be updated are note selected.	[Target] of [Schema definition] is not selected.	Select at least one [Target] of [Schema definition].
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

To a schema dealt as byte[] type you can input the data of Base64 format or binary format.

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