Write(Update/Insert)

Operation Name

Function overview

Write input data to Dr.Sum EA table.
Or creates table based on CSV file or the structure information of database.

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. 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".
Table name	Required	Available	Select or enter the name of the table to write.	View, multi-table, link table are not shown.
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 is updated.Inserts rows that does not match the key value. [Not Checked]: (default) Insert all rows	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 .
Delete record	Optional	Not available	Select whether you want to delete the record before writing data. [Checked]: Delete the record. [Not Checked]: (default) Do not delete the record.	Ignored when table specified with [Table name] do not exist.
Create table	Optional	Not available	Select whether to create table based on the structure information of [Schema definition] with the name specified in [Table name]. [Checked]: Create a table. [Not Checked]: (default) Do not create a table.	Re-created table do not inherit the additional information of table like permission or composite keys. Disabled when distributor is selected in [Table name] is.
Re-create table when exists	Optional	Not available	When table with the same name as [Table name] exists, select whether to re-create the table. [Checked]: If table with the same name exists, drop it can re-create it. [Not Checked]: (default) Do not re-create the table if it already exists.	Enabled if [Create table] is checked. Disabled when distributor is selected in [Table name] is.
Schema definition	Required	-	Specify the schema definition of database.	Of all the columns shown in [Schema definition], the column name with [Target] or [Key] checked is displayed in the 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] Refer to "About Insert / Update" for keys .
Schema definition / Target	Optional	Not available	Select whether the table column specified in [Table name] is included in Target or not. [Checked]: (default) Includes in update criteria. [Not Checked]:: Do not include in update criteria.	The checked column name is displayed in the Mapper schema. Checking a checkbox in the header enables bulk operation. There needs to be at least one checked column. For more details, refer to "Target column".
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.	The checked column name is displayed in the 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 keys.
Schema definition / Column name	Required	Not available	Shows the column name of the table name 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: Dr.Sum EA specific data type. Internal data type Data type used internally in DataSpider. Refer to "here" for details .
Schema definition / Allow null	Required	Not available	Select whether to allow Null for the specified column in table creation.	Enabled if [Create table] is checked.
Schema Definition / Precision	Required	Available	Enter the digits of the precision for the column specified when creating the table.	The default value is "0". Enabled if [Create table] is checked.
Schema Definition / Decimal places	Required	Available	Enter the digits of the decimal places for the column specified when creating the table.	The default value is "0". Enabled if [Create table] is checked.

Property Action

Item name

Description

Remarks

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.
View, multi-table and link table cannot be selected.
If a distributor is selected, the table contents will not be displayed.
Distributors are not shown in [Table name].

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]

Get table structure information

Other database or CSV file to get information from a table structure and set it as the schema definition.

Refer to "Get table structure information " for more information .

CSV files only support the file encoded with Windows-31J.
To get table structure information from other databases, it is necessary that the global resource to connect to the target database is created.

Global resources of the following adapters can be specified.

Adapter Type
Access Adapter
DB2 Adapter
MySQL Adapter
Oracle Adapter
PostgreSQL Adapter
SQL Server Adapter
JDBC Adapter
ODBC Adapter
Dr.Sum EA 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

Order of global resources is undefined.
Disabled when distributor is selected in [Table name] is.

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.

See "Edit Schema"for any details .

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	The number of records written input.	The value defaults to null. The total of insert_count and update_count.
insert_count	The number of records inserted into the input.	The value defaults to null.
update_count	The number of records updated input.	The value defaults to null.
update_record_count	The number of records actually updated.	The value defaults to null.
error_code	When an error occurs, Dr.SumEA 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.

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.

About Insert/Update

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

Target column

Inserting behavior

At insertion, the column with [Schema definition / Target] checked is used for an insert target in an INSERT sentence.

Insert null to columns which are not included in Target.

An error will occur at execution if columns with the NOT NULL restriction are not included in Target.

Example:

Schema definition

Check [Target] Column name

Available COLUMN1

None COLUMN2
Column restriction / Settings

COLUMN1 COLUMN2

None NULL acceptance restriction
Input data

COLUMN1

data
INSERT sentence to be run

INSERT INTO <TABLE NAME> (COLUMN1) VALUES ('data')
Processed data

COLUMN1 COLUMN2

data null

Check [Target]	Column name
Available	COLUMN1
None	COLUMN2

COLUMN1	COLUMN2
None	NULL acceptance restriction

COLUMN1
data

COLUMN1	COLUMN2
data	null

Updating behavior

When you update, a column with [Schema definition / Target] checked is used for a target column in an UPDATE sentence.
The column value which is not included in Target will pertain, without doing any update.

Example:

Schema definition

Check [Target] Check [Key] Column name

Available None COLUMN1

None Available COLUMN2

None None COLUMN3
Data before the process

COLUMN1 COLUMN2 COLUMN3

data1 data2 data3
Input data

COLUMN1 COLUMN2

updateValue data2
UPDATE sentence to be run

UPDATE <TABLE NAME> SET COLUMN1 = 'updateValue' WHERE COLUMN2 = 'data2'
Processed data

COLUMN1 COLUMN2 COLUMN3

updateValue data2 data3

Check [Target]	Check [Key]	Column name
Available	None	COLUMN1
None	Available	COLUMN2
None	None	COLUMN3

COLUMN1	COLUMN2	COLUMN3
data1	data2	data3

COLUMN1	COLUMN2
updateValue	data2

COLUMN1	COLUMN2	COLUMN3
updateValue	data2	data3

Get table structure information

Get the column names from CSV file or, how to get from another database table structure, you can set the schema definition.

Reading information from a database structure

How to read the table structure using the global resources of the database is as follows.

From property to [Get table structure information] when you click, and select the [Table Structure Information] and then initiates a dialogue.

Select [Read structure information from database], click [Next].

At [Global Resource Name] Select Global Resources of database that are already created.

Select or enter the target table to read schema information is selected in the [Table name] list.
In the [Table name] list, tables with the number of the restriction number set in the list setting of global resources.
Variables such as environment variables cannot be used for [Table name].

If you specify an activated table, the schema information of the table is displayed in the table properties.

Click [Finish], and table information structure is set to [Schema definition].You can edit the schema definition.

Reading from a CSV file column names

How to read the CSV file column names in the schema definition is as follows.

From property to [Get table structure information] when you click, and select the [Table Structure Information] and then initiates a dialogue

Select [Read structure information from CSV file], click [Next].

CSV column names to read the file. In this example, the following contents: the CSV file.

PRODUCTNUMBER,PRODUCTNAME,PRICE

Click [Finish], and table information structure is set to [Schema definition].
As the default value, [Type] is [VARCHAR] is set, [Allow null] is enabled. [Key], [Precision], [Decimal places] is not set.You can edit the schema definition.
You can edit the schema definition.

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

Notes

About specifications, please refer to the following page.