Database management with ODBC
Table of contents :
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 FilesPostgreSQL.
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
C:psqlodbc> nmake /f win32.mak CFG=Release ALLC:psqlodbc> nmake /f win64.mak CFG=Release ALL
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
sudo apt-get install iodbc
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
sudo apt-get install libmyodbc
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.
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.
You might also like
I have seen a lot of businesses with linux for their backend servers and a number with specific applications for front ends. Even people that are die-hard windows people whom i thought would never have a linux server, have one now.
Anyway, based on the limited information you gave.
If you are looking for turnkey accounting system or inventory management system. they do exist, and as someone stated before they are mostly web browser driven. Typically off a mysql backend. Again users don't have to change to linux to do
this, and you dont have to ruin your existing system while you are porting the applications to web driven ones
Inside ODBC (Microsoft programming series)
Book (Microsoft Press)
XDB DATABASE MANAGEMENT SYSTEMS: DB2 POWER FOR CLIENT SERVER--XDB-ODBC Server Driver Version 3.0 Windows Operating System
Single Detail Page Misc (XDB)
FileMaker Pro 10: The Missing Manual
Book (O'Reilly Media)
Teach Yourself ODBC Programming in 21 Days
Book (Sams Publishing)
The Odbc Solution: Open Database Connectivity in Distributed Environments/Book and Disk (Mcgraw-Hill Series on Computer Communications)
Book (Mcgraw-Hill (Tx))