Example: Create an ODBC Data Source with Windows

This example provides the step by step procedure to create an ODBC Data Source using the Microsoft Windows ODBC Data Source Administrator dialog that is built into Windows.   The example uses Windows 10.

 

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.  See the Installations topic for a quick guide to using portable installations and to installing the Manifold ODBC driver. 

About ODBC Data Sources

Windows maintains a list of ODBC data sources to which ODBC-capable applications can connect.   To enable an ODBC-aware application to connect to a Manifold data source we must create an ODBC data source in that list.   An ODBC data source is also called an ODBC DSN (for Data Source Name).   It encapsulates necessary information for the connection, such as a what driver to use, the data source that driver can expose and various options the driver allows.

 

There are two ways to create an ODBC data source:

 

 

 

This example shows how to use the Microsoft Windows tool to create an ODBC data source.  In both ways of creating a new ODBC data source both Microsoft dialogs and Manifold dialogs will appear.  Both Release 8 and the Windows tool will call Microsoft dialogs as part of the process, and those in turn will utilize the Manifold ODBC driver we specify.   As part of the creation process, the Manifold driver will open its own dialogs to enable specification of options supported by Manifold.  

 

The primary choice offered by the Manifold ODBC driver is whether the new ODBC data source will connect to items stored in the .map project file, or whether the new ODBC data source will connect through the .map project file to provide access to external data sources that are linked into the project.    

 

Manifold has the highly unusual capability to act as an intermediary between an ODBC connection coming into Manifold and some other, external data source that is linked into the project.   Applications which are ODBC-capable can utilize that Manifold capability by connecting with ODBC through Manifold to other data sources for which no ODBC driver is available.

 

We first look at the simpler case of connections to items stored in the project.   The second part of this example topic discusses connections through the project to external data sources linked into the project.

Create an ODBC Data Source for Data Stored in the Project

We begin by taking a look at the Manifold project for which we will create an ODBC data source.   We will show the simple case of creating an ODBC data source to connect to components stored in the .map file.   

 

This is the same project that is used in the Example: Connect to Manifold from QGIS topic.

 

To fit into this documentation, illustrations show an artificially small Manifold desktop, with only a few panes, docked to the right side.   In real life we use a much larger Manifold desktop, and all panes would be turned on, with some panes docked to the left and others docked to the right.

 

 

 

 

We open the project to take a look at the data we will use.  The regions drawing shows regions of France before recent reorganization.  France has a "squashed" look because the drawing is in latitude / longitude coordinate system.

 

 

 Close the project, so that later when we use Windows ODBC data source dialogs there will be no issues with the file being in use.

 

In Windows, launch the Control Pane.

 

 

Click on the Administrative Tools choice.

 

 

In the Administrative Tools folder, double-click on the ODBC Data Sources (64-bit) applet.

 

 

The ODBC Data Source Administrator dialog shows all ODBC data sources on our Windows system.   User DSNs are specific to a particular user.  When we login as a user that does not have Administrator privileges we can create them for use only by that same user login.     We click the Add button.

 

 

 

In the Create New Data Source dialog we click on the Manifold 9.0 Project Driver choice.   The dialog shows the various ODBC drivers available for use in creating an ODBC data source.   

 

The Manifold 9.0 Project Driver choice we clicked is for the ODBC driver installed by the official release for Manifold System.     Since Release 9 uses Radian technology both 9 and Radian Studio use the same ODBC driver and thus it is called the "Manifold 9.0" driver in both cases.   

 

The Manifold 9.0 Project Driver (Experimental) choice appears if we have installed an experimental, cutting edge build of Manifold.  See the Installations topic for info on cutting edge builds.

 

The Manifold Project Driver is the Release 8.00 ODBC driver, used to connect via ODBC to Release 8.00 .map projects.

 

Click the Finish button (an odd name for the button since we are not yet finished, but this is a standard Microsoft dialog that may appear in other settings).

 

 

That launches a dialog raised by the Manifold ODBC driver.    We provide a Name for the new data source.  An ideal name is short, contains no spaces (to avoid confusing older or less capable applications) and many months or years after the ODBC data source was created will remind us what that source connects to.  In this case we use the name manifold_regionswkb to remind us later on this is a Manifold file that has something to do with regions and WKB.

 

Our intent is to create an ODBC Data Source for items stored in the .map project file, so check the Use MAP file radio button.    The other option, discussed in the second part of this example topic, would create an ODBC Data Source that connects through the .map file to a data source external to Manifold that is linked into the .map file.

 

To choose the .map file to use, we press the [...] Browse button.

 

 

The Manifold driver opens a standard Windows file-browsing dialog that allows us to navigate to the .map file we want to use.  We click on the .map file desired to highlight it and then press Open.

 

 

Back in the Manifold ODBC dialog we confirm the Name and the .map file desired are correct, and then we press OK.  

 

 

Back in the Microsoft ODBC Data Source Administrator dialog we see a new ODBC data source has been added using the name that we specified.  Done!

 

From now on, when an ODBC-aware application launches whatever dialogs it uses to connect to an ODBC data source, the manifold_regionswkb DSN will be available, at least if we are logged in as that same user since we have created a User DSN and not a system-wide DSN for all users.

Create an ODBC Data Source for External Data Linked into the Project

We now will create an ODBC Data Source that connects through the .map file to a data source external to Manifold that is linked into the .map file.

 

In this case we will use a Manifold project, shown below, where we have created two data sources that are Manifold connections to ESRI gdb file databases.   Both are example GDB data sets published by ESRI, one of which, called Gas in the project below, contains gas utility structures and the other, called Water in the project below, contains water utility distribution structures.   Both are in Naperville, Illinois, in the US.

 

 

In the illustration above we have expanded the Water file geodatabase source to show some of the many drawings and tables in that GDB.   The data for both data sources resides in ESRI file geodatabase files, not in the Manifold .map project.

 

 We close the project, to avoid any collisions with files being in use while we set up ODBC data sources.

 

In the Windows Control Pane  we navigate into the Administrative Tools folder and double-click on the ODBC Data Sources (64-bit) applet.

 

 

The list of ODBC Data Sources includes the manifold_regionswkb DSN we created in the first part of this example topic.   We click the Add button.

 

 

In the Create New Data Source dialog we click on the Manifold 9.0 Project Driver choice and then click Finish (again thinking that an odd name for the button...).   

 

 

In the Manifold ODBC driver we specify manifold_waterGDB as the Name for the new data source.  

 

We check the Use data source in MAP file radio button.   Since we still must specify the .map file to use, we press the [...] Browse button.

 

 

We navigate to where the linked-gdbs.map  project file is stored, click it and then press the Open button.

 

 

 

We specify the name of the data source desired within the .map file, in this case Water.   Press OK.

 

 

 

Back in the Microsoft ODBC Data Source Administrator dialog we see a new ODBC data source has been added using the name that we specified, manifold_waterGDB.   Press OK.  Done!

 

From now on, when an ODBC-aware application launches whatever dialogs it uses to connect to an ODBC data source, the manifold_waterGDB DSN will be available, at least if we are logged in as that same user since we have created a User DSN and not a system-wide DSN for all users.

 

Notes

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.169.7, the Manifold ODBC driver will be identified as version number 9.00.169.07.   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.

 

Nothing Done in Manifold - All of the procedures in this topic have been done in Windows dialogs.   We have only opened Manifold to illustrate the contents of the projects.   In the case of the linked_gdbs project we needed to do that to recall the name of the source within the project, called Water.  But other than taking a look into the project we used no dialogs within Manifold to create the ODBC data sources within Windows.

 

Access connecting to Manifold - When linking Manifold tables into Access using the Manifold ODBC driver, please review the notes on Access in the DBMS Data Sources - Notes topic.

 

Double quotes are OK - The Manifold ODBC driver allows enclosing identifiers in double quotes, to work around a limitation in newer versions of QGIS.

 

Historical regions - The illustrations in this topic use data from the US government, which show the regions of France as they were before 1 January 2016, when regions in France were reduced from 22 to 13.

 

See Also

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 to an ESRI GDB usng GDAL/OGR  -  Instead of using Manifold's built-in ability to connect to modern ESRI GDB file geodatabases, use the Manifold GDAL/OGR dataport to take advantage of the GDAL library's ability to connect to deprecated GDB formats.

 

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.