ODBC

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:

1. Run the OmniSci Installer Tool provided by your OmniSci Sales Representative.
2. Enter the path to the ODBC driver (default is C:\OmniSci\ODBC\bin\).
3. Click OK.
4. Wait for the “SUCCESS!” message. Click OK.
5. Open your Windows control panel.
6. Open either the 32-bit or 64-bit version of ODBC Data Source Administrator, depending on the application you are connecting to the driver.
7. Click Add....
8. Enter the user Name (for example, omnisci), Platform, and Driver (OmniSciDriver).
9. In OmniSci ODBC Driver DSN Setup:
   1. Enter an optional Description for your data source.
   2. Enter the User (for example, OmniSci).
   3. Enter the Password for your data source.
   4. Enter the Host:Port values (for example, myhost.omnisci.com:6274).
   5. Enter the Database name (for example, omnisci).
   6. Enter the Max rows returned (for example, 10000).
   7. Select the Protocol Type to connect to the OmniSci Web server: HTTP, HTTPS, Binary, or Binary Encrypted.

      Note	If you select HTTPS or Binary Encrypted, you must enable encrypted connections using the Microsoft Management Console.

   8. 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.
   9. In Connection Timeout, enter the time interval (in seconds) used to terminate failed connection attempts.
10. Click Test.
11. 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:

1. Open the Microsoft Management Console (MMC).
2. On the File menu, select Add/Remove Snap-ins.
3. In the Available snap-ins list box, select Certificates.
4. In the Certificates snap-in box, select Computer Account, and then click Next.
5. Select Local Computer, and click Finish, and in the Add or Remove Snap-ins window, click OK.
6. In the left pane of the Console Root window, right-click the Trusted Root Certificate Authorities folder, click All tasks, and then click Import....
7. In the Certificate Import Wizard, click Next.
8. 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.

1. If required, install unixODBC, which you use to test that the OmniSci ODBC driver configuration is working correctly:
   • For RHEL/Centos:

     $ sudo yum install unixODBC
     

   • For Ubuntu/Debian:

      $ sudo apt-get install unixodbc
     

   Type y when prompted to install the package.

2. Validate that isql is installed by running it from the shell:

   $ isql

   If installed correctly, you see output showing the isql syntax and options.

3. Create a folder for the ODBC driver:

   $ sudo mkdir -p /apps/odbc/omnisci/
   $ sudo chmod 777 /apps/odbc/omnisci

4. Change to the folder you just created:

   $ cd /apps/odbc/omnisci/
   $ pwd /apps/odbc/omnisci

5. Download the OmniSci Linux ODBC driver:

   $ curl -O https://builds.mapd.com/odbc/OmniSciODBC-5.1.2-20200302-a280de1b9a-Linux.tar.gz -u <username>:<password>

6. Extract the contents of the file you just downloaded:

   $ tar xvf mapd_odbc_installer_linux_<version_number>.tar.gz

7. 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]
   OmniSci=OmniSciDriver
   
   [OmniSci]
   Description=64-bit OmniSci Driver
   Driver=/apps/odbc/omnisci/lib/libOmniSciODBC.so
   Locale=en-US
   PWD=<USER_PASSWORD>
   UID=<USER_ID>
   # HOST can contain ':port number' as in 'localhost:6274'.
   HOST=<SERVER_HOSTNAME>
   PORT=<SERVER_PORT>
   DATABASE=<DATABASE_NAME>
   # Protocol can be BINARY or HTTP.
   # If not supplied, the protocol defaults to BINARY.
   PROTOCOL=<PROTOCOL>
   # 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.
   SERVER_CA_CERT=<PATH_TO_SERVER_CA_CERT>
   # Provide the time interval (in seconds) used to terminate failed connection attempts. 
   CONNECTION TIMEOUT=<seconds>

   Note	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.

8. Edit the /etc/odbcinst.ini file by changing the following properties/values in bold to values appropriate for your OmniSci installation.

   [ODBC] 
   Trace = yes 
   TraceFile=<FULL_NAME_ODBC_TRACE_FILE> 
   
   [ODCB Drivers] 
   OmniSciDriver=Installed 
   
   [OmniSciDriver]
   APILevel=1 
   DriverODBCVer=04.80 
   SQLLevel=1 
   ConnectionFunction=YYY 
   Description=OmniSciDriver
   Driver=/apps/odbc/omnisci/libs/libOmniSciODBC.so 
   LogLevel=6 
   LogPath=<PATH_TO_OMNISCI_LOGFILE>

   Note	Your odbcinst.ini file might be empty or might already contain other entries. If your file contains other entries, add the new entries to the end of the file and do not overwrite existing entries.

9. Create a symbolic link to the error messages folder for the OmniSci driver library:

   $ ln -s /apps/odbc/omnisci/DataAccessComponents/en-US/ 
   /apps/odbc/omnisci/libs/
   $ ls -l /apps/odbc/omnisci/libs/
   total 229256
   lrwxrwxrwx 1 mapd mapd        60 Feb  5 12:54 en-US -> 
   /apps/odbc/omnisci/DataAccessComponents/ErrorMessages/en-US/
   -rwxrwxr-x 1 mapd mapd 234750688 Oct 11 15:18 libOmniSciODBC.so

10. Test the installation and connection using isql:

    $ isql -v omnisci
    +---------------------------------------+
    | Connected!                            |
    |                                       |
    | sql-statement                         |
    | help [tablename]                      |
    | quit                                  |
    |                                       |
    +---------------------------------------+
    SQL> select count(*) cnt from flights_2008_7M;
    +---------------------+
    | BIGINT              |
    +---------------------+
    | 7009728             |
    +---------------------+
    SQLRowCount returns 1
    1 rows fetched
    SQL> 

    Note	If you receive an error message, your connection is not successfully configured. Check the connection properties in /etc/odbc.ini.

Supported ODBC Functions

OmniSciDB supports the following Tier 1 (Core) ODBC functions:

Unsupported ODBC Functions

OmniSciDB does not support the following ODBC functions at this time.

SQLBindParameter
SQLBrowseConnect
SQLCancel
SQLCancelHandle
SQLColumnPrivileges
SQLEndTran
SQLExtendedFetch
SQLFetchScroll
SQLForeignKeys
SQLGetCursorName
SQLSetCursorName
SQLSetPos
SQLSpecialColumns
SQLParamData
SQLPrepare
SQLPrimaryKeys
SQLProcedureColumns
SQLProcedures
SQLPutData
SQLTablePrivileges

Unsupported ODBC Features

OmniSciDB does not support the following ODBC features at this time.

• Batch statements
• Multiple result sets
• Domains
• Rules
• Database procedures
• Indexes
• Keys
• Transactions
• Schemas (any client can see tables and views created by any user without restriction)
• Rollbacks, checkpoints, or any other type of database recovery