SQL Process Object
(Windows and UNIX)

The Integrator SQL process object allows SQL statements to be run against an ODBC accessible database. Instead of reading or writing a dataflow, the SQL process object allows Integrator to process SQL administrative statements (such as CREATE TABLE or DROP TABLE), or perform actions not associated with Queries or Inserts (such as UPDATE). It is similar to the Execute object in that the task should consist of a single PROC object.

SQL Attributes

Attribute Type Description
process_type
(required)		 String Identifies the object as an SQL process object. The value of this string is "sql".
connect_string String Specifies the ODBC connection string for connecting to the ODBC data source. To execute an SQL statement for 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.
  • When using the 64-bit Data Integrator, make sure a 64-bit ODBC driver is installed.
  • When using the 32-bit Data Integrator, make sure a 32-bit ODBC driver is installed.

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_source String

Specifies the name of the ODBC source to use. To execute an SQL statement for an ODBC source, either connect_string or sql_source must be specified.

NOTE: This attribute is Sql Source in Visual Integrator.

sql_statement String

Specifies the SQL statement to execute on the ODBC database. The semi-colon, usually required to terminate a SQL statement, is not required. Stored procedures are allowed.

NOTE: This attribute is Sql Statement in Visual Integrator.

sql_qualifier String

Defines the SQL qualifier for connecting to the ODBC source.

NOTE: This attribute is Sql Qualifier in Visual Integrator.

encoding String Defines the SQL qualifier for connecting to the ODBC source. Values include:
  • auto—The SQL command will be interpreted as UTF-8 if any of the other objects in the same task processes Unicode characters; otherwise, it will be interpreted as ISO LATIN. (default)
  • ascii—The SQL command will be interpreted as ISO-8859-1 or Latin1 characters.
  • latin1—The SQL command will be interpreted as ISO-8859-1 or Latin1 characters.
  • utf-8—The SQL command will be interpreted as UTF-8.
  • unicode—The SQL command will be interpreted as UTF-8.

See Integrator Unicode Data Support.

Keep the following points in mind when working with the SQL process object:

  • To get result sets from a stored procedure, use the SQL process object to first call them. The SQL input object can then use a Select statement.
  • To include a dollar sign ($), make sure to use two dollar signs ($$) since a single dollar sign is used to process parameters.
  • The 32-bit Integrator requires a 32-bit ODBC driver and the 64-bit Integrator requires a 64-bit ODBC driver regardless of the platform Integrator is installed on. See 32-Bit versus 64-Bit Integrator.
  • There may be situations where Integrator cannot directly handle the data accessed by the ODBC (for example, MongoDB (noSQL)). Use of CSV as an intermediary format may be appropriate.
  • When reading from an ODBC source, a text or variable length field with an unspecified maximum length is capped at 32,768 characters.

  • Temporary tables are only available inside a single transaction. Each SQL process object is a single transaction, and the transaction ends before the next Integrator task.