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:
ODBC 3.5
Unnamed parameters, as in, INSERT INTO t (a, b) VALUES (?, ?), as used by very many applications. Unnamed parameters can be freely mixed with named parameters. There is no requirement to use either only named or only unnamed.
A wide variety of binding options for better performance.
Dynamic scrollable cursors. This can significantly improve performance and experience with third-party applications accessing .map file data via ODBC.
Relative scrolls for dynamic cursors.
Relative and absolute scrolls for static cursors. Static cursors cache returned data on the fly.
Opening a .map file in read-only mode, via a dialog option. Using read-only mode allows connecting to the same .map file concurrently from multiple processes.
Connecting to a data source inside a .map file, via a dialog option. The user has to specify the name of the data source. This allows using the ODBC driver to access data in any format supported by Manifold.
Delayed binding of parameters in parameter queries. This solves many issues with traditional office applications like Microsoft Access, which may bind parameters either before or after asking to compile a parameter query, depending on the scenario.
Updating records using cursors. This allows making changes to linked tables from Manifold 8 and other clients that do updates via cursors.
Inserting and deleting records using cursors.
Streaming writes of record values. This extends the number of applications that can use the driver in read-write mode.
Datetime parameters.
Named parameters and mixes of named and unnamed parameters when running query statements.
When asked, return of statistics for an index (a fairly rarely used ODBC function).
BTREENULL indexes.
Use in the context of a third-party application, when that application initializes Manifold programmatically by creating an instance of the Manifold.Root object, and then tries to work with a data source that is a .map file linked using the Manifold ODBC driver.
Simultaneous installation of Manifold ODBC drivers for published Manifold releases as well as cutting edge Manifold experimental builds. The ODBC driver registers under a different name for experimental builds.
Operation when invoked from within Manifold. Normally Manifold would connect directly to a .map file; however, allowing the load and enabling the driver to coexist in the same space as Manifold allows testing scripts that use the driver and also allows pass-through links from non-Manifold data sources.
The ODBC driver allows connecting to a new temporary database using DRIVER=...;DBTYPE=TEMP; in the connection string.
The ODBC driver supports extra connection options required by newer versions of Excel 365.
For enhanced Manifold System Release 8 interoperability, the Manifold ODBC driver:
Provides read/write connections to a Manifold Release 8 format .map file. That is an upgrade from the Manifold System Release 8 ODBC driver, which is read-only.
Recognizes Manifold 8 as a connecting application and enters a special compatibility mode which provides for better interoperability with Manifold 8. The version of Manifold 8 must be at least 8.0.30.
Automatically converts Manifold geoms to the format supported by Manifold 8, if used from Manifold 8.
Overrides metadata exposed to Manifold 8 with automatically generated values for drawings. If a particular table in the .map file operated by the driver contains multiple geometry fields with drawings bound to each, Manifold 8 will see one of them (a limitation of Manifold 8).
Exposes coordinate systems for drawings to Manifold 8.
Returns keys for an inserted record to Manifold 8. This allows records inserted from Manifold 8 to appear in components immediately without having to refresh component data.
Components with spaces in names are not supported, a consequence of the intricacies of the ODBC protocol. These may be accessed by running a query, but not directly.
Attempting to use the ODBC driver to connect to a .map file which is already opened in another connection in an incompatible mode (read-only vs read-write) fails with a descriptive error message.
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:
Not installed - The ODBC driver has not been installed
Installed - The ODBC driver has been installed into the same location from which Manifold has been launched
Installed with mismatching options - The ODBC driver has been installed into current location, but the install is partial, or damaged, or out-of-date, and thus must be reinstalled/updated.
Installed in different location - The ODBC driver has been installed but into a location different from that from which Manifold has been launched. Typical examples are an ODBC installation that is part of an official build with the current Manifold session launched from a portable installation, or an ODBC installation from one portable installation that is different from the portable installation from which Manifold has been launched.
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.
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 9.0.177.1, the Manifold ODBC driver will be identified as version number 9.00.177.1. 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.
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.