ODBC


ODBC is widely used in Microsoft and office environments. A powerful ODBC interface is provided by PostgreSQL. The most important facts about ODBC will be covered in this section.

An Overview of ODBC

In the traditional database, an application is a piece of software that performs a specific database task for a specific database system. Such applications are usually using embedded SQL. This is very efficient and, in many cases, is very portable across different platforms and database systems. The problem is that the code has to be recompiled for each new environment. Embedded SQL is not the best solution for analyzing data stored in databases such as DB2 and Oracle. For example, if you want to integrate your data in office applications, things have to be done differently. If many data sources have to be integrated in one application, it is useful to have a standard interface. Imagine having 10 different database systems with each system having its own proprietary interface. This would lead to very complicated and bad software, if not to disaster.

Open Database Connectivity (ODBC) offers a new approach. A separate program is used to extract the data, while the application simply has to import the data. There will always be different database systems with different features, but ODBC offers a standard interface for accessing all databases supporting ODBC.

If you want your Windows application to connect to a database server, the application should be able connect to any database providing an ODBC driver. As long as the data in two databases is the same, there should be no difference between connecting to an Oracle database and connecting to a PostgreSQL server.

Using ODBC with PostgreSQL

The idea behind ODBC is very simple, but let's look at how PostgreSQL can be used in combination with ODBC.

Making your PostgreSQL database support ODBC should be a rather easy task. If you are working on a Linux machine, simply install the binaries you can find on any PostgreSQL mirror. If you are a user of Linux Debian, simply convert the RPMs to Debian archives by using alien . After installing the binaries, your database will support ODBC.

If you want to use the sources instead of compiling the server, you have to use --with-odbcinst at compile time. In addition to that, you should add the commands in the file odbc.sql in your database. odbc.sql is distributed with the source distribution of your PostgreSQL server and contains some extensions mandated by the ODBC standard that are not included in PostgreSQL by default. The file has to be inserted into template1 so that all databases created after inserting the file can use the additional functions.

You will need a driver to access your database. The driver manager for UNIX can be downloaded from www.iodbc.org. Simply compile the sources and you can use the driver. If you want to use the ODBC driver for Perl's DBI module, a driver manager will also be necessary.

To access your PostgreSQL server from a Windows platform, you have to download the appropriate ODBC driver for PostgreSQl from ftp://download. sourceforge .net/pub/mirrors/postgresql/odbc/versions. On the FTP site (a PostgreSQL mirror site), you can find zip-archives containing the necessary software.

ODBC and Security

Security demands are constantly increasing. Security is one of the most important topics, especially when dealing with database environments.

We have to admit that every computer system around the globe can be hacked or at least severely be impaired. In spite of that issue, it is important to take care of security to make sure that hackers have a hard job.

Applications using NT security do not pass user IDs and passwords across an ODBC connection. This is a very important issue because a transmission can, in many cases, be sniffed. This would lead to security problems. IBM's DB2, Microsoft's SQL server, and all BackOffice products can be configured to use integrated NT security.

Some applications have a security system implemented on top of ODBC, and passwords are encrypted before they are transmitted over the network. If someone scans the data transmitted via ODBC, he or she can only find encrypted passwords.

Make sure that applications prevent tracking of sensitive commands. On Windows 95 and Windows NT systems, simply delete the file called odbctrac.dll . This will make sure that tracing is really disabled on your system.

The Microsoft Knowledge Base offers a lot of additional information concerning ODBC and security. If you want to build business-critical applications based on ODBC, we strongly recommend to check these sources.



PostgreSQL Developer's Handbook2001
PostgreSQL Developer's Handbook2001
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 125

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net