Create a connection to an ODBC database
Data gateway
Database properties | Description | Required |
---|---|---|
Direct Access gateway |
Select the Direct Access gateway through which you need to connect to your data source. Information noteUsers that need to leverage gateway-enabled data connections must have the Can Consume Data permission for the space with which the gateway is associated.
|
Yes |
Database properties
Database properties | Description | Required |
---|---|---|
System DSN ODBC driver |
Choose one of the following:
|
Yes |
ODBC source |
Click the Load button. The drop-down list will be populated with either of the following:
Choose the driver or System DSN for your data source. |
Yes |
Connection string |
Do one of the following:
Information noteAlthough the connection string usually starts with Driver= (for drivers) or DSN= (for System DSNs), these parameters should be omitted as they are automatically added when establishing a connection to the data source.
Example of an ODBC driver connection string - MariaDB syntax: SERVER=mydatabase.mydomain.com;USER=odbc_user;PASSWORD=odbc_pw;DATABASE=odbc_test;PORT=3306 Warning noteDo not include sensitive parameters such as user, password, tokens, and so on, in the connection string, as such parameters might be exposed as plain text when establishing the connection. Instead, use the Encrypted Properties option (see below) to store sensitive connection string parameters.
|
Yes |
Encrypted properties | You can securely store encrypted connection string properties. For each encrypted property, you have to provide a Name and a Value. Use the and buttons to the right of the Value field to add and remove properties. The connection string will be completed with the specified property names and their unencrypted values when connecting to the data source. | No |
Attached file properties | For each attached file property, specify a property Name and a File name. Use the and buttons to the right of the File button to add and remove properties. A connection string will be completed with specified property names and temporary path to uploaded the file when connecting to the data source. | No |
Database specific properties
Database properties | Description | Required |
---|---|---|
SQL syntax |
Select your data source syntax or select Custom SQL syntax to specify a custom syntax. Out-of-the-box syntax is provided for the following data sources:
|
Yes |
Identifier start delimiter | The character string that is used as the starting delimiter of a quoted (delimited) identifier in SQL statements. This field is read-only unless you selected Custom syntax. | A value needs to be provided if Custom SQL syntax is selected. |
Identifier end delimiter | The character string that is used as the ending delimiter of a quoted (delimited) identifier in SQL statements. This field is read-only unless you selected Custom syntax. | A value needs to be provided if Custom SQL syntax is selected. |
SELECT statement template for Data Preview |
This field is read-only unless you selected Custom syntax. The template supports the use of the following variables:
Warning noteDo not omit "LIMIT" keywords from the SELECT statement template for Data Preview. "LIMIT" keywords differ according to the SQL syntax supported by the data source. For example, MariaDB uses "LIMIT" while SAP Sybase ASE uses TOP. Even if "LIMIT" keywords are omitted, the connector will always truncate the data preview result set to a maximum of 50 records. However, in some cases, performing just SELECT xxx FROM yyy without limiting the result-set could take a long time to complete on the database server, impacting Select dialog performance.
See also: |
Values need to be provided if Custom SQL syntax is selected. |
Miscellaneous properties
Database properties | Description | Required |
---|---|---|
Allow non-select queries |
You can execute load scripts that do not start with an SQL SELECT statement, for example UPDATE, INSERT, EXEC, and WITH ... AS. For more information, see Executing non-SELECT statements |
No |
Load primary key metadata |
Turn on this option to load primary key metadata. Information noteThe Load primary key metadata option is not supported with all drivers. If this option is turned on for an unsupported data source, a message will be shown in the Select dialog (when retrieving a table) that an error occurred while trying to retrieve the primary key metadata. In such a case, you need to turn off the Load primary key metadata option before retrying the operation.
|
|
Query timeout | Amount of time before the query times out. Can be set from 30 seconds to 65535 seconds. Default is 150. | No |
Load optimization properties
If you have large datasets, the Bulk Reader will automatically load larger portions of data in the iterations within a load, instead of loading data row by row.
Property | Description | Required |
---|---|---|
Maximum string length |
Maximum length of string fields. This can be set from 256 to 16384 characters. The default value is 4096. Setting this value close to the maximum length may improve load times, as it limits the need to allocate unnecessary resources. If a string is longer than the set value, it will be truncated, and the exceeding characters will not be loaded. |
No |