MapInfo Pro

 View Only
Back to discussions
Expand all | Collapse all

MapInfo Monday: Connecting to an ODBC Database

  • 1.  MapInfo Monday: Connecting to an ODBC Database

    Employee
    Posted 07-31-2023 06:12

    Happy #MapInfoMonday

    Most users rely on the native MapInfo tables when they need to store data. These files are easy to work with and quite performant in MapInfo Pro. The latest extended version of the native MapInfo table format can hold massive amounts of data too whereas the older format was limited to 2GB. The native MapInfo tables do have a shortcoming if several users need to edit the table at the same time.

    This is where a spatial database could prove helpful. Besides allowing several users to edit the data simultaneously, it also brings also benefits to the table such as access to the data from non-GIS applications and the benefit of using a database with features like security, triggers, views, and more.

    In this article and a few to follow, I'll introduce how you can start using data in a database from within MapInfo Pro. In this first article, we will look at connecting to an ODBC database from MapInfo Pro. We will create an ODBC Data Source and use this to connect to the database. In an upcoming article, I will discuss how you can prepare the database to be used from MapInfo Pro and how you can upload spatial data into the database. And finally, we will look at accessing data stored in a database.

    Let's get started

    What is ODBC?

    ODBC - or Open Database Connectivity - is a standard API for accessing databases from applications. The idea behind ODBC is to make it possible to easily change the database used by an application without having the rewrite the logic of the application.

    This was done by introducing a translation layer between the application and the database: the ODBC driver. The application "talks" to the ODBC driver which translates the request into a request or query that the database understands. And similarly, the response from the database is passed back to the application in a standard way.

    When you start working with databases and ODBC, you need to understand the different terms. First, you have a database. This is also often referred to as a DBMS, a DataBase Management System. Next, there is the ODBC Driver that knows how to talk to a specific database. This driver mostly needs to be installed onto your system. Using this ODBC driver, you can create a data source that basically describes what database to connect to using which ODBC Driver. And finally, you connect to the database using the data source from within your application, in this case, MapInfo Pro.

    I think this is also a good spot to mention that MapInfo Pro also uses other ways to connect to specific databases.

    For Oracle Spatial, we recommend - or require - you to use Oracle Call Interface, OCI. You can read more about connecting the Oracle Spatial in the MapInfo Pro User Guide in the chapter Creating a Data Source Connection to Oracle.

    MapInfo Pro has built-in support for SQLite databases and for GeoPackage databases. You don't need to use ODBC to connect to these.

    Read more about ODBC on Wikipedia.

    Creating an ODBC Data Source

    I'm going to assume that you have 1) MapInfo Pro installed and 2) a database system installed too. In my example, I will use PostgreSQL with PostGIS. The steps would be similar for another database system such as MS SQL Server.

    First, you need to download and install the specific PDBC driver for the database system. For PostgreSQL with PostGIS, you can find the drivers here: odbc.postgresql.org. For MS SQL Server, this is a good starting place: Download ODBC Driver for SQL Server.

    If you look in the latest Release Notes document, you can find a list of ODBC drivers that MapInfo Pro supports. These are drivers that have been tested with MapInfo Pro. We may also support newer/older versions but we can't be sure as we have tested these. As MapInfo Pro is a 64-bit application, you also need to ensure that you install a 64-bit driver.

    These are the supported drivers for MapInfo Pro v2021.1:

    • Microsoft Access ODBC (64-bit)
    • Microsoft SQL Server 2019
    • Microsoft SQL Server 2017
    • Microsoft SQL Server 2016
    • Microsoft SQL Server 2012 QFE Native Client v11 (64-bit)
    • PostgreSQL Unicode and ANSI ODBC driver 13.01 (64-bit)
    • FDO toolkit 3.8 (used by SQLite and installed with MapInfo Pro)
    • Oracle Instant Client 19.11 (64-bit)
    • Microsoft ODBC Driver for SQL Server 17.8

    You may also have to reach out to your IT department to get the ODBC driver installed on your system.

    Once the ODBC Driver has been installed, it's time to create an ODBC Data Source. You can create this directly from within MapInfo Pro or from the Windows Control Panel. I'll show you how to create it from within MapInfo Pro.

    From the Home tab, click on Open Database Table in the Open dropdown.

    The dialog Select Data Source now appears.
    Before we proceed, we need to establish the different ways an ODBC Data Source can be stored on your system.
    1. As a File Data Source
    2. As a User Machine Data Source
    3. As a System Machine Data Source

    The first is as the name reveals stored in a file somewhere on your local computer or the network. You decide where to store the file and in this way, you can also store it in a place where other users to access it too. This can be a good way to share a data source across your team. Do remember that all users need to have the ODBC Driver installed to be able to use the data source.

    The last two options stores the data source in the Registry on your computer. The difference between the two is where in the Registry. The first - user - stores it so that only you can access it. The second - system - stores it so that all users of the computer can access it. The second option often requires more credentials for you to be able to store the data source.

    Let's create a personal Machine data source.
    First, you will have to switch to the Machine Data Source tab in the Select Data Source dialog. Then click on New... to start the process of creating a new data source.
    If you don't have the necessary admin credential to create a system data source, you will see this dialog:
    You can just click OK on this dialog and store the data source as a personal data source.
    Depending on your credentials you may be able to pick User Data Source or System Data Source. We will select User Data Source (Applies to this machine only). Now click Next >.
    In the next step, you will have to select the ODBC Driver to use. The dialog will list the different drivers that have been installed on your system so this can vary from what you see in the image above.
    I select the PostgreSQL ODBC Driver (UNICODE) from the list and click Next >.
    When you click Finish, the new data source will get created. This is however just the first step in the process. This first step is identical for all ODBC Drivers. The next step where we need to configure the data source will differ depending on the ODBC Driver type. They all have different parameters to need to be set. Some do this in a single dialog, while others require you to go through numerous dialogs.
    For PostgreSQL, the default configuration looks like this:
    You now need to enter the additional information to be able to connect to the database. You may have to talk to your database admin if you haven't set up and configured the database yourself.
    For my PostgreSQL database, the configuration looks like this:
    If you click the Datasource button under Options, you will find three dialogs allowing you to configure certain options when connecting to the database and transferring data back and forth between the database and MapInfo Pro. I must admit that I haven't had a closer look at these options. Any suggestions would be very welcome.
    It's recommended to use the Test button to see if the connection to the database works.
    Click Save to save the configurations to the data source.
    This will now bring you back to the Select Data Source dialog where you can select the new data source and connect to the database. But let's wait a minute with that and discuss additional ways in the next chapter. Click the Cancel button in the Select Data Source dialog for now.

    Connection to a Database using an ODBC Data Source

    You can connect to a database system from multiple places in MapInfo Pro.

    Above you saw that you can do this from the Open DBMS Table control on in the Open dropdown.

    You can also connect to a database from the Open dialog through Open DBMS Connect button in the top right corner of the dialog.

    And finally, if you often or always need to connect to a database, you can configure MapInfo Pro to connect to a database through the Startup Preferences dialog.
    Click on Set... in the Default DBMS Connection area and select your preferred DBMS Data Source. If you have selected a data source, MapInfo Pro will automatically connect to this database when it starts.
    No matter how you have connected to a database, you will afterward be able to locate the open database connections in the Files of Type list in the Open dialog.
    You can also find the connections in the Connections List in the Explorer window. From here you can interact with the connection through the context menu of the connections.
    That's all for now. In the next #MapInfoMonday article on the topic of using databases from within MapInfo Pro, we will look into preparing the database and loading data into the database. Stay tuned!


    ------------------------------
    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------