odbc.sgml

关系型数据库 Postgresql 6.5.2

<Chapter Id="odbc">
<DocInfo>
<AuthorGroup>
<Author>
<FirstName>Tim</FirstName>
<Surname>Goeke</Surname>
</Author>
<Author>
<FirstName>Thomas</FirstName>
<Surname>Lockhart</Surname>
</Author>
</AuthorGroup>
<Date>1998-10-21</Date>
</DocInfo>

<Title>ODBC Interface</Title>

<Note>
<Para>
Background information originally by
 <ULink url="mailto:[e-mail]">Tim Goeke</ULink>
</Para>
</Note>

<Para>
<acronym>ODBC</acronym> (Open Database Connectivity) is an abstract 
<acronym>API</acronym> 
which allows you to write applications which can interoperate
with various <acronym>RDBMS</acronym> servers.
<acronym>ODBC</acronym> provides a product-neutral interface 
between frontend applications and database servers,
allowing a user or developer to write applications which are 
transportable between servers from different manufacturers..
</Para>

<Sect1>
<Title>Background</Title>

<Para>
The <acronym>ODBC</acronym> <acronym>API</acronym> matches up 
on the backend to an <acronym>ODBC</acronym>-compatible data source.
This could be anything from a text file to an Oracle or 
<productname>Postgres</productname> <acronym>RDBMS</acronym>.
</Para>

<Para>
The backend access come from <acronym>ODBC</acronym> drivers, 
or vendor specifc drivers that
allow data access.   <productname>psqlODBC</productname> is such a driver,
 along with others that are
available, such as the OpenLink <acronym>ODBC</acronym> drivers.
</Para>

<Para>
Once you write an <acronym>ODBC</acronym> application, 
you <emphasis>should</emphasis> be able to connect to <emphasis>any</emphasis>
back end database, regardless of the vendor, as long as the database schema
is the same.
</Para>

<Para>
For example. you could have <productname>MS SQL Server</productname>
 and <productname>Postgres</productname> servers which have
exactly the same data.  Using <acronym>ODBC</acronym>, 
your Windows application would make exactly the
same calls and the back end data source would look the same (to the Windows
app).
</Para>

<para>
<ulink url="http://www.insightdist.com/">Insight Distributors</ulink> 
provides active and ongoing
support for the core <productname>psqlODBC</productname> distribution. 
They provide a
<ulink url="http://www.insightdist.com/psqlodbc/"><acronym>FAQ</acronym></ulink>,
 ongoing development on the code base, and actively participate on the 
<ulink url="mailto:[e-mail]">interfaces mailing list</ulink>.
</Para>
</sect1>

<sect1>
<title><productname>Windows</productname> Applications</title>

<Para>
In the real world, differences in drivers and the level of 
<acronym>ODBC</acronym> support
lessens the potential of <acronym>ODBC</acronym>:

<ItemizedList Mark="bullet" Spacing="compact">
<ListItem>
<Para>
Access, Delphi, and Visual Basic all support <acronym>ODBC</acronym> directly.
</Para>
</listitem>
<ListItem>
<Para>
Under C++, such as Visual C++, 
you can use the C++ <acronym>ODBC</acronym> <acronym>API</acronym>.
</Para>
</listitem>

<ListItem>
<Para>
In Visual C++, you can use the CRecordSet class, which wraps the 
<acronym>ODBC</acronym> <acronym>API</acronym>
set within an MFC 4.2 class.  This is the easiest route if you are doing
Windows C++ development under Windows NT.
</Para>
</listitem>
</ItemizedList>
</Para>

<sect2>
<title>Writing Applications</title>

<Para>
<quote>
If I write an application for <productname>Postgres</productname> 
can I write it using <acronym>ODBC</acronym> calls
to the <productname>Postgres</productname> server, 
or is that only when another database program 
like MS SQL Server or Access needs to access the data?</quote>
</para>
<Para>
The <acronym>ODBC</acronym> <acronym>API</acronym>
is the way to go.
For <productname>Visual C++</productname> coding you can find out more at
Microsoft's web site or in your <productname>VC++</productname> docs.
</Para>

<Para>
Visual Basic and the other RAD tools have Recordset objects 
that use <acronym>ODBC</acronym>
directly to access data.  Using the data-aware controls, you can quickly
link to the <acronym>ODBC</acronym> back end database 
(<Emphasis>very</Emphasis> quickly).
</Para>

<Para>
Playing around with MS Access will help you sort this out.  Try using
<literal>File->Get External Data</literal>.
</Para>

<Tip>
<Para>
You'll have to set up a DSN first.
</Para>
</Tip>

<!--
<Para>
<Tip>
<Para>
The <productname>Postgres</productname> datetime type will break MS Access.
</Para>
</Tip>
-->
</sect2>
</sect1>

<sect1>
<title>Unix Installation</title>

<para>
<productname>ApplixWare</productname> has an 
<acronym>ODBC</acronym> database interface
supported on at least some platforms. 
<productname>ApplixWare</productname> v4.4.1 has been
demonstrated under Linux with <productname>Postgres</productname> v6.4 
using the <productname>psqlODBC</productname>
driver contained in the <productname>Postgres</productname> distribution.
</Para>

<sect2>
<title>Building the Driver</title>

<para>
The first thing
to note about the <productname>psqlODBC</productname> driver
 (or any <acronym>ODBC</acronym> driver) is that there must
exist a driver manager on the system where
 the <acronym>ODBC</acronym> driver is to be
used. There exists a freeware <acronym>ODBC</acronym> driver for Unix
 called <productname>iodbc</productname> which
can be obtained from various locations on the Net, including at
<ulink url="http://www.as220.org/FreeODBC/iodbc-2.12.shar.Z">AS200</ulink>. 
Instructions for installing <productname>iodbc</productname>
 are beyond the scope of this
document, but there is a <filename>README</filename>
 that can be found inside the <productname>iodbc</productname> compressed
.shar file that should explain how to get it up and running.
</Para>

<para>
Having said that, any driver manager that you can find for your platform
should support the <productname>psqlODBC</productname> driver
 or any <acronym>ODBC</acronym> driver.
</Para>

<para>
The Unix configuration files for <productname>psqlODBC</productname>
 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.
</Para>

<para>
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 <application>configure</application> and <application>make</application>. 
The driver can be built in a standalone, client-only installation, or can be 
built as a part of the main <productname>Postgres</productname> distribution.
The standalone installation is convenient if you have <acronym>ODBC</acronym>
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.
</Para>

<para>
Specifically if you have received the <productname>psqlODBC</productname>
 driver as part of the <productname>Postgres</productname> distribution
 (from now on referred to as an "integrated" build) then you will
configure and make the <acronym>ODBC</acronym> driver
 from the top level source directory
of the <productname>Postgres</productname> 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.
</Para>

<procedure>
<title>Integrated Installation</title>

<para>
This installation procedure is appropriate for an integrated installation.
</Para>

<step performance="required">
<para>
Specify the <option>--with-odbc</option>
command-line argument for <application>src/configure</application>:

<programlisting>
% ./configure --with-odbc
% make
</programlisting>
</Para>
</step>
<step performance="required">
<para>
Rebuild the <productname>Postgres</productname> distribution:

<programlisting>
% make install
</programlisting>
</Para>
</step>
</procedure>

<para>
Once configured, the <acronym>ODBC</acronym> driver will be built and installed
into the areas defined for the other components of the
<productname>Postgres</productname> system. The installation-wide
<acronym>ODBC</acronym> configuration file will be placed into
the top directory of the Postgres target tree (<envar>POSTGRESDIR</envar>).
This can be overridden from the <application>make</application> command-line
as
<programlisting>
% make ODBCINST=<replaceable>filename</replaceable> install
</programlisting>
</Para>

<procedure>
<title>Pre-v6.4 Integrated Installation</title>

<para>
If you have a <productname>Postgres</productname> installation older than
v6.4, you have the original source tree available, 
and you want to use the newest version of the <acronym>ODBC</acronym>
driver, then you may want to try this form of installation.
</Para>

<step performance="required">
<para>
Copy the output tar file to your target system and unpack it into a 
clean directory.
</Para>
</step>
<step performance="required">
<para>
From the directory containing the
sources, type:

<programlisting>
% ./configure
% make
% make POSTGRESDIR=<replaceable class="parameter">PostgresTopDir</replaceable> install
</programlisting>
</Para>
</step>

<step performance="optional">
<para>
If you would like to install components into different trees, 
then you can specify various destinations explicitly:

<programlisting>
% make BINDIR=bindir  LIBDIR=libdir  HEADERDIR=headerdir ODBCINST=instfile install
</programlisting>
</Para>
</step>
</procedure>

<procedure>
<title>Standalone Installation</title>

<para>
A standalone installation is not integrated with or built on the normal
<productname>Postgres</productname> distribution. It should be best suited
for building the <acronym>ODBC</acronym> driver for multiple, heterogeneous
clients who do not have a locally-installed <productname>Postgres</productname>
source tree.
</Para>

<para>
The default location for libraries and headers 
for the standalone installation is <filename>/usr/local/lib</filename>
 and <filename>/usr/local/include/iodbc</filename>, respectively.
There is another system wide configuration file that gets installed
as <filename>/share/odbcinst.ini</filename> (if <filename>/share</filename>
 exists) or as <filename>/etc/odbcinst.ini</filename>
 (if <filename>/share</filename> does not exist).
</Para>

<note>
<para>
Installation of files into <filename>/share</filename>
 or <filename>/etc</filename> requires system root privileges.
Most installation steps for <productname>Postgres</productname> do not
have this requirement, and you can choose another destination which
is writable by your non-root <productname>Postgres</productname> superuser
account instead.
</Para>
</note>

<step performance="required">
<para>
The standalone installation distribution can be built from the
<productname>Postgres</productname> distribution or may be obtained from 
<ulink url="http://www.insightdist.com/psqlodbc">Insight Distributors</ulink>,
the current maintainers of the non-Unix sources.
</Para>

<para>
Copy the zip
or gzipped tarfile to an empty directory. If using the zip package
unzip it with the command 
<programlisting>
% unzip -a <replaceable>packagename</replaceable>
</programlisting>

The <option>-a</option> option
is necessary to get rid of <acronym>DOS</acronym> 
CR/LF pairs in the source files.
</Para>

<para>
If you have the gzipped tar package than simply run

<programlisting>
tar -xzf <replaceable>packagename</replaceable>
</programlisting>
</Para>

<substeps>

<step performance="optional">
<para>
To create a tar file for a complete standalone installation
from the main <productname>Postgres</productname> source tree:
</Para>
</step>
</substeps>
</step>
<step performance="required">
<para>
Configure the main <productname>Postgres</productname> distribution.
</Para>
</step>
<step performance="required">
<para>
Create the tar file:

<programlisting>
% cd interfaces/odbc
% make standalone
</programlisting>
</Para>
</step>

<step performance="required">
<para>
Copy the output tar file to your target system. Be sure to transfer as
a binary file if using <application>ftp</application>.
</Para>
</step>

<step performance="required">
<para>
Unpack the tar file into a clean
directory.
</Para>
</step>

<step performance="required">
<para>
Configure the standalone installation:

<programlisting>
% ./configure
</programlisting>
</Para>

<para>
The configuration can be done with options:

<programlisting>
% ./configure --prefix=<replaceable>rootdir</replaceable> --with-odbc=<replaceable>inidir</replaceable>
</programlisting>

where <option>--prefix</option> installs the libraries and headers in
the directories <filename><replaceable>rootdir</replaceable>/lib</filename> and
<filename><replaceable>rootdir</replaceable>/include/iodbc</filename>, and
<option>--with-odbc</option> installs <filename>odbcinst.ini</filename> in the
specified directory.
</Para>

<para>
Note that both of these options can also be used from the integrated build
but be aware that <emphasis>when used in the integrated build</emphasis>
<option>--prefix</option> will also apply to the rest of
your <productname>Postgres</productname> installation.
<option>--with-odbc</option> applies only to the configuration file
 <filename>odbcinst.ini</filename>.
</Para>
</step>

<step performance="required">
<para>
Compile and link the source code:

<programlisting>
% make ODBCINST=<replaceable>instdir</replaceable>
</programlisting>
</Para>

<para>
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.
</Para>
</step>

<!--
This doesn't currently work - thomas 1998-10-19
<tip>
<para>
<envar>ODBCINST</envar> can be specified during configuration or during
the compilation. It is not necessary to do so in both steps.
</tip>
-->

<step performance="required">
<para>
Install the source code:

<programlisting>
% make POSTGRESDIR=<replaceable>targettree</replaceable> install
</programlisting>
</Para>

<para>
To override the library and header installation directories separately
you need to pass the correct installation variables on the
<literal>make install</literal> command line. These variables are
<envar>LIBDIR</envar>, <envar>HEADERDIR</envar>
 and <envar>ODBCINST</envar>.
Overriding <envar>POSTGRESDIR</envar> on the make command line will cause
 <envar>LIBDIR</envar> and <envar>HEADERDIR</envar>
 to be rooted at the new directory you specify. 
<envar>ODBCINST</envar> is independent of <envar>POSTGRESDIR</envar>.
</Para>

<para>
Here is how you would specify the various destinations explicitly:

<programlisting>
% make BINDIR=<replaceable>bindir</replaceable> LIBDIR=<replaceable>libdir</replaceable> HEADERDIR=<replaceable>headerdir</replaceable> install
</programlisting>
</Para>

<para>
For example, typing

<programlisting>
% make POSTGRESDIR=/opt/psqlodbc install
</programlisting>

(after you've used
 <application>./configure</application> and <application>make</application>)
will cause the libraries and headers to be installed in the directories
<filename>/opt/psqlodbc/lib</filename>
 and <filename>/opt/psqlodbc/include/iodbc</filename> respectively.
</Para>

<para>
The command

<programlisting>
% make POSTGRESDIR=/opt/psqlodbc HEADERDIR=/usr/local install
</programlisting>

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.
</Para>
</step>
</procedure>
</sect2>
</sect1>

<sect1>
<title>Configuration Files</title>

<para>
<filename>~/.odbc.ini</filename> contains user-specified access information 
for the <productname>psqlODBC</productname> driver.