ODBC

Open Database Connectivity (ODBC) is a standard application programming interface (API) for accessing database management systems (DBMS). OmniSciDB supports ODBC connections.

The OmniSci ODBC installer tool filename has the following format: 

OmniSciInstall_<major_version>.<minor_version>.<minor_patch_version>.<odbc_driver_version>.<odbc_driver_minor_version>.
<odbc_patch_version>.<odbc_extra_info>.zip

For example: 

OmniSciInstall_4.4.1.3.11.002.0000.zip

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:

  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. Locate and open ODBC Data Source Administrator.
  7. Click Add....
  8. Enter the user Name (for example, omnisci), Platform (64-bit), and Driver (OmniSciDriver).
  9. 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 fetched (for example, 50).
    7. Enter the Max rows returned (for example, 10000).
    8. Select the Protocol TypeJSON/HTTP to connect to the OmniSci Web server using HTTP (or HTTPS if a certificate file is specified), or Binary to connect to the main OmniSci server port (using Thrift's binary protcol).
    9. To encrypt the connection, add the path to the OmniSci Server public certificate to the CA File field. The server must be running the appropriate encrypted service for the interface to connect to.
  10. Click Test.
  11. Wait for the “Success!” message. Click OK.

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/mapd_odbc_installer_linux_<version_number>.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 following properties/values in bold to values appropriate for your OmniSci installation. 
    [MapD]
Description=Sample 32-bit OmniSci Driver
Driver=/apps/odbc/mapd/libs/libODBC.so
Locale=en-US
PWD=HyperInteractive
UID=admin
HOST=localhost
PORT=6274
DATABASE=omnisci
    To configure encrypted communication with the server, add the following entries to the etc/odbc.ini file. The server must be running the appropriate encrypted service for the interface to connect. 
    [OmniSci]
.
.
.
protocol=<HTTP | BINARY>
SERVER_CA_CERT=<OmniSci_server_public_certificate_path>
    If SERVER_CA_CERT is not specified, the communication is not encrypted, and HTTP or nonencrypted binary is used.
  8. Using a text editor, edit the etc/odbcinst.ini file by changing the following properties/values in bold to values appropriate for your OmniSci installation. 
    [MapDDriver]
APILevel=1
ConnectFunctions=YYY
Description=Sample 64-bit OmniSci ODBC Driver
Driver=/apps/odbc/mapd/libs/libODBC.so
DriverODBCVer=03.80
SQLLevel=1
    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/mapd/ErrorMessages/en-US/ /apps/odbc/mapd/libs/
$ ls -l libs
total 224312
lrwxrwxrwx. 1 centos centos        36 Sep 20 14:47 en-US -> /apps/odbc/mapd/ErrorMessages/en-US/
-rwxrwxr-x. 1 centos centos 229692960 May 16 20:39 libODBC.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
+------------+
| INTEGER    |
+------------+
| 7009728    |
+------------+
SQLRowCount returns 1
1 rows fetched
SQL> quit
    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:

Function Description
SQLAllocHandle Obtains an environment, connection, statement, or descriptor handle.
SQLBindCol SQLBindCol binds application data buffers to columns in the result set.
SQLCloseCursor SQLCloseCursor closes a cursor that has been opened on a statement and discards pending results.
SQLColAttribute SQLColAttribute returns descriptor information for a column in a result set. Descriptor information is returned as a character string, descriptor-dependent value, or an integer value.
SQLColumns SQLColumns returns the list of column names in specified tables. The driver returns this information as a result set on the specified StatementHandle.
SQLConnect 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.
SQLCopyDesc SQLCopyDesc copies descriptor information from one descriptor handle to another.
SQLDescribeCol SQLDescribeCol returns the result descriptor for one column in the result set.
SQLDescribeParam SQLDescribeParam returns the description of a parameter marker associated with a prepared SQL statement. This information is also available in the fields of the IPD.
SQLDisconnect SQLDisconnect closes the connection associated with a specific connection handle.
SQLDriverConnect 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 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.
SQLExecute SQLExecute executes a prepared statement using the current values of the parameter marker variables, if any parameter markers exist in the statement.
SQLFetch SQLFetch fetches the next rowset of data from the result set and returns data for all bound columns.
SQLFreeHandle SQLFreeHandle frees resources associated with a specific environment, connection, statement, or descriptor handle.
SQLFreeStmt 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.
SQLGetConnectAttr SQLGetConnectAttr returns the current setting of a connection attribute.
SQLGetData 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.
SQLGetDescField SQLGetDescField returns the current setting or value of a single field of a descriptor record.
SQLGetDescRec SQLGetDescRec returns the current settings or values of multiple fields of a descriptor record. The fields returned describe the name, data type, and storage of column or parameter data.
SQLGetDiagField SQLGetDiagField returns the current value of a field of a record of the diagnostic data structure that contains error, warning, and status information.
SQLGetDiagRec SQLGetDiagRec returns the current values of multiple fields of a diagnostic record that contain error, warning, and status information.
SQLGetEnvAttr SQLGetEnvAttr returns the current setting of an environment attribute.
SQLGetFunctions SQLGetFunctions returns information about whether a driver supports a specific ODBC function. This function is implemented in the Driver Manager and potentially in drivers.
SQLGetInfo SQLGetInfo returns general information about the driver and data source associated with a connection.
SQLGetStmtAttr SQLGetStmtAttr returns the current setting of a statement attribute.
SQLGetTypeInfo SQLGetTypeInfo returns information about data types supported by the data source.
SQLMoreResults SQLMoreResults determines whether more results are available on a statement containing SELECT, UPDATE, INSERT, or DELETE statements and, if so, initializes processing for those results.
SQLNativeSql SQLNativeSql returns the SQL string as modified by the driver. SQLNativeSql does not execute the SQL statement.
SQLNumParams SQLNumParams returns the number of parameters in an SQL statement.
SQLNumResultCols SQLNumResultCols returns the number of columns in a result set.
SQLRowCount SQLRowCount returns the number of rows affected by an UPDATE, INSERT, or DELETE statement.
SQLSetConnectAttr SQLSetConnectAttr sets attributes that govern aspects of connections.
SQLSetDescField SQLSetDescField sets the value of a single field of a descriptor record.
SQLSetDescRec The SQLSetDescRec function sets multiple descriptor fields that affect the data type and buffer bound to a column or parameter data.
SQLSetEnvAttr SQLSetEnvAttr sets attributes that govern aspects of environments.
SQLSetStmtAttr SQLSetStmtAttr sets attributes related to a statement.
SQLStatistics SQLStatistics retrieves a list of statistics about a single table and the indices associated with the table. The driver returns the information as a result set.
SQLTables 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.

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

Installing ODBC on Linux

Follow these steps to configure OmniSciDB ODBC connections.

  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.omnisci.com/odbc/omnisci_odbc_installer_linux_<version_number>.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. Using a text editor, edit the etc/odbc.ini file by changing the following properties/values in bold to values appropriate for your OmniSci installation. 
    [OmniSci]
Description=Sample 32-bit OmniSci Driver
Driver=/apps/odbc/omnisci/libs/libODBC.so
Locale=en-US
PWD=HyperInteractive
UID=omnisci
HOST=localhost
PORT=6274
DATABASE=omnisci
    Note Your odbc.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.
  8. Using a text editor, edit the etc/odbcinst.ini file by changing the following properties/values in bold to values appropriate for your OmniSci installation. 
    [OmniSciDriver]
APILevel=1
ConnectFunctions=YYY
Description=Sample 64-bit OmniSci ODBC Driver
Driver=/apps/odbc/omnisci/libs/libODBC.so
DriverODBCVer=03.80
SQLLevel=1
    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/ErrorMessages/en-US/ /apps/odbc/omnisci/libs/
$ ls -l libs
total 224312
lrwxrwxrwx. 1 centos centos        36 Sep 20 14:47 en-US -> /apps/odbc/omnisci/ErrorMessages/en-US/
-rwxrwxr-x. 1 centos centos 229692960 May 16 20:39 libODBC.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
+------------+
| INTEGER    |
+------------+
| 7009728    |
+------------+
SQLRowCount returns 1
1 rows fetched
SQL> quit
    Note If you receive an error message, your connection is not successfully configured. Check the connection properties in /etc/odbc.ini.