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.

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.

 

eg_configure_odbc_win01_00.png

 

Close the project, so that later when we connect to it there will be no issues with the file being in use.

 

In Windows, launch the Control Panel.

 

eg_configure_odbc_win01_01.png

 

Click on the Administrative Tools choice.

 

eg_configure_odbc_win01_02.png

 

In the Administrative Tools folder, double-click on the ODBC Data Sources (64-bit) applet.   If we are working only with 32-bit programs, we could use the ODBC Data Sources (32-bit) applet.

 

eg_configure_odbc_win01_03.png

 

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.

 

eg_configure_odbc_win01_04.png

 

 

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).

 

eg_configure_odbc_win01_05.png

 

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 radian_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.

 

eg_configure_odbc_win01_06.png

 

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.

 

eg_configure_odbc_win01_07.png

 

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

 

eg_configure_odbc_win01_08.png

 

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 radian_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.

 

eg_configure_odbc_win01_09.png

 

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.

 

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

 

eg_configure_odbc_win01_10.png

 

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

 

eg_configure_odbc_win01_11.png

 

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...).   

 

eg_configure_odbc_win01_12.png

 

In the Manifold ODBC driver we specify water_distribution 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.

 

eg_configure_odbc_win01_13.png

 

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

 

 

eg_configure_odbc_win01_14.png

 

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

 

 

eg_configure_odbc_win01_15.png

 

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, water_distribution.  Done!

 

From now on, when an ODBC-aware application launches whatever dialogs it uses to connect to an ODBC data source, the water_distribution 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

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.