VI SQL Process Object

The Visual Integrator (VI) SQL process object runs SQL statements against an ODBC-accessible database.

The SQL process object does not read or write a data flow. It processes SQL administrative statements (such as CREATE TABLE or DROP TABLE), or performs actions not associated with Queries or Inserts (such as UPDATE).

The SQL process object should be alone in a task.

The SQL process object has two panes where you set attributes.

NOTE: There is no column grid because there is no data flow.

VI SQL Process Object All Panes

Object Attributes

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

Attribute	Description
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_Statement	Specifies the SQL statement to run on the ODBC database. The semi-colon, usually required to terminate an SQL statement, is not required. Stored procedures are supported.
Sql_Qualifier	Optionally, specifies an SQL qualifier for connecting to the ODBC database. Click the browse button that appears to navigate to the Sql_Qualifier 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.
Encoding	Defines how files names are read and interpreted in terms of character encoding. 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.
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.