This node allows you to write out data to a ODBC data source, it also allows you to set parameters on how to write out the data.
- Data Source section allows you to choose the ODBC Data Source currently configured on your computer system or you can type in your own. The dropdown list will show all the ODBC configured connections available, to have an entry shown here you will have to first generate one on your system. To do this follow the instructions in the "ODBC Data Sources" control found in your "Control Panel".
- Login Details section allows you to specify the User Name and or Password required to connect to the selected Data Source. If you configured your Data Source with the user name and password then you do not have to supply it here.
- DB Table section sets up the Table for data insertion.
- Table Name entry allows you to enter in the name of the table to which data will be written into.
- Append option will append data to the existing data within the Table.
- Truncate option will perform the "truncate table tablename" SQL statement before adding data into the table.
- Delete option will perform the "delete from tablename" SQL statement before adding data into the table. This option is suitable for ACCESS databases.
- Insert Options section allows you to configure the data insertion into the Data Source.
- Strip Space when selected allows you to remove leading and lagging spaces from the data value before inserting into the database.
- Prepared when selected will compile the SQL statement within the selected ADO datasource provider. It will also speed up the data insertion into the database. If you recieve an error that the SQL statement can not be compiled then uncheck this option.
- Timeout value (seconds) sets the timeout for inserting each row into the database.
- SQL Insert option when selected will insert data into the database using a complete SQL statement as shown below:
insert into tablename values(123,'Apple',NULL,123.456)
If any DISCRETE data element contains the single quote then the SQL statement will contain double single quotes as shown:
DISCRETE data value: Apple'Banana
insert into tablename values(123,'Apple''Banana',NULL,123.456)
Use this option if the Parameter Insert option fails to work for the selected datasource provider.
- Parameter Insert option when selected will insert data into the database using a parametised SQL statement as shown below:
insert into tablename values(?,?,?,?)
The ? will be populated with the data values for each row of data been inserted.
NOTE: This option is the fastest method of inserting large amounts of rows into the database.
- Output Data Columns section allows you to analyse the incomming connected node. Clicking the Analyse button will determine the columns of data fed in from the incomming node.
NOTE: Global Constants can be used within the UserName, Password and Table Name entries.
- The target datasource must have the specified table created before any data can be inserted into the table. The columns defined for the table must match the defined Output Data Columns as a DISCRETE and CONTINUOUS data type. For example, an Oracle database could have the columns defined as VARCHAR and NUMBER. Also for a ACCESS database the columns can be defined as TEXT and NUMBER(DOUBLE). Other data types can be used but they must match the data that is been been inserted.
- If the data inserted into the Data Source contains missing values which are designated as '?' then the SQL insert statement will replace the '?' missing value with NULL.
NOTE: To be able to use the OutputODBC Node you require to install the "Microsoft Data Access Components" (MDAC) libraries. The procedure for installing MDAC is as follows:
- Install DCOM95 for Windows95 or DCOM98 for Windows98
- Install the latest MDAC
Both of these libraries can be downloaded from the Microsoft MDAC web site.