From 8cf41db2996758b935f21f10edd2c2c2b076e3b7 Mon Sep 17 00:00:00 2001 From: "Thomas G. Lockhart" Date: Wed, 21 Oct 1998 05:33:22 +0000 Subject: [PATCH] Incorporate information from Cary and Robert. More how-to stuff on ApplixWare. --- doc/src/sgml/odbc.sgml | 708 ++++++++++++++++++++++++++++++++++++++++++++++--- 1 file changed, 664 insertions(+), 44 deletions(-) diff --git a/doc/src/sgml/odbc.sgml b/doc/src/sgml/odbc.sgml index 223eb415d3..d23779254f 100644 --- a/doc/src/sgml/odbc.sgml +++ b/doc/src/sgml/odbc.sgml @@ -10,7 +10,7 @@ Lockhart -1998-08-25 +1998-10-21 ODBC Interface @@ -26,10 +26,12 @@ Background information originally by ODBC (Open Database Connectivity) is an abstract API -which allows you to write standard "ODBC" applications. +which allows you to write applications which 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 which are portable among servers. +allowing a user or developer to write applications which are +transportable between servers from different manufacturers.. Background @@ -57,7 +59,7 @@ is the same. For example. you could have MS SQL Server and Postgres servers which have exactly the same data. Using ODBC, -your Windows app would make exactly the +your Windows application would make exactly the same calls and the back end data source would look the same (to the Windows app). @@ -78,33 +80,40 @@ 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. - + + + +Writing Applications -If I write an app for Postgres + +If I write an application for Postgres can I write it using ODBC calls to the Postgres server, or is that only when another database program -like MS SQL Server or Access needs to access the data? +like MS SQL Server or Access needs to access the data? -Again, the ODBC API set +The ODBC API is the way to go. -For Visual C++ you can find out more at -Microsoft's web site or in your docs. +For Visual C++ coding you can find out more at +Microsoft's web site or in your VC++ docs. Visual Basic and the other RAD tools have Recordset objects @@ -133,7 +142,7 @@ The Postgres datetime type will break MS Access. --> -Unix Applications +Unix Installation ApplixWare has an @@ -148,6 +157,41 @@ driver contained in the Postgres distribution. Building the Driver +The first thing +to note about the psqlODBC driver + (or any ODBC driver) is that there must +exist a driver manager on the system where + the ODBC driver is to be +used. There exists a freeware ODBC driver for Unix + called iodbc which +can be obtained from various locations on the Net, including at +AS200. +Instructions for installing iodbc + are beyond the scope of this +document, but there is a README + that can be found inside the iodbc compressed +.shar file that should explain how to get it up and running. + + +Having said that, any driver manager that you can find for your platform +should support the psqlODBC driver + or any ODBC driver. + + +The Unix configuration files for psqlODBC + have recently been extensively +reworked to allow for easy building on supported platforms as +well as to allow for support of other Unix platforms in the future. +The new configuration and build files for the driver should make it +a simple process to build the driver on the supported platforms. Currently +these include Linux and FreeBSD but we are hoping other users will +contribute the necessary information to quickly expand the number of +platforms for which the driver can be built. + + +There are actually two separate methods to build the driver depending on +how you received it and these differences come down to only where and how to +run configure and make. The driver can be built in a standalone, client-only installation, or can be built as a part of the main Postgres distribution. The standalone installation is convenient if you have ODBC @@ -155,17 +199,45 @@ client applications on multiple, heterogeneous platforms. The integrated installation is convenient when the target client is the same as the server, or when the client and server have similar runtime configurations. - + +Specifically if you have received the psqlODBC + driver as part of the Postgres distribution + (from now on referred to as an "integrated" build) then you will +configure and make the ODBC driver + from the top level source directory +of the Postgres distribution + along with the rest of its libraries. +If you received the driver as a standalone package than you will run +configure and make from the directory in which you unpacked the +driver source. + + Integrated Installation -For an integrated installation, specify the -command-line argument for src/configure: +This installation procedure is appropriate for an integrated installation. + + + +Specify the +command-line argument for src/configure: -./configure --with-odbc +% ./configure --with-odbc +% make + + +Rebuild the Postgres distribution: + + +% make install + + + + + Once configured, the ODBC driver will be built and installed into the areas defined for the other components of the Postgres system. The installation-wide @@ -174,10 +246,46 @@ the top directory of the Postgres target tree (POSTGRESDIR). This can be overridden from the make command-line as -% make ODBCINST=filename +% make ODBCINST=filename install + + + +Pre-v6.4 Integrated Installation + + +If you have a Postgres installation older than +v6.4, you have the original source tree available, +and you want to use the newest version of the ODBC +driver, then you may want to try this form of installation. + + + +Copy the output tar file to your target system and unpack it into a +clean directory. + + + +From the directory containing the +sources, type: + + +% ./configure +% make +% make POSTGRESDIR=PostgresTopDir install - + + +If you would like to install components into different trees, +then you can specify various destinations explicitly: + + +% make BINDIR=bindir LIBDIR=libdir HEADERDIR=headerdir ODBCINST=instfile install + + + + + Standalone Installation @@ -188,44 +296,191 @@ clients who do not have a locally-installed Postgres source tree. +The default location for libraries and headers +for the standalone installation is /usr/local/lib + and /usr/local/include/iodbc, respectively. +There is another system wide configuration file that gets installed +as /share/odbcinst.ini (if /share + exists) or as /etc/odbcinst.ini + (if /share does not exist). + + + +Installation of files into /share + or /etc requires system root privileges. +Most installation steps for Postgres do not +have this requirement, and you can choose another destination which +is writable by your non-root Postgres superuser +account instead. + + + + The standalone installation distribution can be built from the -Postgres distribution or may be obtained -from Insight Distributors, +Postgres distribution or may be obtained from +Insight Distributors, the current maintainers of the non-Unix sources. -To create a tar file for a complete standalone installation, first -configure the main Postgres distribution. -Then, create the tar file: +Copy the zip +or gzipped tarfile to an empty directory. If using the zip package +unzip it with the command + +% unzip -a packagename + + +The option +is necessary to get rid of DOS +CR/LF pairs in the source files. + + +If you have the gzipped tar package than simply run + + +tar -xzf packagename + + + + + + +To create a tar file for a complete standalone installation +from the main Postgres source tree: + + + + + +Configure the main Postgres distribution. + + + +Create the tar file: % cd interfaces/odbc % make standalone + + +Copy the output tar file to your target system. Be sure to transfer as +a binary file if using ftp. + + -Copy the output tar file to your target system, unpack it into a clean -directory, and then: +Unpack the tar file into a clean +directory. + + + +Configure the standalone installation: -% ./configure --with-odbcinst=instfile -% make POSTGRESDIR=targettree ODBCINST=instfile +% ./configure - -The ODBCINST can be specified on either or both command lines. - +The configuration can be done with options: + + +% ./configure --prefix=rootdir --with-odbc=inidir + + +where installs the libraries and headers in +the directories rootdir/lib and +rootdir/include/iodbc, and + installs odbcinst.ini in the +specified directory. -If you would like to install components into different trees, then you -can specify various destinations explicitly: +Note that both of these options can also be used from the integrated build +but be aware that when used in the integrated build + will also apply to the rest of +your Postgres installation. + applies only to the configuration file + odbcinst.ini. + + + +Compile and link the source code: -% make BINDIR=bindir LIBDIR=libdir HEADERDIR=headerdir ODBCINST=instfile +% make ODBCINST=instdir - + +You can also override the default location for installation on the +'make' command line. This only applies to the installation of the +library and header files. Since the driver needs to know the location +of the odbcinst.ini file attempting to override the enviroment variable +that specifies its installation directory will probably cause you +headaches. It is safest simply to allow the driver to install the +odbcinst.ini file in the default directory or the directory you specified +on the './configure' command line with --with-odbc. + + + + + +Install the source code: + + +% make POSTGRESDIR=targettree install + + + +To override the library and header installation directories separately +you need to pass the correct installation variables on the +make install command line. These variables are +LIBDIR, HEADERDIR + and ODBCINST. +Overriding POSTGRESDIR on the make command line will cause + LIBDIR and HEADERDIR + to be rooted at the new directory you specify. +ODBCINST is independent of POSTGRESDIR. + + +Here is how you would specify the various destinations explicitly: + + +% make BINDIR=bindir LIBDIR=libdir HEADERDIR=headerdir install + + + +For example, typing + + +% make POSTGRESDIR=/opt/psqlodbc install + + +(after you've used + ./configure and make) +will cause the libraries and headers to be installed in the directories +/opt/psqlodbc/lib + and /opt/psqlodbc/include/iodbc respectively. + + +The command + + +% make POSTGRESDIR=/opt/psqlodbc HEADERDIR=/usr/local install + + +should cause the libraries to be installed in /opt/psqlodbc/lib and +the headers in /usr/local/include/iodbc. If this doesn't work as +expected please contact one of the maintainers. + + + + Configuration Files @@ -235,6 +490,36 @@ The file uses conventions typical for Windows Registry files, but despite this restriction can be made to work. +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 = POSTGRESDIR/lib/libpsqlodbc.so +Database=DatabaseName +Servername=localhost +Port=5432 + + + + +Remember that the Postgres database name is +usually a single word, without path names of any sort. +The Postgres 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: @@ -274,22 +559,357 @@ Driver = /opt/postgres/current/lib/libpsqlodbc.so InstallDir = /opt/applix/axdata/axshlib + +ApplixWare + + + + +Configuration + + +ApplixWare must be configured correctly + in order for it to +be able to access the Postgres + ODBC software drivers. + + +Enabling ApplixWare Database Access + + +Note that +these instructions are for the 4.4.1 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/... + + + -For Applix, here is an example ~/axhome/macros/login.am file: +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. +If you have installed applix somewhere else, +change the path accordingly. + + + + + +Create .odbc.ini as +described above. 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**. + + + + +Testing ApplixWare ODBC Connections + + + + Bring up Applix Data + + + +Select the Postgres database of interest. + + + + + +Select Query->Choose Server. + + + + Select ODBC, and click Browse. +The database you configured in .odbc.ini + should be shown. Make sure that the + 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 username 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 Query->Choose tables, and then select Query->Query + to access the database. The first 50 or so rows from the table should + appear. + + + + +Common Problems + + +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 Postgres +ODBC package. + + + + +Debugging ApplixWare ODBC Connections + + +One good tool for debugging connection problems uses the Unix system +utility strace. + + +Debugging with 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. + + +Note from Cary + + +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 +can't find it. That is why axnet.cnf needed to be changed. + + +Running the ApplixWare Demo + + +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 Postgres does not currently allow this option. + + +To get around this problem, you can do the following: + + +Modifying the ApplixWare Demo + + + +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 File->Compile 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. + + + + +Useful Macros + + +You can add information about your +database login and password to the standard Applix startup +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") + 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 +username and password information. + + + Supported Platforms -psqlODBC has been built and tested -on Linux. There have been reports of success -with FreeBSD and with Solaris. +psqlODBC has been built and tested +on Linux. There have been reports of success +with FreeBSD and with Solaris. There are no known restrictions +on the basic code for other platforms which already support +Postgres. -- 2.11.0