Write Table

Operation Name

Write Table

Function Overview

Insert, Update, Update/Insert, Delete data in a table Microsoft Dynamics AX.

Data Model

Data model of this type of component is table model type.

Properties

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]:
    Add new global resource.
  • [Edit list]:
    Global resource settings can be edited by "Edit resource list".
  
Table Name Required Available Enter the name of the Microsoft Dynamics AX table to write.  
Action Required Not available Select one action.
  • [Insert]:(default)
    Insert input data for all setting columns.
  • [Update]:
    Update input data of target columns when velues of key columns match the records in table.
  • [Update/Insert]:
    Update input data of target columns when velues of key columns match the records in table.
    If not match, insert input data for all setting columns.
  • [Delete]:
    Delete records when velues of input data for all setting columns match the records in table.
  • When [insert] in [Action] is selected, the checkbox of [Input/Key] will be disable. All setting columns will be the target columns for insert.
  • When [Update] or [Update/Insert] in [Action] is selected, the checkbox of [Input/Key] will be able to be checked. And if none of the checkboxes is checked, error will occur.
    • For more information, please refer to "About update".
  • When [Delete] in [Action] is selected, the checkbox of [Input/Key] will be disable. All columns will be used for the delete condition.
Commit Type Required Not available Select one commit type.
  • [All records will be committed or rollbacked]:(default)
    If any error occurs, all records will be rollbacked.
    Otherwise, all records will be committed.
  • [Commit non-error records(Stop at the error line If any error occured)]:
    If any error occurs, operation will stop at the error line, and throw an exception for the error. Previous data will be committed.
    Otherwise, all records will be committed.
  • [Commit non-error records(Skip error lines If any error occured)]:
    Operation will not stop though any error occurs. Number of error lines will be counted, and error data will be outputed.
    Otherwise, all records will be committed.
  • When [All records will be committed or rollbacked] or [Commit non-error records(Stop at the error line If any error occured)] is selected, the operation will be end successfully or forced to end with exception.
  • When [Commit non-error record(Skip error lines If any error occured)] is selected, the operation always will be end successfully.
Input Required - Set the schema definition specified in [Table Name].  
Input/Column Name Required Available Enter the column name in the Microsoft Dynamics AX table.

You can set the first column of the file specified in property action of [Read column name from file] as the column name. 		 
Input/Data Type Required Not available Select a data type for the column.

You can set the second column of the file specified in property action of [Read column name from file] as the data type.
  • Displayed in the format of "Native type (Internal data type)" .
    • Native type:
      The data type of Microsoft Dynamics AX.
    • Internal data type:
      Data type used internally in DataSpider(specific data types of Java).
  • Displayed in the format of "Native type" when Native type is same as Internal data type.
Input/Key Required Not available Select the column as key for the update condition.
  • [Checked]:
    Check columns that will be used for the update condition.
  • [Not Checked]: (default)
    Remove the check of columns that will be the update target.
  • If a column needs to be used for both the update condition and the update target, the setting should be divided into two lines.
    For more information, please refer to Notes at the bottom.
Property Action
Item name Description Remarks
Read column name from file Select the file in the file Selectr and set the first column of the file as the column name.
Set the second column of the file as the data type.
  • Six characters used as delmiter are "half comma(,)", "tab", "space ( )", "semicolon (;)", "slash (/)" and "Vertical bar (|)".
  • If the second column does not match the spelling of any word in data type list, blank will be shown and "String" will be used.
  • Characters after third column will be neglected.
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] the number of columns may vary.
See "Schema of Table Model" for details regarding schema structure.

Output Schema

Same as the settings of Input Schema.
If error occurs, unsuccessful input data will be output. Otherwise, Output will be null.

Loading schema in Mapper

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

Transaction

Transaction is NOT supported.

Usage in a PSP script

Cannot be used in a PSP script.

Available Component variables

Component variable name Description Remarks
count The number of input data that had been writen successfully.
  • The value defaults to 0.
error_count The number of input data that had been writen unsuccessfully.
  • The value defaults to 0.
commit_type The number of commit type.
  • The value defaults to 1.
  • The meanings of the number are as follows.
    • 1 means [All records will be committed or rollbacked].
    • 2 means [Commit non-error records(Stop at the error line If any error occured)].
    • 3 means [Commit non-error records(Skip error lines If any error occured)].
message_category When an error occurs, the category to which corresponding message code belongs to is stored.
  • The value defaults to null.
message_code When an error occurs, its corresponding message code is stored.
  • The value defaults to null.
message_level When an error occurs, the severity of the corresponding message code is stored.
  • The value defaults to null.
error_type When an error occurs, the type of the error is stored.
  • 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 The error message for the error occurred is stored.
  • The value defaults to null.
  • The message may vary depending on DataSpider Servista version.
error_trace Stack trace of the occurred error is stored.
  • The value defaults to null.
  • The message may vary depending on DataSpider Servista version or the client application used.

Specification Limits

Main exceptions

Exception name Causes Solution
ResourceNotFoundException
Resource not defined.Name: []		 [Destination] is not specified. Specify [Destination].
ResourceNotFoundException
Resource not defined.Name:[<name of Global Resources>]		 Resource definition selected in [Destination] is not found. Please check the global resource specified in [Destination].
InvalidPropertyConfigurationException
<Global resource name >:There is no BusinessConnector connection.		 Resource definition selected in [Destination] is not found. Please check the global resource specified in [Destination].
BusinessConnectorException
<Global resource name > Required connection settings cannot be null. Cannot connect to AX server. 		[Destination]Resource definition is insufficient. Please check all items of resource definition selected in [Destination].
InvalidPropertyConfigurationException
Table name cannot be null.		 [Table Name] is not set. Enter [Table Name].
BusinessConnectorException
The operation failed. The record <Table Name> does not exist. 		[<Table Name>] may be incorrect. Please check the name of table in Microsoft Dynamics AX.
VerificationException
Action is wrong.		 [action] may be incorrect. Please check the selection of [action].
InvalidPropertyConfigurationException
No column setting.		 [Input] is not set. Please check the setting of [Input].
BusinessConnectorException
The supplied method arguments are not valid. 		[<Input/Column Name>] may be incorrect. Please check the [<Input/Column Name>] that should be same as the column name in Microsoft Dynamics AX table.
BusinessConnectorException
Unable to compile static void NetExecuteStmt(<Table Name> axTbl_0)
{ select forupdate axTbl_0 where axTbl_0<Input/Column Nam> == the value of [Input data];} 		[<Input/Column Name>] checked as Key may be incorrect. Please check the [<Input/Column Name>] checked as Key.
BusinessConnectorException
Input string was not in a correct format. 		[<Input/Data Type>] may be not same as the data type in Microsoft Dynamics AX. Please check the [<Input/Data Type>] that should be same as the data type in Microsoft Dynamics AX.
CannotConvertException
<column no> column error Columnlogic - <input data type> can not be converted to <Native type>. 		Input data may be include error format data. Please check the input data.
InvalidPropertyConfigurationException
Key Column for where must be set. 		[<Input/Key>] may not be checked. Please check the [<Input/Key>].
If the checkbox of [<Input/Key>] is able to be changed, one column should be checked at least.
InvalidPropertyConfigurationException
Column for update must be set. 		All columns in [<Input/Key>] may be checked. Please check the [<Input/Key>].
If the checkbox of [<Input/Key>] is able to be changed, one column should be removed the check at least.

Notes

About update

When update a table, check [Input/Key] to select the columns for the update condition. The column names for the update condition are specified in the WHERE clause of UPDATE statement, and the values of columns not checked will be updated when these records match the update condition.

Update

More than one column can be specified for the update condition. Records will be updated when they match all values in the update condition.