/ 
DBD-Ingres-0.33
River stage one • 1 direct dependent • 1 total dependent
/ DBD::Ingres

NAME

DBD::Ingres - DBI driver for Ingres database systems

SYNOPSIS

$dbh = DBI->connect("DBI:Ingres:$dbname", $user, $options, {AutoCommit=>0})
$sth = $dbh->prepare($statement)
$sth = $dbh->prepare($statement, {ing_readonly=>1})
$sth->execute
@row = $sth->fetchrow
$sth->finish
$dbh->commit
$dbh->rollback
$dbh->disconnect
and many more

DESCRIPTION

DBD::Ingres is an extension to Perl which allows access to Ingres databases. It is built on top of the standard DBI extension an implements the methods that DBI require.

This document describes the differences between the "generic" DBD and DBD::Ingres.

Extensions/Changes

returned types

The DBI docs state that:

    Most data is returned to the perl script as strings (null values are returned as undef). This allows arbitrary precision numeric data to be handled without loss of accuracy. Be aware that perl may not preserve the same accuracy when the string is used as a number.

This is not the case for Ingres.

Data is returned as it would be to an embedded C program:

    Integers are returned as integer values (IVs in perl-speak).

    Floats and doubles are returned as numeric values (NVs in perl-speak).

    Dates, moneys, chars, varchars and others are returned as strings (PVs in perl-speak).

get_dbevent

This non-DBI method calls GET DBEVENT and INQUIRE_INGRES to fetch a pending database event. If called without argument a blocking GET DBEVENT WITH WAIT is called. A numeric argument results in a call to GET DBEVENT WITH WAIT= :seconds.

In a second step INQUIRE_INGRES is called to fetch the related information, wich is returned as a reference to a hash with keys name, database, text, owner and time. The values are the dbevent* values received from Ingres. If no event was fetched, undef is returned. See t/event.t for an example of usage.

$event_ref = $dbh->func(10, 'get_dbevent')     # wait 10 secs at most
$event_ref = $dbh->func('get_dbevent')         # blocks

for (keys %$event_ref) {
  printf "%-20s = '%s'\n", $_, $event_ref->{$_};
}
connect
connect(dbi:Ingres:dbname[;options] [, user [, password]])

Options to the connection are passed in the datasource argument. This argument should contain the database name possibly followed by a semicolon and the database options.

Options must be given exactly as they would be given an ESQL-connect statement, ie. separated by blanks.

The connect call will result in a connect statement like:

CONNECT dbname IDENTIFIED BY user PASSWORD password OPTIONS=options

Eg.

local database
connect("mydb", "me", "mypassword")
with options and no password
connect("mydb;-Rmyrole/myrolepassword", "me")
Ingres/Net database
connect("thatnode::thisdb;-xw -l", "him", "hispassword")

and so on.

Important: The DBI spec defines that AutoCommit is ON after connect. This is the opposite of the normal Ingres default.

It is recommended that the connect call ends with the attributes { AutoCommit = 0 }>.

If you dont want to check for errors after every call use { AutoCommit = 0, RaiseError => 1 }> instead. This will die with an error message if any DBI call fails.

do
$dbh->do

This is implemented as a call to 'EXECUTE IMMEDIATE' with all the limitations that this implies.

Placeholders and binding is not supported with $dbh->do.

ChopBlanks and binary data

Fetching of binary data is not possible if ChopBlanks is set. ChopBlanks uses a \0 character internally to mark the end of the field, so returned will be truncated at the first \0 character.

ing_readonly

Normally cursors are declared READONLY to increase speed. READONLY cursors don't create exclusive locks for all the rows selected; this is the default.

If you need to update a row then you will need to ensure that either

  • the select statement contains an for update of clause, or

    = item *

    the $dbh->prepare calls includes the attribute {ing_readonly => 0}.

Eg.

$sth = $dbh->prepare("select ....", {ing_readonly => 0});

will be opened for update, as will

$sth = $dbh->prepare("select .... for direct update of ..")

while

$sth = $dbh->prepare("select .... for direct update of ..",
             { ing_readonly => 1} );

will be opened FOR READONLY.

When you wish to actually do the update, where you would normally put the cursor name, you put:

$sth->{CursorName}

instead, for example:

$sth = $dbh->prepare("select a,b,c from t for update of b");
$sth->execute;
$row = $sth->fetchrow_arrayref;
$dbh->do("update t set b='1' where current of $sth->{CursorName}");

Later you can reexecute the statement without the update-possibility by doing:

$sth->{ing_readonly} = 1;
$sth->execute;

and so on. Note that an update will now cause an SQL error.

In fact the "FOR UPDATE" seems to be optional, ie you can update cursors even if their SELECT statements do not contain a for update part.

If you wish to update such a cursor you must include the ing_readonly attribute.

NOTE DBD::Ingres version later than 0.19_1 have opened all cursors for update. This change breaks that behaviour. Sorry if this breaks your code.

ing_statement
$sth->{ing_statement}             ($)

Contains the text of the SQL-statement. Used mainly for debugging.

This is exactly the same as the new and DBI-supported $sth->{Statement} and the use of $sth->{ing_statement} is depreceated.

ing_types
$sth->{ing_types}              (\@)

Returns an array of the "perl"-type of the return fields of a select statement.

The types are represented as:

'i': integer

All integer types, ie. int1, int2 and int4.

These values are returned as integers. This should not cause loss of precision as the internal Perl integer is at least 32 bit long.

'f': float

The types float, float8 and money.

These values are returned as floating-point numbers. This may cause loss of precision, but that would occur anyway whenever an application referred to the data (all Ingres tools fetch these values as floating-point numbers)

's': string

All other supported types, ie. char, varchar, text, date etc.

TYPE
$sth->TYPE                       (\@)

See the DBI-docs for a description.

The ingres translations are:

short -> DBI::SQL_SMALLINT
int -> DBI::SQL_INTEGER
float -> DBI::SQL_DOUBLE
double -> DBI::SQL_DOUBLE
char -> DBI::SQL_CHAR
text -> DBI::SQL_CHAR
varchar -> DBI::SQL_VARCHAR
date -> DBI::SQL_DATE
money -> DBI::SQL_DECIMAL
decimal -> DBI::SQL_DECIMAL

Have I forgotten any?

ing_lengths
$sth->{ing_lengths}              (\@)

Returns an array containing the lengths of the fields in Ingres, eg. an int2 will return 2, a varchar(7) 7 and so on.

Note that money and date fields will have length returned as 0.

$sth->{SqlLen} is the same as $sth->{ing_lengths}, but the use of it is depreceated.

See also the C$sth->{PRECISION}> field in the DBI docs. This returns a 'reasonable' value for all types including money and date-fields.

ing_sqltypes
$sth->{ing_sqltypes}              (\@)

Returns an array containing the Ingres types of the fields. The types are given as documented in the Ingres SQL Reference Manual.

All values are positive as the nullability of the field is returned in $sth->{NULLABLE}.

See also the C$sth->{TYPE}> field in the DBI docs.

Not implemented

state
$h->state                (undef)

SQLSTATE is not implemented yet. It is planned for the (not so) near future.

disconnect_all

Not implemented

commit and rollback invalidates open cursors

DBD::Ingres should warn when a commit or rollback is isssued on a $dbh with open cursors.

Possibly a commit/rollback should also undef the $sth's. (This should probably be done in the DBI-layer as other drivers will have the same problems).

After a commit or rollback the cursors are all ->finish'ed, ie. they are closed and the DBI/DBD will warn if an attempt is made to fetch from them.

A future version of DBD::Ingres wil possibly re-prepare the statement.

This is needed for

Cached statements

A new feature in DBI that is not implemented in DBD::Ingres.

Procedure calls

It is not possible to call database procedures from DBD::Ingres.

A solution is underway for support for procedure calls from the DBI. Until that is defined procedure calls can be implemented as a DB::Ingres-specific function (like get_event) if the need arises and someone is willing to do it.

OpenIngres new features

The new features of OpenIngres are not (yet) supported in DBD::Ingres.

This includes BLOBS and spatial datatypes.

Support will be added when the need arises - if you need it you add it ;-)

NOTES

I wonder if I have forgotten something?

SEE ALSO

The DBI documentation in DBI.

AUTHORS

DBI/DBD was developed by Tim Bunce, <Tim.Bunce@ig.co.uk>, who also developed the DBD::Oracle that is the closest we have to a generic DBD implementation.

Henrik Tougaard, <htoug@cpan.org> developed the DBD::Ingres extension.