1
<!DOCTYPE Book PUBLIC "-//OASIS//DTD DocBook V4.1//EN" [
3
<!ENTITY freedoc-license SYSTEM "../../doc/include/copying-fdl.sgml">
4
<!ENTITY www 'http://libdbi-drivers.sourceforge.net'>
5
<!ENTITY www-libdbi 'http://libdbi.sourceforge.net'>
7
<!-- customize these and the bookinfo section -->
8
<!ENTITY author-email 'qu1j0t3@sourceforge.net'>
9
<!ENTITY author-name 'Toby Thain'>
10
<!ENTITY dbd-name 'ingres'>
11
<!ENTITY proper-name 'Ingres'>
12
<!ENTITY www-upstream 'http://www.ingres.com/'>
16
<!-- $Id: dbd_ingres.sgml,v 1.15 2006/06/12 16:58:17 qu1j0t3 Exp $ -->
18
<book id="dbd-&dbd-name;">
21
<title>&proper-name; Driver Notes</title>
22
<subtitle>Details about the libdbi driver providing support for &proper-name; backends</subtitle>
24
<firstname>Toby</firstname>
25
<surname>Thain</surname>
27
<address><email>qu1j0t3@sourceforge.net</email></address>
30
<edition>Document revision: $Id: dbd_ingres.sgml,v 1.15 2006/06/12 16:58:17 qu1j0t3 Exp $</edition>
31
<pubdate>$Date: 2006/06/12 16:58:17 $</pubdate>
34
<revnumber>0.3</revnumber>
35
<date>2006-06-08</date>
40
<holder>Toby Thain</holder>
44
Permission is granted to copy, distribute and/or modify this
45
document under the terms of the <link linkend="copying-FDL">
46
<citetitle>GNU Free Documentation License</citetitle></link>,
47
Version 1.1 or any later version published by the Free Software
48
Foundation; with no Invariant Sections, with no Front-Cover Texts,
49
and with no Back-Cover Texts. A copy of the license is included in
50
<xref linkend="copying-FDL">.
55
<preface><title>Preface</title>
57
<ulink url="&www-libdbi;">libdbi</ulink> is a database abstraction
58
layer written in C. It implements a framework that can utilize
59
separate driver libraries for specific database servers. The <ulink
60
url="&www;">libdbi-drivers</ulink> project provides the drivers
61
necessary to talk to the supported database servers.
64
This manual provides information about the &dbd-name; driver, which
65
provides support for &proper-name; databases. The manual is
66
intended for programmers who write applications linked against libdbi
67
and who want their applications to use this database backend.
70
Questions and comments about this driver should be sent to the
71
libdbi-drivers-devel mailing list (see the <ulink
72
url="&www;">website</ulink>). Questions and comments about the libdbi
73
library should be sent to the appropriate libdbi mailing list.
76
This driver is maintained by <ulink url="mailto:&author-email;">&author-name;</ulink>.
80
<chapter id="intro"><title>Introduction</title>
82
<ulink url="http://ingres.com/products/Prod_Ingres_2006.html">Ingres(R)</ulink>
83
is an advanced relational database management system
84
from Ingres Corporation. Notable features include C2 security certification,
85
clustering, partitioning, transaction journaling, query optimisation,
86
diverse programming bindings and flexible licensing from GPL community
87
through to 24/7 enterprise level support.
90
More information can be found from the <ulink url="&www-upstream;">&proper-name; website</ulink>.
94
<chapter id="install"><title>Installation</title>
96
This chapter describes the prerequisites and the procedures to build
97
and install this driver from source code.
99
<section id="install-prereq"><title>Prerequisites</title>
101
The following packages must be installed on your system:
108
This library implements the core database abstraction
109
layer, which provides your application with database
110
backend functionality when paired with this driver.
111
More information about libdbi, including mailing
112
lists, documentation, bug reports, and downloads, is
113
available from the <ulink url="&www-libdbi;">libdbi
122
This is the C shared library to enable user programs
123
to communicate with the Ingres(R) 2006 Community Edition
127
The current version of the dbd_ingres driver was
128
written and tested with the 32-bit NPTL version II 9.0.4
129
for Linux/x86, running on Gentoo
130
(see <ulink url="http://slashdot.org/~toby/journal/136300">my installation notes</ulink>).
136
<section id="install-build"><title>Build and install the driver</title>
138
First you have to unpack the libdbi-drivers archive in a suitable
139
directory. Unpacking will create a new subdirectory with the
140
version number, such as <filename
141
class="directory">libdbi-drivers-0.8.0</filename>
143
<screen><prompt>$ </prompt><userinput>tar xfvz libdbi-drivers-0.8.0.tar.gz</userinput></screen>
145
The libdbi-drivers project consists of several drivers that use a
146
common build system. Therefore you <emphasis>must</emphasis> explicitly tell
147
the configuration script that you want to build the &dbd-name; driver (you
148
can list as many drivers as you want to build):
150
<screen><prompt>$ </prompt><userinput>cd libdbi-drivers-0.8.0</userinput></screen>
151
<screen><prompt>$ </prompt><userinput>./configure --with-&dbd-name;</userinput></screen>
153
Run <command>./configure --help</command> to find out about
154
additional options and other available drivers.
157
Then build the driver with the command:
159
<screen><prompt>$ </prompt><userinput>make</userinput></screen>
162
Please note that you may have to invoke
163
<command>gmake</command>, the GNU version of make, on some
168
Then install the driver with the command (you'll need root
169
permissions to do this):
171
<screen><prompt>$ </prompt><userinput>make install</userinput></screen>
173
To test the operation of the newly installed driver, use the
176
<screen><prompt>$ </prompt><userinput>make check</userinput></screen>
178
This command creates and runs a test program that performs a few
179
basic input and output tests.
183
If for some reason you need to re-create the
184
autoconf/automake-related files, try running <command
185
moreinfo="none">./autogen.sh</command>. In some situations,
186
the current stable autoconf/automake/libtool versions (as
187
found in FreeBSD 4.7 and Debian 3.0) do not cooperate well, so
188
it may be necessary to run the older autoconf 2.13. If
189
necessary, edit <filename
190
moreinfo="none">autogen.sh</filename> so that it will catch
191
the older autoconf version on your system.
197
<chapter id="options"><title>Driver options</title>
199
Before you can initiate a connection, you must usually specify options
200
that tell the database driver what to connect to. This driver supports
201
the standard options of
202
<varname>username</varname>, <varname>password</varname> and
203
<varname>dbname</varname>. The latter parameter is in Ingres syntax,
204
<literal>[node_id::]dbname[/svr_class]</literal> -
205
see Ingres documentation for details.
206
(When running as the Ingres admin user on the same host as the DBMS,
207
only the <literal>dbname</literal> is normally required.)
208
Sensible defaults will be used for all unspecified options.
211
This driver also offers the following non-standard options, and/or
212
redefines the meaning of the following standard options:
216
<term>ingres_autocommit</term>
219
Set this option to <emphasis>string value</emphasis> "0"
220
to disable Ingres autocommit mode
221
for the connection (it is enabled by default).
228
<chapter id="specific"><title>&proper-name;-specific Peculiarities</title>
230
This chapter lists known peculiarities of the &dbd-name; driver.
231
Wherever possible, nonstandard driver behavior is hidden by libdbi's
232
abstractions, but occasionally special considerations must be taken or
233
extra functionality may be utilized for particular drivers.
237
<term>Ingres environment variables must be set</term>
239
<para>The driver will not be loaded at all by <function>dbi_initialize()</function>
240
if the Ingres environment variables are not set (e.g. by <literal>~/.ingIIbash</literal>).
241
If the current user or specified user does not have appropriate Ingres privileges,
242
the connection will fail ("User authorization check failed" or similar).</para>
244
<para>When connecting, the driver uses the values of the
245
<literal>dbname</literal>, <literal>username</literal>
246
and <literal>password</literal> options, if set.</para>
250
<term>Autocommit</term>
252
<para>'Autocommit' mode is enabled by default for the DBMS connection.
253
In this mode, every query is committed as an individual transaction.</para>
255
<para>With autocommit disabled (see <literal>ingres_autocommit</literal> connection option),
256
a transaction is begun by the first query statement after connection, commit
257
or rollback. The transaction remains open until the next explicit commit,
258
rollback or disconnection (which implies commit). Commit and rollback
259
cannot be done using an OpenAPI query, but must use separate API functions.
260
To work around this, the Ingres driver makes a specific check
261
for COMMIT and ROLLBACK queries and calls the appropriate API function
262
instead of issuing the query.</para>
264
<para>See Ingres OpenAPI and SQL documentation for more information.</para>
268
<term>Handling of result sets</term>
270
<para>In the Ingres OpenAPI, result sets must be accessed sequentially.
271
Furthermore, Ingres does not provide the row count; this must be determined
272
by fetching rows until no more is available. For this reason, the driver
273
must fetch all result data into memory immediately after a query. Then
274
libdbi functions can be used to get random access to rows as usual.</para>
276
<para>A workaround, for example if your result sets
277
are very large, would be to use SELECT for random access; for example,
278
query once to get a set of key values, then issue SELECTs on individual rows
279
or smaller subsets of rows.</para>
281
<para>If your result set or BLOB data is likely to exhaust memory,
282
and workarounds such as the above are not appropriate, then you will
283
need to use OpenAPI directly to access them sequentially or by segments,
284
instead of using libdbi and this driver.</para>
288
<term>SEQUENCE functions and autocommit</term>
290
<para><function>dbi_conn_sequence_last()</function> is not usable in Ingres' autocommit mode
291
(the default). To use sequences to identify INSERTed rows, first
292
obtain the NEXT VALUE using <function>dbi_conn_sequence_next()</function> then use that
293
sequence value directly in a separate INSERT transaction.</para>
297
<term>DATE parsing</term>
299
<para>Parsing DATE columns into timestamp values will only work correctly when
300
returned values are in the default (US) date format; i.e.
301
<literal>dd-mmm-yyyy [hh:mm:ss]</literal> where <literal>mmm</literal>
302
is a partial month name. The parsing routine will accept
303
any separators and either a numeric or named month, but the D-M-Y
304
ordering is required. If needed, a workaround for date format
305
incompatibility is to format into a string using a SQL function
306
such as <function>date_gmt(date)</function>.</para>
310
<term>Version string format</term>
312
<para><function>dbi_conn_get_engine_version_string()</function> returns a
313
string like <literal>II 9.0.4 (int.lnx/104)NPTL</literal>
314
(internally this is implemented as <literal>SELECT dbmsinfo('_version')</literal>.</para>
318
<term>Getting database and table lists</term>
320
<para><function>dbi_conn_get_table_list()</function> returns matching objects
321
of all types - tables, views, indexes and partitions -
322
in the <literal>iitables</literal> catalog for the named database
323
(except those beginning with <literal>ii</literal>, which are reserved for Ingres).
324
These names are space padded fixed width fields.</para>
328
<term>Logging verbosity</term>
330
<para>The driver respects <function>dbd_set_verbosity()</function> level in reporting messages.
331
At verbosity 1, only errors and abnormalities are reported;
332
at 2, more information is shown; at 3, detailed debugging information is shown.</para>
336
<term>Blank handling</term>
338
<para>Blanks are preserved in column values (except that DECIMAL and MONEY columns
339
are converted to strings, and then stripped of trailing blanks).
340
Note that names returned by <function>dbi_conn_get_table_list()</function>
341
and <function>dbi_conn_get_db_list()</function> are padded with blanks
342
to a fixed width.</para>
346
<term>Error messages</term>
348
<para>The Ingres OpenAPI may return multiple error messages for a given query/operation.
349
The <function>dbi_conn_error()</function> returns the error code only for the
350
last error message, but the message text will include all relevant messages. At verbosity level 2,
351
errors directly relating to libdbi function calls are logged. At verbosity 3,
352
all errors are logged including those caused by API calls made internally by the driver.</para>
354
<para>Environment variables <literal>II_API_TRACE</literal> and
355
<literal>II_API_LOG</literal> control logging by the API itself.
356
See Chapter 6 of the Ingres OpenAPI User Guide for details.</para>
360
<term>Connection options</term>
362
<para>All Ingres option values are specified as <emphasis>strings</emphasis>
363
(using <function>dbi_conn_set_option()</function>).</para>
367
<term><literal>ingres_blobsegment</literal> option</term>
369
<para>This option specifies the size of individual segments
370
returned for BLOB columns (by default this is approx 2000 bytes).
371
If your BLOBs are large, increasing this value reduces the number
372
of API calls required by the Ingres driver.</para>