IMPORT ODBC command

Used to define ACL tables from ODBC (Open Database Connectivity) data sources.

Syntax

IMPORT ODBC SOURCE source_name TABLE table_name <QUALIFIER data_qualifier>
<OWNER user_name> <USERID user_id> <PASSWORD num> <WHERE where_clause>
<TO table_name> <WIDTH max_column_width> <MAXIMUM max_text_width>
<FIELDS field <,...n>>

Parameters

SOURCE source_name

Specify the name of the ODBC data source to connect to.

TABLE table_name

Specify the table name in the ODBC data source to import data from. The table_name parameter usually refers to a database table in the source data, but it can refer to anything ACL imports as a table. For example, if you use the Microsoft Text Driver, the table_name parameter refers to the text file you want to import data from.

QUALIFIER data_qualifier

Optional. Specifies a text qualifier. You must specify the data_qualifier value as a quoted string. To specify a double quote as a text qualifier, enclose the double quote in single quotes.

OWNER user_name

Optional. Specifies the name of the database user account that owns the table you are connecting to.

USERID user_id

Optional. Specifies the username to use to access the data source.

PASSWORD num

Optional. Specifies the numbered password definition to use for password-protected ODBC data sources.

Note that you are specifying a password definition previously created using either the PASSWORD or the SET PASSWORD command, not the password itself. The num parameter corresponds to the number of the password definition. For example, if you previously created two password definitions in your script, PASSWORD 2 specifies that the second one should be used.

If you used the PASSWORD command to create the password definition, no password value is defined, so a password prompt is displayed when the script is run. If you used the SET PASSWORD command to create the password definition, a password value is defined, so no password prompt is displayed, which is appropriate for scripts designed to run unattended.

For more information, see PASSWORD command and SET command.

WHERE where_clause

Optional. Specify a value for the where_clause if you want to limit the records returned based on a criteria you specify. The where_clause must be a valid SQL statement and must be entered as a quoted string. For example WHERE “SALARY > 50000”

TO table_name

Optional. Specify the table_name parameter value as a quoted string with a .FIL file extension to create the ACL table data file (e.g., “Invoices.FIL”).

If you do not want to create the .FIL file in the same directory as the ACL project, you can specify an absolute or relative file path to the folder to write the .FIL file to.

For example: “C:\Invoices.FIL” or “\data\Invoices.FIL”

WIDTH max_column_width

Optional. Specifies the maximum length of character fields you are importing. You can enter any value between 1 and 254. The default value is 50.

MAXIMUM max_text_width

Optional. Specifies the maximum length of text, note, or memo fields you are importing. You can enter any value between 1 and 1100. The default value is 100.

FIELDS field

Optional. Specifies the field(s) to import. If you specify multiple fields, each field must be separated by a comma. If you omit the FIELDS parameter, all fields are imported.

Enclosing the field names in quotation marks makes them case-sensitive. If you use quotation marks, the case of field names must exactly match between the FIELDS parameter and the ODBC data source. If you use quotation marks and the case of the field names does not match, the fields are not imported.

Remarks

When using the IMPORT ODBC command to define an ACL table you can suppress the time portion of datetime values by prefacing the command with the SET SUPPRESSTIME command. This capability is intended for use in pre-version-10.0 ACL scripts that assume the time portion of datetime values will be truncated. If SET SUPPRESSTIME ON is not added to these scripts, they will not run in the datetime-enabled version of ACL. For more information, see the “SET SUPPRESSTIME” section in SET command.

Example

The following example imports data from a SQL Server database to an ACL table named Trans_Dec11.

IMPORT ODBC SOURCE "SQLServerAudit" TABLE "Transactions" OWNER "audit"
TO "C:\ACL DATA\Trans_Dec11.FIL" WIDTH 100 MAXIMUM 200
FIELDS "CARDNUM","CREDLIM","CUSTNO","PASTDUEAMT"


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback