Tim Goeke Thomas Lockhart 1998-10-21 ODBC Background information originally by Tim Goeke (tgoeke@xpressway.com) ODBC (Open Database Connectivity) is an abstract API that allows you to write applications that can interoperate with various RDBMS servers. ODBC provides a product-neutral interface between frontend applications and database servers, allowing a user or developer to write applications that are portable between servers from different manufacturers.. The ODBC API matches up on the backend to an ODBC-compatible data source. This could be anything from a text file to an Oracle or PostgreSQL RDBMS. The backend access comes from ODBC drivers, or vendor-specific drivers that allow data access. psqlODBC, which is included in the PostgreSQL distribution, is such a driver, along with others that are available, such as the OpenLink ODBC drivers. Once you write an ODBC application, you should be able to connect to any back-end database, regardless of the vendor, as long as the database schema is the same. For example. you could have MS SQL Server and PostgreSQL servers that have exactly the same data. Using ODBC, your Windows application would make exactly the same calls and the back-end data source would look the same (to the Windows application). In order to make use of an ODBC driver there must exist a driver manager on the system where the ODBC driver is to be used. There are two free ODBC driver managers for Unix-like operating systems known to us: iODBC iODBC and unixODBC unixODBC. Instructions for installing these driver managers are to be found in the respective distribution. Software that provides database access through ODBC should provide its own driver manager (which may well be one of these two). Having said that, any driver manager that you can find for your platform should support the PostgreSQL ODBC driver, or any other ODBC driver for that matter. The unixODBC distribution ships with a PostgreSQL ODBC driver of its own, which is similar to the one contained in the PostgreSQL distribution. It is up to you which one you want to use. We plan to coordinate the development of both drivers better in the future. To install the ODBC you simply need to supply the --enable-odbc option to the configure script when you are building the entire PostgreSQL distribution. The library will then automatically be built and installed with the rest of the programs. If you forget that option or want to build the ODBC driver later you can change into the directory src/interfaces/odbc and do make and make install there. It is also possible to build the driver to be specifically tuned for use with iODBC or unixODBC. This means in particular that the driver will use the driver manager's routines to process the configuration files, which is probably desirable since it creates a more consistent ODBC environment on your system. If you want to do that, then supply the configure options --with-iodbc or --with-unixodbc (but not both). If you build a stand-alone driver (not tied to iODBC or unixODBC), then you can specify where the driver should look for the configuration file odbcinst.ini. By default it will be the directory /usr/local/pgsql/etc/, or equivalent, depending on what --prefix and/or --sysconfdir options you supplied to configure. To select a specific location outside the PostgreSQL installation layout, use the --with-odbcinst option. To be most useful, it should be arranged that the driver and the driver manager read the same configuration file. odbc.sql Additionally, you should install the ODBC catalog extensions. That will provide a number of functions mandated by the ODBC standard that are not supplied by PostgreSQL by default. The file /usr/local/pgsql/share/odbc.sql (in the default installation layout) contains the appropriate definitions, which you can install as follows: psql -d template1 -f LOCATION/odbc.sql where specifying template1 as the target database will ensure that all subsequent new databases will have these same definitions. If for any reason you want to remove these functions again, run the file odbc-drop.sql through psql. .odbc.ini ~/.odbc.ini contains user-specified access information for the psqlODBC driver. The file uses conventions typical for Windows Registry files. The .odbc.ini file has three required sections. The first is [ODBC Data Sources] which is a list of arbitrary names and descriptions for each database you wish to access. The second required section is the Data Source Specification and there will be one of these sections for each database. Each section must be labeled with the name given in [ODBC Data Sources] and must contain the following entries: Driver = prefix/lib/libpsqlodbc.so Database = DatabaseName Servername = localhost Port = 5432 Remember that the PostgreSQL database name is usually a single word, without path names of any sort. The PostgreSQL server manages the actual access to the database, and you need only specify the name from the client. Other entries may be inserted to control the format of the display. The third required section is [ODBC] which must contain the InstallDir keyword and which may contain other options. Here is an example .odbc.ini file, showing access information for three databases: [ODBC Data Sources] DataEntry = Read/Write Database QueryOnly = Read-only Database Test = Debugging Database Default = Postgres Stripped [DataEntry] ReadOnly = 0 Servername = localhost Database = Sales [QueryOnly] ReadOnly = 1 Servername = localhost Database = Sales [Test] Debug = 1 CommLog = 1 ReadOnly = 0 Servername = localhost Username = tgl Password = "no$way" Port = 5432 Database = test [Default] Servername = localhost Database = tgl Driver = /opt/postgres/current/lib/libpsqlodbc.so [ODBC] InstallDir = /opt/applix/axdata/axshlib In the real world, differences in drivers and the level of ODBC support lessens the potential of ODBC: Access, Delphi, and Visual Basic all support ODBC directly. Under C++, such as Visual C++, you can use the C++ ODBC API. In Visual C++, you can use the CRecordSet class, which wraps the ODBC API set within an MFC 4.2 class. This is the easiest route if you are doing Windows C++ development under Windows NT. If I write an application for PostgreSQL can I write it using ODBC calls to the PostgreSQL server, or is that only when another database program like MS SQL Server or Access needs to access the data? The ODBC API is the way to go. For Visual C++ coding you can find out more at Microsoft's web site or in your Visual C++ documentation. Visual Basic and the other RAD tools have Recordset objects that use ODBC directly to access data. Using the data-aware controls, you can quickly link to the ODBC back-end database (very quickly). Playing around with MS Access will help you sort this out. Try using FileGet External Data. You'll have to set up a DSN first. Applixware Applixware has an ODBC database interface supported on at least some platforms. Applixware 4.4.2 has been demonstrated under Linux with PostgreSQL 7.0 using the psqlODBC driver contained in the PostgreSQL distribution. Applixware must be configured correctly in order for it to be able to access the PostgreSQL ODBC software drivers. These instructions are for the 4.4.2 release of Applixware on Linux. Refer to the Linux Sys Admin on-line book for more detailed information. You must modify axnet.cnf so that elfodbc can find libodbc.so (the ODBC driver manager) shared library. This library is included with the Applixware distribution, but axnet.cnf needs to be modified to point to the correct location. As root, edit the file applixroot/applix/axdata/axnet.cnf. At the bottom of axnet.cnf, find the line that starts with #libFor elfodbc /ax/... Change line to read libFor elfodbc applixroot/applix/axdata/axshlib/lib which will tell elfodbc to look in this directory for the ODBC support library. Typically Applix is installed in /opt so the full path would be /opt/applix/axdata/axshlib/lib, but if you have installed Applix somewhere else then change the path accordingly. Create .odbc.ini as described in . You may also want to add the flag TextAsLongVarchar=0 to the database-specific portion of .odbc.ini so that text fields will not be shown as **BLOB**. Bring up Applix Data Select the PostgreSQL database of interest. Select QueryChoose Server. Select ODBC, and click Browse. The database you configured in .odbc.ini should be shown. Make sure that the Host: field is empty (if it is not, axnet will try to contact axnet on another machine to look for the database). Select the database in the box that was launched by Browse, then click OK. Enter user name and password in the login identification dialog, and click OK. You should see Starting elfodbc server in the lower left corner of the data window. If you get an error dialog box, see the debugging section below. The Ready message will appear in the lower left corner of the data window. This indicates that you can now enter queries. Select a table from QueryChoose tables, and then select QueryQuery to access the database. The first 50 or so rows from the table should appear. The following messages can appear while trying to make an ODBC connection through Applix Data: Cannot launch gateway on server elfodbc can't find libodbc.so. Check your axnet.cnf. Error from ODBC Gateway: IM003::[iODBC][Driver Manager]Specified driver could not be loaded libodbc.so cannot find the driver listed in .odbc.ini. Verify the settings. Server: Broken Pipe The driver process has terminated due to some other problem. You might not have an up-to-date version of the PostgreSQL ODBC package. setuid to 256: failed to launch gateway The September release of Applixware 4.4.1 (the first release with official ODBC support under Linux) shows problems when user names exceed eight (8) characters in length. Problem description contributed by Steve Campbell (scampbell@lear.com). Contributed by Steve Campbell (scampbell@lear.com), 1998-10-20 The axnet program's security system seems a little suspect. axnet does things on behalf of the user and on a true multiuser system it really should be run with root security (so it can read/write in each user's directory). I would hesitate to recommend this, however, since we have no idea what security holes this creates. One good tool for debugging connection problems uses the Unix system utility strace. Start Applixware. Start an strace on the axnet process. For example, if $ ps -aucx | grep ax shows cary 10432 0.0 2.6 1740 392 ? S Oct 9 0:00 axnet cary 27883 0.9 31.0 12692 4596 ? S 10:24 0:04 axmain Then run $ strace -f -s 1024 -p 10432 Check the strace output. Many of the error messages from Applixware go to stderr, but I'm not sure where stderr is sent, so strace is the way to find out. For example, after getting a Cannot launch gateway on server, I ran strace on axnet and got [pid 27947] open("/usr/lib/libodbc.so", O_RDONLY) = -1 ENOENT (No such file or directory) [pid 27947] open("/lib/libodbc.so", O_RDONLY) = -1 ENOENT (No such file or directory) [pid 27947] write(2, "/usr2/applix/axdata/elfodbc: can't load library 'libodbc.so'\n", 61) = -1 EIO (I/O error) So what is happening is that applix elfodbc is searching for libodbc.so, but it cannot find it. That is why axnet.cnf needed to be changed. I think the condition this refers to is gone. -- petere 2002-01-07 In order to go through the Applixware Data Tutorial, you need to create the sample tables that the Tutorial refers to. The ELF Macro used to create the tables tries to use a NULL condition on many of the database columns, and PostgreSQL does not currently allow this option. To get around this problem, you can do the following: Copy /opt/applix/axdata/eng/Demos/sqldemo.am to a local directory. Edit this local copy of sqldemo.am: Search for null_clause = "NULL". Change this to null_clause = "". Start Applix Macro Editor. Open the sqldemo.am file from the Macro Editor. Select FileCompile and Save. Exit Macro Editor. Start Applix Data. Select *Run Macro. Enter the value sqldemo, then click OK. You should see the progress in the status line of the data window (in the lower left corner). You should now be able to access the demo tables. You can add information about your database login and password to the standard Applix start-up macro file. This is an example ~/axhome/macros/login.am file: macro login set_set_system_var@("sql_username@","tgl") set_system_var@("sql_passwd@","no$way") endmacro You should be careful about the file protections on any file containing user name and password information.