ODBC
The two available ODBC data sources (64-bit and 32-bit) provide a standard, SQL-based interface to access many different database systems.
Supported Drivers and Versions
Data Hub supports database connections to both 64-bit and 32-bit ODBC drivers for the following versions:
64-bit driver. 3.8 driver. Later versions may also work.
32-bit driver. 3.8 driver. Earlier and later versions may also work.
Important
Be sure to select the correct data source from the wizard, based on whether your database management system uses a 64-bit or 32-bit driver. The 32-bit data source is labeled as such. The 64-bit database is not explicitly labeled. To determine which data source you should use, contact your local database administrator.
Connect to the data source
The following connection settings can be defined for an ODBC driver-based data source. All settings, except System Table Regular Expression, are required.
Connection String - Enter the connection string that will be used by the ODBC driver to connect to the data source. The connection string may refer to an existing DSN. Refer to the documentation for the target data source to determine the correct format for the connection string. Consult the RSSBus website (www.rssbus.com) for details on the required connection strings and other parameters for RSSBus drivers.
User Name - Type the user name needed to connect to the specified data source.
Note
Some data sources may require login credentials to be specified as part of the connection string. In these cases, user name may be left blank. Refer to the documentation for the target data source to determine how to specify login credentials.
-Password - Type a password for the specified user name.
Note
Some data sources may need login credentials to be specified as part of the connection string. In these cases, the password may be left blank. Refer to documentation for the target data source to determine how to specify login credentials.
Identifier Starts With. Identifiers, such as column and table names, are automatically surrounded with delimiters. Enter the starting character that will be used when delimiting identifiers. For example, in SQL the identifier Sales Amount can be delimited as [Sales Amount], so the Identifier Starts With entry would be [.
Note
The default character for this setting is the double quote character ("). This delimiter works for many source databases, including SQL Server (square brackets also work for SQL Server).
Identifier Ends With. Identifiers, such as column and table names, are automatically surrounded with delimiters. Enter the ending character that will be used when delimiting identifiers. For example, in SQL the identifier Sales Amount can be delimited as [Sales Amount], so the Identifier Ends With entry would be ].
Note
The default character for this setting is the double quote character ("). This delimiter works for many source databases, including SQL Server (square brackets also work for SQL Server).
Rows Limit Template. Sometimes, a limited number of rows must be selected from a table, for example when generating a preview of the data in a pipeline. To implement this requirement, enter the template text which will be inserted into each SQL statement that is sent to the data source to select a limited number of rows. The template must include the #ROWS_LIMIT# parameter, which will be replaced with the number of rows requested from the data source.
Note
The required syntax depends on the requirements of the data source used. Consult the documentation for the data source to determine the correct syntax. For example, SQL Server uses the TOP keyword. In this case, the template would be TOP #ROWS_LIMIT#. If the required row limit was 5, a typical SQL statement after the parameter was replaced would be: SELECT TOP 5 [Sales Amount] FROM [Sales Orders].
Rows Limit Position. Select where the Rows Limit Template is inserted within each SQL statement that is sent to the data source.
Note
The position depends on the requirements of the data source used. Consult the documentation for the data source to determine the correct position.
Options:
After Select - The template will be inserted after the SELECT keyword. For example, in SQL Server, if the template text to be inserted is TOP #ROWS_LIMIT#, and the rows limit is 5, the SQL statement after the parameter was replaced would be: SELECT TOP 5 [Sales Amount] FROM [Sales Orders].
End of Query - The template will be inserted at the end of the SQL statement. For example, in Oracle, if the template text to be inserted is where rownum <= #ROWS_LIMIT#, and the rows limit is 5, the SQL statement becomes: SELECT "Sales Amount" FROM "Sales Orders" where rownum <= 5.
Supports Aliasing with AS - Check this option if the data source's SQL dialect supports aliasing using the AS keyword. For example, SELECT [SalesAmt] AS [Sales Amount].
-String Concatenation Operator - The operator used to concatenate string values. For example, the ANSI standard concatenation operator is '||' while Microsoft SQL Server uses '+'.
Wildcard Escape Character - The character used to escape wildcards like '%' and '_' in LIKE expressions.
System Table Regular Expression - This setting is optional. Type a regular expression to filter out tables returned from the data source. Tables whose names match the regular expression will not appear in data source's TABLES tab. Leave the text box blank to return all tables from the data source. For example, in PostgreSQL, the regular expression ^information_schema could be used to remove all tables whose names begin with "information_schema".
Note
For more detail on regular expressions, see: http://msdn.microsoft.com/en-us/library/az24scfc(v=vs.110).aspx
Date/Time Format String - Allows you to specify the date/time formatting used by the data source. If the displayed (default) string is not suitable for your database (yyyy-MM-dd HH:mm:ss.fff), you can specify a custom C# format string, replacing the default string.
Note
If this setting is left blank, the default string, described above, is used. For more information on this formatting, refer to: https://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx