VI SQL Input Object

The Visual Integrator (VI) SQL input object brings database data into the script.

The object defines a data input flow from an ODBC data source in Windows, unixODBC in UNIX, and DB2 in OS/400.

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

VI SQL Input Object All Panes

Object Attributes

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

Attribute	Description
Additional where condition(s)	Specifies any additional sets of WHERE clauses to add to the SQL query statement. Each string should be in the SQL WHERE clause form, such as: `region = 'north'` `detail.product_id = 2017` Multiple WHERE clauses are combined using a logical AND operator. These WHERE clauses can be supplied as array parameters. Numeric values do not contain quotes, but string values are contained in single quotes. A terminal semicolon is not required.
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. If you use the Sql_Table attribute, you cannot set either the Sql_Query, or Sql_File attributes.
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.
Sql_Query	Specifies the SQL query. Click the browse button to open the Edit SQL query dialog box where you can enter the query. The columns from the query are selected as the input source. If you use the Sql_Query attribute, you cannot set either the Sql_Table or Sql_File attributes. See Column Grid section below.
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. NOTE: On Window servers, the attributes for Username and Password are only used when the ODBC's system DSN does not specify "Windows Authentication".
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.
Sql_File	Specifies a text file that contains the SQL query. Click the browse button that appears when you click in the Sql_File box, and browse to the file. The query is read from the file, and the columns in the query are used as the input source. The SQL file must contain a SELECT statement. Multiple spaces or line breaks are compressed into single spaces. Any lines before the SELECT statement are ignored for OS/400 files. If you use the Sql_File attribute, you cannot set either the Sql_Table or Sql_Query attributes.
Sql_No_Trim	Specifies whether or not to trim trailing spaces from the SQL input data flow. true—Trailing spaces are not trimmed from the input data flow false (default)—Trailing spaces are trimmed from the input data flow
Cleanup	Specifies whether VI filters the data for certain potentially problematic characters, such as carriage return, line feed, and tab. true (default)—Data is filtered for the problem characters false—Data is not filtered for the problem characters
Skip_Read	Specifies whether VI finds columns for an SQL query without having to run the query. true—If connected to a Fileout object, the column headers are written out to the output flow, but no rows of data are written false (default)—VI runs the query and returns all the data
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. Keep the following in mind: If any object handles Unicode data, the database returns Unicode characters when accessed; otherwise, the database returns Latin characters. On Windows platforms, databases are accessed using the Wide character ODBC APIs. On UNIX platforms, VI relies on the UTF-8 support available in UNIX databases; data is accessed using the single character ODBC APIs, but interpreted as UTF-8. You may need to set the ODBC driver settings to enable UTF-8 support.
First	Specifies a number to limit how many records are read from each input file. This attribute is useful for script testing on a small number of records. If not used, all rows are read.
Prefix	Defines a prefix that is added to all column names in the flow. If you want a space between the prefix and the column name, include that space in the prefix string definition. Any columns assigned an alias do not use the prefix; instead, the columns use the alias name.
Rename_Duplicates	Renames duplicate columns so that each column in the output data flow has a unique name. The duplicate naming process occurs before attributes defining aliases, prefixes, or the columns to keep are applied, so these generated column names can be aliases to another name. true—Creates new, unique column names for duplicate columns Subsequent columns with the same name are given the names name_2 ... name_(n) based on the positional order in the input. If for some reason, a column in the input flow already has this name, that number is skipped. For example, if the input flow already has a column named DESC_2, the object names the duplicate column DESC as DESC_3. false (default)—Duplicate column names are not renamed and the duplicate columns appear in the output data flow
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.
Alias_Lines	Aliases can be set and edited in both the Alias_Lines attribute and the column grid. The column grid allows for graphical editing, while the Alias_Lines attribute is set at the code level in the following format: OldColumnName=NewColumnName For example: inv_nbr=Invoice Number Where Invoice Number is the alias for inv_nbr. The Alias_Lines method is useful for working with array parameters used as aliases. VI cannot process these array parameters because they are not in a format that VI can interpret. VI considers these array parameters to be malformed aliases and displays a warning message in the Logs tab. For example: Alias definition "$(ExternalAliases)" in object "From List" is not formatted as "OldColumn=NewColumn". When the line contains a parameter Integrator is most likely able to resolve it when the parameter is properly defined. To edit this alias definition use the "Alias_Lines" property. This message indicates that there is a malformed alias named $(ExternalAliases) in the object named From List. The array parameter displays as $(ExternalAliases) in the script. For VI to interpret this array parameter, you must assign an alias in the VI format. To assign an alias and resolve the error: Select the object in the task flow that is mentioned in the warning message. In this example From List. Click in the Alias_Lines field, and then click the browse button that appears. The Alias lines for <object_name> dialog box displays. Edit the alias in the form of: `OldColumnName=NewColumnName` In this example: `$(ExternalAliases)=External Aliases` Click OK. The array parameter has an alias, and the error message no longer appears.
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 input column grid displays the columns from the input files.

Whenever you adjust your SQL query, VI attempts to refresh the column grid. If it succeeds, the query runs OK. If it cannot refresh the columns, there is an issue with the query. Right-click the SQL object in the data flow area and select Test Object. You can then examine the run results to determine the problem. Once the query is fixed, you might see rows colored pink. Use the context menu to Remove All Errors.

Attribute	Description
Name	Displays the name of each input column. This attribute is read-only.
Alias	Defines alternate names for any of the input columns. Spaces before or after an alias column name are ignored. Spaces within an alias column name are acceptable.
Keep Order	Manages the order that columns display in the output data flow. By default, columns that are passed to the next object in the data flow are displayed in the order that they appear in the Name column. You can change this order by typing a number in the Keep Order column. When you assign a Keep Order number, the Keep column is checked automatically. The Keep Order numbers might reorder to accommodate any changes you make.
Keep	Manages which columns are kept in the output data flow. If no columns have a Keep check mark, all columns are kept in the output data flow, except for any explicitly marked Remove. Select the Keep check box for columns you want to explicitly keep in the output data flow. A number is automatically added in the Keep Order column when you select its Keep check box. After marking any column with a Keep check mark, only those marked Keep are kept in the output data flow. NOTE: After any Keep check boxes are checked, do not use the Remove check boxes as clicking a Remove check box sets all Keep check boxes to unchecked.
Remove	Manages which columns are removed from the output data flow. Select the Remove check box for columns that you want to explicitly suppress from the output data flow. NOTE: Use the Remove check boxes only when no Keep check boxes are checked.