SQL Input Object

An Integrator SQL input object defines data to be accessed from an ODBC accessible database in a Windows environment. In a UNIX environment, unixODBC is supported. On the OS/400 platform, it reads data from a DB2 file or query. Tables or views can be specified or SQL code can be used to query tables on the server.

SQL Attributes

Attribute	Type	Description
input_type (required)	String	Identifies the object as an SQL input object. The value of this string is "sql".
sql_type	String	Selects the SQL type (system or SQL). If this attribute equals "SYSTEM" or "*SYS", then system naming is used. (OS/400 only) NOTE: This attribute is Sql Type in Visual Integrator.
sql_source	String	Specifies the name of the ODBC source to use (that is, the DSNs). Integrator creates one connection for each query/input object (no pooling). To build a Model from an ODBC source, either connect_string or sql_source must be specified. On the OS/400, sql_source is used to specify a remote DB2 database to use. The sql_source should refer to the database name as it appears in the WRKRDBDIRE command. If no data source is given, Integrator will connect to the local DB2 database. Integrator makes a single DB2/400 connection for all input objects in a script. NOTE: This attribute is Sql Source in Visual Integrator.
connect_string	String	Specifies the ODBC connection string for connecting to an ODBC data source. Integrator creates one connection for each query/input object (no pooling). To build a Model from an ODBC source, either connect_string or sql_source must be specified. The appropriate ODBC driver must be installed when accessing a database using SQL objects in a Windows environment. NOTE: This attribute is Connect String in Visual Integrator.
connect_file	String	Defines an obfuscated file containing the connect string. The file can be created by using the '-save_connect_file' option to the Integrator command-line. This method of specifying the connect string is used to hide the SQL username and password from casual access. The connect_file attribute conflicts with the connect_string attribute. NOTE: This attribute is Connect File in Visual Integrator. See Options for ODBC Login Credentials.
sql_owner	String	Defines the SQL owner for the sql_table, that is, the schemas in the DSN. (OS/400 may require the string in all caps.) NOTE: This attribute is Sql Owner in Visual Integrator.
sql_library	String	Defines the OS/400 library for the DB2 database. If this attribute exists, it overrides the sql_owner attribute. (OS/400 only) NOTE: This attribute is Sql Library in Visual Integrator.
sql_qualifier	String	Defines the SQL qualifier for connecting to the ODBC source. (Windows only) The qualifier's meaning varies from ODBC driver to ODBC driver, and Integrator has no knowledge of expected attributes. For example, when supplying paths, use the operating system path to the database, not a project path. NOTE: This attribute is Sql Qualifier in Visual Integrator.
sql_table	String	Defines the SQL tables or views in the DSN. If this option is used, all columns in the specified table or view are selected. Exactly one of the sql_table, sql_query, or sql_file attributes must be defined in the SQL object. NOTE: This attribute is Sql Table in Visual Integrator.
sql_query	String	Defines the SQL select query. The columns from the query are selected as the input source. Exactly one of the sql_table, sql_query, or sql_file attributes must be defined in the SQL object. (Visual Integrator has some dialog options that can assist in the construction of this attribute.) NOTE: This attribute is Sql Query in Visual Integrator.
sql_file	String	Defines a text file containing the SQL query. If this option is used, the query is read from the specified file, and the columns in that query are used as the input source. The SQL file must contain a Select statement. To support Query/400 files, any lines before the Select command will be ignored. Multiple spaces and line breaks will be compress into single spaces. Exactly one of the sql_table, sql_query, or sql_file attributes must be defined in the SQL object. NOTE: This attribute is Sql File in Visual Integrator.
skip_read	Boolean	When using Visual Integrator, this option allows the application to find columns for an SQL query without having to execute the query. When set to "true" in an SQL input object connected to a Fileout output object, the column headers are written out to the file, but no rows of data (0 records). If not specified, it defaults to "false", meaning that Visual Integrator will execute the query. NOTE: This attribute is Skip Read in Visual Integrator.
where_clauses	Array of Strings	Defines an additional set of where-clauses to be added to the SQL query statement. Each string should have the form of an SQL where-clause, such as "detail.product_id = '1166' ". Multiple where-clauses will be combined using a logical AND. This attribute allows SQL where-clauses to be supplied as array parameters. Keep the following points in mind when specifying where clauses: Each clause should be contained in double quotes (""). A column name containing a space is contained in single quotes (''). A string value is contained in single quotes (''). A numeric value is not contained in single quotes (''). A semicolon (;), usually required to terminate a SQL statement, is not required.
sql_no_trim	Boolean	Indicates when set to "true" that trailing spaces are not trimmed from the SQL input. The default setting is "false", with trailing space being removed. NOTE: This attribute is Sql No Trim in Visual Integrator.
first	Integer	Controls the number of records read in from the SQL input. If first is present and positive, Integrator reads up to the specified number of records. This limit is particularly useful for script testing on a small number of input records.
cleanup	Boolean	Controls whether or not the data read from the SQL database is filtered for certain problem characters (such as carriage return, line feed, and tab). The default is "true".
aliases	Array of Strings	Defines new column names for the columns already defined in the input data. Format is "oldname=newname". Blanks before or after the columns names will be ignored. Spaces within a column name are acceptable. If newname is blank, then the given column is deleted from the output flow. NOTE: This attribute is Alias Lines in Visual Integrator.
prefix	String	Defines a prefix that is prepended to all columns in the flow that are not aliased using the aliases array. If you want a space between the prefix and the column name, include that space in the prefix string definition.
keep_columns	Array of Strings	Defines a list of columns to be kept by the input object. If this attribute is not used, all columns are kept. The output flow of the object is limited to those columns that are listed, and no excluded columns are available to subsequent process objects. Column names in the keep_columns array should be given after they are aliased or prepended with the prefix string.
rename_duplicates	Boolean	Creates new column names for duplicate columns names that appear in the input flow for this object. Subsequent columns for a column with the same name as a column name will be 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 will be skipped. For example, if the input flow already has a column named "DESC_2", the object will name the duplicate column DESC as "DESC_3". 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. NOTE: This attribute is Rename Duplicates in Visual Integrator.
encoding	String	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.
trace_after	Sub-object	Traces data flows leaving the specified object, which makes debugging scripts easier. This is equivalent to adding a Trace process object immediately after the current object. See Embedded Trace Object for more on using trace sub-objects.