For assistance with downloading OmniSci ODBC software and utilities, contact your OmniSci Sales Representative.
Connecting from Microsoft Windows Using ODBC
While there are many ways to connect to ODBC, perhaps the most common is to connect from a Windows 10 client using Tableau or Excel. OmniSci provides an ODBC connection utility to help you get started.
To configure your ODBC datasource:
Run the OmniSci Installer Tool provided by your OmniSci Sales Representative.
Enter the path to the ODBC driver (default is C:\OmniSci\ODBC\bin\).
Wait for the “SUCCESS!” message. Click OK.
Open your Windows control panel.
Open either the 32-bit or 64-bit version of ODBC Data Source Administrator, depending on the application you are connecting to the driver.
Enter the user Name (for example, omnisci), Platform, and Driver (OmniSciDriver).
In OmniSci ODBC Driver DSN Setup:
Enter an optional Description for your data source.
Enter the User (for example, OmniSci).
Enter the Password for your data source.
Enter the Host:Port values (for example, myhost.omnisci.com:6274).
Enter the Database name (for example, omnisci).
Enter the Max rows returned (for example, 10000).
Select the Protocol Type to connect to the OmniSci Web server: HTTP, HTTPS, Binary, or Binary Encrypted. If you select HTTPS or Binary Encrypted, you must enable encrypted connections using the Microsoft Management Console.
If you want to ease server certificate validation requirements, select Ignore Invalid Certificates (not recommended). If you do this, the CN name in the certificate is not required to match the network name of the host from which it was received.
In Connection Timeout, enter the time interval (in seconds) used to terminate the failed connection attempts.
To configure options for OmniSci integration with applications such as Alteryx, click Advanced Options. You can:
Escape single quotes or line-feed characters when a parameterized insert command is used.
Allow an SQLPrepare command to be run before an SQL SELECT command to return metadata about the columns. This adds an extra call to the OmniSci database and should be used with caution.
Allow the driver to remove blank schema names from qualified table names. The Alteryx database discovery GUI tool attempts to insert a blank query into qualified names such as omnisci..table. This option allows the driver to remove the extra period, transforming the name to omnisci.table.
Wait for the “Success!” message. Click OK.
Using HTTPS and Binary Encryption
In OmniSci ODBC Driver DSN Setup, if you set your protocol type to HTTPS or Binary Encrypted, follow these instructions to enable an encrypted connection:
Open the Microsoft Management Console (MMC).
On the File menu, select Add/Remove Snap-ins.
In the Available snap-ins list box, select Certificates.
In the Certificates snap-in box, select Computer Account, and then click Next.
Select Local Computer, and click Finish, and in the Add or Remove Snap-ins window, click OK.
In the left pane of the Console Root window, right-click the Trusted Root Certificate Authorities folder, click All tasks, and then click Import....
In the Certificate Import Wizard, click Next.
Click Browse, select the certificate file to import, and click Finish.
Installing ODBC on Linux
Follow these steps to configure OmniSciDB ODBC connections on Linux.
If required, install unixODBC, which you use to test that the OmniSci ODBC driver configuration is working correctly:
$ sudo yum install unixODBC
$ sudo apt-get install unixodbc
Type y when prompted to install the package.
Validate that isql is installed by running it from the shell:
If installed correctly, you see output showing the isql syntax and options.
Edit the /etc/odbc.ini file by changing the properties and values to values appropriate for your OmniSci installation. The included /configuration/odbc.ini.skeleton file (show below) provides a template and information about available options.
[ODBC Data Sources]
Description=64-bit OmniSci Driver
# HOST can contain ':port number' as in 'localhost:6274'.
# Protocol can be BINARY or HTTP.
# If not supplied, the protocol defaults to BINARY.
# If CA_CERT points to a valid PKI certificate, the driver
# attempts to establish an SSL connection. If not supplied
# or empty, the driver defaults to a nonencrypted connection.
# Note that the server port must be expecting an SSL connection.
# Provide a time interval (in seconds) used to terminate the failed connection attempts.
If SERVER_CA_CERT is supplied, it overrides the default search path.
Invalid non-mandatory options--for example, those that have misspellings--are ignored and do not generate errors.
Edit the /etc/odbcinst.ini file by changing the following properties/values in bold to values appropriate for your OmniSci installation.
SQLConnect establishes a connection between a driver and a data source. The connection handle references storage of all information about the connection to the data source, including status, transaction state, and error information.
SQLDriverConnect is an alternative to SQLConnect.It supports data sources that require more connection information than the three arguments in SQLConnect, dialog boxes to prompt the user for all connection information, and data sources that are not defined in the system information.
SQLExecDirect executes a preparable statement using the current values of the parameter marker variables, if any parameters exist in the statement. SQLExecDirect is the fastest way to submit an SQL statement for one-time execution.
SQLFreeStmt stops processing associated with a specific statement, closes any open cursors associated with the statement, discards pending results, or, optionally, frees all resources associated with the statement handle.
SQLGetData retrieves data for a single column in the result set, or for a single parameter after SQLParamData returns SQL_PARAM_DATA_AVAILABLE. You can call it multiple times to retrieve variable-length data in parts.
SQLTables returns the list of table, catalog, or schema names, and table types, stored in a specific data source. The driver returns the information as a result set.
Unsupported ODBC Functions
OmniSciDB does not support the following ODBC functions at this time.
Unsupported ODBC Features
OmniSciDB does not support the following ODBC features at this time.
Multiple result sets
Schemas (any client can see tables and views created by any user without restriction)
Rollbacks, checkpoints, or any other type of database recovery
Installing the Power BI Data Connector
To use the OmniSci analytics engine, the Power BI Desktop connects via the OmniSci ODBC driver in DirectQuery mode. (Power BI has two modes: direct and import.)
The Power BI Data Connector is distributed with the ODBC driver; install it to the directory that the Power BI desktop instance defines for custom connectors. To connect in DirectQuery mode, the ODBC driver connection needs to be "wrapped" in the supplied Power BI Data Connector.
Install the Connector
To install the connector, copy OmniSciPBIC.pqx to the Power BI Desktop custom connectors folder. For a standard Power BI install, this directory is \Users\<user name>Documents\Power BI Desktop\Custom Connectors. You might need to create the directory.
OmniSciPBIC.pqx is a signed file. For the signature to be trusted, the thumbprint of the signing certificate needs to be added to the "TrustedCertificateThumbprints” registry entry in the HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Power BI Desktop path. The thumbprint of the signing key is 29A21397B8169B153577806E3C43A502D944BC99.