VI SQL Output Object

The Visual Integrator (VI) SQL output object writes data out to an ODBC-accessaible database. The specified SQL table or view must exist and not be locked by another process.

The SQL output object has three panes where you set attributes.

VI SQL Output Object All Panes

Object Attributes

You set attributes for the SQL output object in the object attributes pane.

Attribute	Description
Input	Defines the object from which the data flow arrives. Use one of the following methods to attach the SQL output object to an input data flow object: Right-click the data flow object, and click New Object after this > Output > SQL. Place the SQL output object on the task flow, and click its Input attribute's browse button. Move the pointer to the canvas and note that a dotted-line connector is attached to the SQL output object. Attach this connector to the data flow object. NOTE: The SQL output object's Input attribute is populated with the name of the input data flow object.
Sql_Source	Specifies the SQL data source from the list. If no data sources appear in the Sql_Source list, click <Refresh datasources>. VI creates one connection for each query or input object. There is no pooling. Do not use the Connect_String or the Connect_File attributes when using the Sql_Source attribute because they are mutually exclusive.
Sql_Owner	Specifies the SQL owner if the Sql_Table attribute is set. If no names appear in the Sql_Owner list, click <Refresh schemas>.
Sql_Table	Specifies an SQL table or view name by selecting it from the list. If no names appear in the Sql_Table list, click <Refresh tables>. All columns in the specified table or view are selected when you use the Sql_Table attribute.
Sql_Qualifier	Specifies the SQL qualifier when connecting to a Windows ODBC source. Click the browse button in the Sql_Table box, and browse to the file.
Connect_String	Specifies the ODBC connection string. VI creates one connection for each query or input object. There is no pooling. Do not use the Sql_Source or the Connect_File attributes when using the Connect_String attribute because they are mutually exclusive.
Connect_File	Specifies an obfuscated file that contains the connect string. This method of specifying the connect string is used to hide the SQL username and password from casual access. Click the browse button that appears after you click inside the field and navigate to the file. Do not use the Sql_Source or the Connect_String attributes when using the Connect_File attribute because they are mutually exclusive. To create the connect file, you use the command-line Integrator: integ -save_connect_file sql-server.cfile See `-save_connect_file` command in Integrator from the Command Line.
Username	Specifies the username to log on to the ODBC data source. Type directly in the Username box, or click the browse button that appears to open the Enter ODBC credentials dialog box where you can enter both the username and password.
Password	Specifies the password to log on to the ODBC data source. Type directly in the Password box, or click the browse button that appears to open the Enter ODBC credentials dialog box where you can enter both the username and password. NOTE: The Enter ODBC credentials dialog box has a Test button so you can ensure that the credentials successfully connect to the ODBC datasource.
Default_Value	Specifies what value is given for columns missing from the input flow. null (default)—Null value is given for missing columns. The SQL column must allow null values. blank—Blank value is given for missing columns.
Null_Value	Defines a string that indicates a null value for columns that allow nulls to be set. If a value matches this exact string, then a null will be stored instead of this string value. If this attribute is not set, blank strings will be used to indicate nulls.
Allow_Missing_Columns	Specifies whether all table columns must appear in the input flow. true—Missing table columns from the input flow are given the default value as controlled by the Default_Value attribute. false (default)—An error occurs when table columns are missing from the input flow.
Clear_Table	Specifies whether to clear the table before saving data to it. true—All rows are deleted from the table before VI writes its data. false (default)—No data is deleted from the table before VI writes its data.
Bulk_Insert	Specifies whether to send data to the SQL database as multiple insert statements. true (default)—Multiple insert statements are not used to send data. false—Data is sent to the database as multiple insert statements. Use of this attribute can be helpful if there are error messages such as ODBC driver requesting duplicate data for column <n> or No data at execution values pending in the logs.
Encoding	Defines the encoding for the output file(s). Values include: auto—The input object sets the encoding based on the file signature and the Unicode state of other objects in the same task. ascii—The characters in the file are interpreted as ISO-8859-1 or Latin1 characters. gb18030—The file is interpreted as Chinese National Standard 18030-2000 characters. The gb18030 encoding option is supported on Windows platforms only. latin1—The characters in the file are interpreted as ISO-8859-1 or Latin1 characters. utf-8—The file is interpreted as UTF-8 Unicode characters. unicode—The file is interpreted as 2-byte Unicode characters (UCS-2) with native byte swapping, unless overridden by a UCS-2 file signature. unicode-be—The file is interpreted as UCS-2 characters in a big-endian fashion. unicode-le—The file is interpreted as UCS-2 characters in a little-endian fashion. UCS-2 and UTF-8 files can include a Byte Order Mark (BOM) at the beginning of the file to denote the file encoding. These file signatures are defined as follows: UCS-2 Big Endian—`FE FF` UCS-2 Little Endian—`FF FE` UTF-8—`EF BB BF` File signatures are common for Unicode files on Windows operating systems. If the file input object reads multiple files, the signature of each file determines its encoding. If the encoding attribute is auto and no signature is found, the encoding is assumed to be latin1 if no other object in the task handles Unicode data and the VI file is not encoded as utf-8 (using the charset 1208 directive). Otherwise, the encoding is assumed to be utf-8. See also Integrator Unicode Data Support.
Sql_Type (OS/400 only)	Specifies the SQL type. If SYSTEM or SYS is selected, it uses system naming.
Sql_LIbrary (OS/400 only)	Specifies the OS/400 library for the DB2 database. If specified, it overrides the Sql_Owner attribute.
Sql_Commit (OS/400 only)	Specifies the commit behavior as rows are added to the output table. These values correspond to the commit types found in the IBM SQL Call-Level Interface book. none (`SQL_TXN_NO_COMMIT`)—Commitment control not used. read_committed (`SQL_TXN_READ_COMMITTED`)—Dirty reads are not possible. Non-repeatable reads and phantoms are possible read_uncommitted (`SQL_TXN_READ_UNCOMMITTED`)—Dirty reads, non-repeatable reads and phantoms are possible. repeatable_read (`SQL_TXN_REPEATABLE_READ`)—Dirty reads and non-repeatable reads are not possible. Phantoms are possible. serializable (`SQL_TXN_SERIALIZABLE`)—Transactions are serializable. Dirty reads, non-repeatable reads, and phantoms are not possible.
Connection_Status	Specifies connection status: Connection is OK—Connection to the data source is active. Missing connect_string or sql_source—There is no data source designated. Exception while trying to connect—Not connected to the data source even though a connection is set using a sql_source, connect_string, or connect_file attribute. Click the browse button that appears for more information about the exception.

Column Grid

The SQL ouput object column grid displays the input data flow.

Attribute	Description
Input Column	Displays the name of each input column. This attribute is read-only.
Source Object	Displays the name and object type of the source object. Double-click the Source Object for a column to change the task flow focus to that object.