Manifold ODBC Driver

The Manifold ODBC driver for Release 9 enables other applications to connect to data within Manifold projects, and also to connect through Manifold to other data sources linked into a Manifold project, even if the other application does not have the ability directly to connect to such other data sources.  


The Manifold ODBC driver can also connect to any file format supported by Manifold, in addition to .map project files.  This allows third party applications to use Manifold's ability to connect to an extremely wide range of GIS file formats.


The Manifold ODBC driver supports:



For enhanced Manifold System Release 8 interoperability, the Manifold ODBC driver:






The Manifold ODBC driver is automatically installed when installing Manifold using a Windows Installer installation package, but it is not automatically installed when running a portable installation.  When using a portable installation we must install the Manifold driver using the Help - About dialog.   We only need do that once for any given Manifold installation.  


The report in the Help - About dialog shows if ODBC has been installed, whether it is an ODBC driver from an base build or a cutting edge build.  The accessory button allows installation/update/repair and removal.    ODBC status can be reported as:



If our login does not have Administrator rights, pressing the Install or Remove button to install or uninstall the ODBC driver will launch a dialog that allows us to proceed with Administrator rights.  



If the Manifold ODBC driver has not been installed, the Help - About dialog will have the Install button enabled.   Press Install.



The ODBC status will immediately switch to Installed, with the button caption changed to Remove.  Press OK.    


If we have multiple Manifold sessions launched from the same installation, installing ODBC on any of them will install it for all of them, with no need to restart.  

Command Line Installation

The ODBC driver may be installed or uninstalled from a Windows command line.    Running ODBC commands requires administrator privileges.


To install the ODBC driver, run


manifold.exe -odbcupdate


To uninstall the ODBC driver, run


manifold.exe -odbcdelete



Square brackets or reverse quotes are OK  -  Manifold standard style is to enclose identifiers in square brackets, as in SELECT [name], [address] FROM [contacts];  To provide better compatibility with ODBC the query engine also allows enclosing identifiers in reverse quote ` characters.  ODBC as a technology wants to be able to enclose identifiers using the same character on both sides.   A construction such as [name] uses two different characters, a left square bracket [ and a right square bracket ] while `name` uses the same character, a reverse quote ` character on both sides.


Fast repeat connections - The Manifold ODBC driver is fast at making repeat connections when used from third-party code.  Steady improvement has resulted in effective handling of shutdown / re-initialize cycles.  Those happen fairly frequently with third-party applications which first invoke the driver to set up a data source, finish that and release the driver, then make a connection to the set-up data source, re-initializing the driver again, and so on.


Manifold ODBC Driver Version - The version number of the Manifold ODBC driver will be the equivalent of the version number of the Manifold build that installed it.  For example, if we have installed the Manifold ODBC driver using Manifold System Release 9 Edge build, the Manifold ODBC driver will be identified as version number   Manifold ODBC drivers installed by Cutting Edge builds will be identified as Manifold 9.0 Project Driver (Experimental) while Manifold ODBC drivers installed by Official builds will be identified as Manifold 9.0 Project Driver.


Linking Manifold tables into Microsoft Access - We can use the Manifold ODBC driver to enable Microsoft Access to connect to data in a Manifold .map file.  


The catch is that almost all Manifold projects use 64-bit integer fields (for example, the mfd_id key field is always a 64-bit integer) and Microsoft Access has only recently started supporting 64-bit integer fields on an opt-in basis:  we must turn on that capability within Access before connecting to Manifold.  Note that opting to use 64-bit integers in Access will make the resulting Access files incompatible with older versions of Access and other applications.


To link tables with 64-bit integer fields from Manifold into Access, in Access create a new blank database.  Next, choose File - Options, Current Database. Scroll all the way down and check Support BigInt data Type for Linked/Imported Tables.   You can then link tables from a Manifold ODBC data source.   


You can also repair existing links:  turn the same option on in Access, and then invoke External Data - Linked Table Manager, select the tables linked from the Manifold data source and click OK to refresh them.


When linking Manifold tables into Access, Manifold tables with a primary key on text fields should only have a primary key if the text field is a VARCHAR data type.   Tables with a primary key on text fields that are NVARCHAR (Unicode) data type should not be used with Access.   


The source of the above problem is an issue in Access:  When Access asks the Manifold ODBC driver to return data for the field in its native format, for NVARCHAR fields the driver returns Unicode.  However,  because Access expects the data to be ANSI text the interaction fails.  That only happens with key fields.   When accessing non-key fields Access either asks specifically for Unicode text, in which case Manifold returns Unicode and then Access treats it as Unicode correctly, or Access asks specifically for ANSI text, in which case Manifold converts the Unicode text to ANSI and returns ANSI, which Access then treats as ANSI correctly.


The issue of how Access treats key fields is built into Access and cannot be patched by Manifold.   Other products interacting with Access experience the same issue.   Some try to deal with it by exposing user-level options to force data in key fields to ANSI, either always or when Access asks to return their values in the format native to the field.   That is an unsatisfactory workaround, since it loses data and ruins uniqueness, because the conversion from Unicode to ANSI is lossy.


A possible workaround for Manifold users is creating a computed VARCHAR field in tables to be used in Access, building an index on that computed field and dropping the index on the NVARCHAR field.   The issue in Access has been known for a long time.  Word in the Microsoft developer community has it the issue remains because there is an external component involved which is difficult to update.


See Also

Example: Create an ODBC Data Source with Windows - How to create an ODBC data source (a DSN) using dialogs built into Windows 10.


Example: Connect to Manifold from Release 8  - Step by step procedure to connect from Manifold System Release 8 to a Manifold .map file using Manifold's ODBC driver.


Example: Connect to an ESRI GDB File Geodatabase - Connect Manifold to an ESRI GDB file geodatabase, display the contents, make a selection in the GDB and overlay in a map.


Example: Connect Through Manifold ODBC to a Third Party  - With Release 8, use an ODBC connection to a Manifold .map to connect through the .map project to a third party, external data source, an ESRI GDB file geodatabase.  We use Manifold facilities as an intermediary to give Release 8 capabilities it does not have on its own, to link into data stored within an ESRI file geodatabase.


Example: Connect LibreOffice Through Manifold to an ESRI GDB - A companion example topic to the Example: Connect Through Manifold ODBC to a Third Party  topic.  Shows how to connect LibreOffice Base, the database part of LIbreOffice, through Manifold to link an ESRI GDB file geodatabase table into LibreOffice.


Example: Connect to Manifold from QGIS - Step by step procedure to connect from QGIS 2.8.9 to a Manifold .map file using Manifold's ODBC driver.