PostgreSQL/MySQL ODBC/JDBC

What is ODBC?

Open Database Connectivity (ODBC) refers to a software API method for using database management systems (DBMS). ODBC was created so as to be independent of programming languages or operational systems and offers access to different database systems. The standard ODBC consists of an ODBC core and the respective specific ODBC database drivers. The core, also known as Driver manager, is independent of the database and acts as an interpreter between the application and the database drivers. The database drivers, on the other hand, contain DBMS-specific details and offer a mechanism for connecting with different ODBS-enabled database systems.

Thanks to this modular model ODBC has become a universal solution for creating dynamic applications. A connection with the ODBC API can be established by using several popular programming languages including PHP, Perl, Python, C, C++ and .Net. Thanks to the JDBC-ODBC bridges, Java support can be provided by translating JDBC calls into ODBC.

The presence of many database drivers allows ODBC to connect with the most popular corporate database systems - Oracle, MsSQL, Sybase, as well as with the proven Open Source alternatives - MySQL, PostgreSQL.

ODBC with PostgreSQL

The ODBC driver for PostgreSQL is named psqlODBC. It used to be a part of the PostgreSQL package, but it was taken away and has been developed as a separate package since 2004. This marks a significant update in the quality of the driver, since now its developers are solely concentrated on improving its functionality.

Installing a psqlODBC driver in Linux

To install psqlODBC in Linux, you will just have to download the latest version and then compile it. The process is really easy and consists of a few simple steps.

First off, you will have to download the latest release from the project's home page. You can do this using the "wget" command

How to download the latest psqlODBC version in a Linux system

Once you have the latest version, it's time to install it. First off, un-tar the newly downloaded file and go into the created folder.

How to extract and access the psqlODBC folder

Once in the new folder, you will have to perform the compile-make install sequence, just like you would with any other Linux source file.

How to compile psqlODBC

And you are ready.

Compiling psqlODBC in Windows

There are two ways to compile psqlODBC in Windows - using the command line interface or using Microsoft Visual C++.

To compile psqlODBC with Visual C++, open the psqlodbc.dsp file and compile it. Have in mind that all PostgreSQL-required headers and libraries will be installed in C:\Program Files\PostgreSQL.

To compile it with the command line, use the NMAKE command. You can specify if the build should be for the x32 or the x64 version of Windows.

How to compile posqlODBC with the Windows command line

The compiled driver will have the following features:

• UNICODE support.
• libpq.dll is used, therefore, SSL and Kerberos are supported.
• ODBCVER is 0x0351.
• The driver filename is "psqlodbc35w.dll".

ODBC with MySQL

The ODBC driver for MySQL is labeled MyODBC. It is also known as MySQL Connector /ODBC. It is developed by the MySQL team and at the moment, it has two versions available - 3.51 and 5.1. The 5.1 version is the newer version and the recommended one for installation. It introduces several new features over the older version:

• Unicode support conforming to ODBC specifications (SQL*W APIs and SQL_C_WCHAR support)
• ODBC descriptor support
• SQLCancel() support (for canceling a query in another thread)
• Native Windows setup GUI (for specifying the requested information prior to connecting)
• Replaced internal library which handles creation and loading of DSN information

Installing MyODBC in Ubuntu Linux

To have MyODBC in Linux, you will also need an ODBC Manager. MyODBC is known to work with most of the Linux ODBC managers without a problem. Here we will show you how to install MyODBC in Ubuntu Linux with iODBC as the manager.

Install iODBC in Ubuntu Linux

This will install the GTK+ config frontend for the iODBC Driver Manager. Once this is done, we will need to get the actual MyODBC.

Install the MySQL connector for iODBC in Ubuntu Linux

When you have the MyODBC installed, you will need to set it up. Run the iODBC and in there, point the path to the MyODBC drivers.

Start iODBC

In the "Drivers" tab, select "Add a driver" and type in the description. For "Driver file name" choose /usr/lib/odbc/libmyodbc.so and for "Setup file name" choose /usr/lib/odbc/libodbcmyS.so.

Installing MyODBC in Windows

To install MyODBC in Windows, all you need to do is download the installer for the version you want. On the MySQL page you can find installers for both Windows x32 and Windows x64. Once installed, the ODBC driver will appear in your Control Panel. When you run it, you will be able to choose if you want to set up the driver for a specific user (the "User DSN" tab) or for all the users on your machine (the "System DSN" tab). Once you are done with this, click the "Add" button and select the MySQL driver. When you click "Finish", you will need to configure the driver. There are several fields, which you will need to fill in:

• Windows DSN Name - type the name of this particular driver. This is left to your own choice and can be set to anything.
• Server - this is the name of the server where your database is located.
• MySQL Database Name - the name of your MySQL database.
• User - the user name for your MySQL database.
• Password - the password for the MySQL database.
• Port - usually, the port for MySQL is 3306, but if you are behind a firewall, you may have to specify a different port.

PostgreSQL and MySQL with NTC Hosting

NTC Hosting provides advanced hosting solutions. Our plans offer support not only for MySQL, but also for the proven, enterprise-class PostgreSQL database. We have specifically optimized our servers in order to provide our clients with the best PostgreSQL and MySQL hosting experience. In all our web hosting packages we have PHP, Perl and Python included, in order to provide our users with as many possibilities as possible when creating their database driven applications.