ODBC¶
Open Database Connectivity (ODBC) is a standard application programming interface (API) for accessing database management systems (DBMS). MapD Core Database supports ODBC connections.
For assistance with downloading MapD ODBC software and utilities, contact your MapD 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. MapD provides an ODBC connection utility to help you get started.
To configure your ODBC datasource:
- Run the MapD Installer Tool provided by your MapD Sales Representative.
- Enter the path to the ODBC driver (default is
C:\MapD\ODBC\bin\
). - Click OK.
- Wait for the “SUCCESS!” message. Click OK.
- Open your Windows control panel.
- Locate and open ODBC Data Source Administrator.
- Click Add....
- Enter the user Name (for example,
mapd
), Platform (64-bit), and Driver (MapDDriver). - ODBC Driver DSN Setup.
a. Enter an optional Description for your data source.
b. Enter the User (for example, MapD).
c. Enter the Password for your data source.
d. Enter the Host:Port values (for example,
myhost.mapd.com:9091
). e. Enter the Database name (for example,mapd
). f. Enter the Max rows fetched (for example,50
). - Click Test.
- Wait for the “Success!” message. Click OK.
Supported ODBC Functions¶
MapD Core Database 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¶
MapD Core Database 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¶
MapD Core Database 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 MapD Core Database ODBC connections.
Verify that your Linux installation supports unixODBC.
Locate the file
libodbc.so
. It is typically stored in one of the following directories:/lib
,/usr/lib
, or/usr/local/lib
. If you cannot locate the file, download unixODBC for free at unixODBC.org.Download the MapDODBC.tar file into a scratch directory. The archive contains a set of files and directories similar to the following:
config/ config/.simba.ultralight.ini config/odbc.ini libs/ libs/libODBC.so ErrorMessages/ ErrorMessages/CBMessages.xml ErrorMessages/CLIDSIMessages.xml ErrorMessages/ClientMessages.xml ErrorMessages/CSCommonMessages.xml ErrorMessages/JNIDSIMessages.xml ErrorMessages/MDMessages.xml ErrorMessages/NetworkMessages.xml ErrorMessages/ODBCMessages.xml ErrorMessages/OLEDBMessages.xml ErrorMessages/ServerMessages.xml ErrorMessages/SQLEngineMessages.xml
The
odbc.ini
file configures the location and name of your ODBC DSN (Data Source Name) definition. You can renameodbc.ini
to.odbc.ini
(note the leading dot) in your home directory, or else export theODBCINI
environment variable to the path and location ofodbc.ini
.Copy the
libs/libODBC.so
file to the directory of your choice.Copy the files in the ErrorMessages directory to the directory of your choice.
The
odbc.ini
file contains a sample DSN (Data Source Name) namedmapd
.Edit the file so that the
Driver
attribute contains the path and location of thelibODBC.so
file from MapD.Enter the
PWD
,UID
,HOST
,DATABASE
andMAX_ROWSX
attributes according to the specifications of your MapD data source. The following is a working sampleodbc.ini
file:[ODBC Data Sources] MapD=MapDDriver [MapD] Description=Sample 64-bit MapD Driver Driver=/opt/Mapd/ODBC/libODBC.so PWD=MyPassword # Enter your real password here UID=mapd HOST=localhost:9091 DATABASE=mapd MAX_ROWS=-1
Copy the
.simba.ultralight.ini
file to your home directory. Change theErrorMessages
attribute to the location of the ErrorMessages files copied from theErrorMessages
directory you expanded from the archive. The following is a samplesimba.ultralight.ini
file:## Note that this default DriverManagerEncoding of UTF-32 is for iODBC. ## unixODBC uses UTF-16 by default. ## If unixODBC was compiled with -DSQL_WCHART_CONVERT, then UTF-32 is the correct value. ## Execute 'odbc_config --cflags' to determine if you need UTF-32 or UTF-16 on unixODBC DriverManagerEncoding=UTF-16 DriverLocale=en-US ErrorMessagesPath=/opt/mapd/ODBC/ErrorMessages LogLevel=0 LogNamespace= LogPath=
Test your connection. UnixODBC has an
isql
utility that lets you query the MapD Core Database. When you connect to themapd
DSN, you receive the following prompt:isql -v mapd +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
Installation is complete. You can begin using your ODBC connection.