NAME

DBD::IngresII - DBI driver for Actian Ingres and Actian Vectorwise RDBMS

SYNOPSIS

    $dbh = DBI->connect("DBI:IngresII:$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::IngresII is a database driver for the perl DBI system that allows access to Ingres and Vectorwise databases. It is built on top of the standard DBI extension and implements the methods that DBI requires.

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

EXTENSIONS/CHANGES

Connect

  DBI->connect("DBI:IngresII:dbname[;options]");
  DBI->connect("DBI:IngresII:dbname[;options]", user [, password]);
  DBI->connect("DBI:IngresII:dbname[;options]", user [, password], \%attr);

To use DBD::IngresII call connect specifying a datasource option beginning with "DBI:Ingres:", followed by the database instance name and optionally a semi-colon followed by any Ingres connect options.

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

The connect call will result in a connect statement like:

  CONNECT dbname IDENTIFIED BY user PASSWORD password OPTIONS=options

E.g.,

local database

  DBI->connect("DBI:IngresII:mydb", "me", "mypassword")

with options and no password

  DBI->connect("DBI:IngresII:mydb;-Rmyrole/myrolepassword", "me")

dynamic vnode

  DBI->connect("DBI:IngresII:@localhost,tcp_ip,II;[login,password]::dbname")

Ingres/Net database

  DBI->connect("DBI:IngresII:thatnode::thisdb;-xw -l", "him", "hispassword")

and so on.

AutoCommit Defaults to ON

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

To reflect this behavior in your code, it is recommended that the connect call ends with the attributes { AutoCommit => 0 }.

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).

This does not cause loss of precision, because the Ingres API uses these types to return the data anyway.

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->{$_};
  }

do

$dbh->do is implemented as a call to 'EXECUTE IMMEDIATE' with all the limitations that this implies. An exception to that are the DML statements INSERT, DELETE and UPDATE. For them, a call to PREPARE is made, possible existing parameters are bound and a subsequent EXECUTE does the job. SELECT isn't supported since $dbh->do doesn't give back a statement handler hence no way to retrieve data.

Binary Data

Fetching binary data from char and varchar fields is not guaranteed to work, but probably will most of the time. Use 'BYTE' or 'BYTE VARYING' data types in your database for full binary data support.

Long Data Types

DBD::IngresII supports the LONG VARCHAR and LONG BYTE data types as detailed in "Handling BLOB / LONG / Memo Fields" in DBI.

The default value for LongReadLen in DBD::IngresII is 2GB, the maximum size of a long data type field. DBD::IngresII dynamically allocates memory for long data types as required, so setting LongReadLen to a large value does not waste memory.

In summary:

When inserting blobs, use bind variables with types specified.
When fetching blobs, set LongReadLen and LongTruncOk in the $dbh.
Blob fields are returned as undef if LongReadLen is 0.

Due to their size (and hence the impracticality of copying them inside the DBD driver), variables bound as blob types are always evaluated at execute time rather than bind time. (Similar to bind_param_inout, except you don't pass them as references.)

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
the $dbh->prepare calls includes the attribute {ing_readonly => 0}.

E.g.,

  $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, i.e., 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::IngresII version later than 0.19_1 have opened all cursors for update. This change breaks that behaviour. Sorry if this breaks your code.

ing_rollback

The DBI docs state that 'Changing AutoCommit from off to on will trigger a commit'.

Setting ing_rollback to on will change that to 'Changing AutoCommit from off to on will trigger a rollback'.

Default value is off.

NOTE Since DBD::IngresII version 0.53 ing_rollback has also an impact on the behavior on disconnect . Earlier versions always did a rollback, when disconnecting while a transaction was active. Now despite the state of AutoCommit the action (rollback/commit) is determined on the state of ing_rollback. If it's on, a rollback is done, otherwise a commit takes place. So if AutoCommit is off, and you disconnect without commiting, all your work would be treated like one big transaction.

Please take that in mind: This is just due to compatibility to other databases. Correct would be a commit at the end of the transaction, before disconnecting...

ing_statement

This has long been deprecated in favor of $sth->{Statement}, which is a DBI standard.

$sth->{ing_statement} provides access to the SQL statement text.

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, i.e., 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)

'l': long / blob

Either of the two long datatypes, long varchar or long byte.

's': string

All other supported types, i.e., char, varchar, text, date etc.

'n': UTF-16 string

UTF-16 types - nchar or nvarchar.

Ingres Types and their DBI Equivalents

  $sth->TYPE                       (\@)

See DBI for a description. The Ingres translations are:

short -> DBI::SQL_SMALLINT
int -> DBI::SQL_INTEGER
float -> DBI::SQL_DOUBLE
double -> DBI::SQL_DOUBLE
boolean -> DBI::SQL_BOOLEAN
c -> DBI::SQL_CHAR
char -> DBI::SQL_CHAR
nchar -> DBI::SQL_BINARY
nvarchar -> DBI::SQL_VARBINARY
text -> DBI::SQL_CHAR
byte -> DBI::SQL_BINARY
varchar -> DBI::SQL_VARCHAR
byte varying -> DBI::SQL_VARBINARY
ansidate -> DBI::SQL_DATE
timestamp -> DBI::SQL_DATETIME
timestamp with time zone -> DBI::SQL_DATETIME
timestamp with local time zone -> DBI::SQL_DATETIME
time -> DBI::SQL_TIME
time with time zone -> DBI::SQL_TIME
time with local time zone -> DBI::SQL_TIME
money -> DBI::SQL_DECIMAL
decimal -> DBI::SQL_DECIMAL
long varchar -> DBI::SQL_LONGVARCHAR
long byte -> DBI::SQL_LONGVARBINARY
interval year to month -> DBI::SQL_INTERVAL_YEAR_TO_MONTH
interval day to second -> DBI::SQL_INTERVAL_DAY_TO_SECOND

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 deprecated.

See also the $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 $sth->{TYPE} field in the DBI docs.

ing_ph_ingtypes

    $sth->{ing_ph_ingtypes}           (\@)

Returns an array containing the Ingres types of the columns the placeholders represent. This is a guess from the context of the placeholder in the prepared statement. Be aware, that the guess isn't always correct and sometypes a zero (illegal) type is returned. Plus negative values indicate nullability of the parameter. A $sth->{ing_ph_nullable} field is to be implemented yet.

ing_ph_inglengths

    $sth->{ing_ph_inglengths}         (\@)

Returns an array containing the lengths of the placeholders analog to the $sth->{ing_lengths} field.

ing_utf8_quote

    # Returns q{U&'Chrz\+000105szcz'}
    $dbh->ing_utf8_quote('Chrząszcz');

Returns quoted string (which prevents SQL injection) with escaped UTF-8 literals.

ing_bool_to_str

    # Returns 'TRUE':
    $dbh->ing_bool_to_str($hashref->{some_kind_of_true_bool);

Converts boolean returned from Ingres into string. For undef it returns 'NULL', for 0 - 'FALSE' and for 1 'TRUE'.

ing_norm_bool

    # Returns 1:
    $dbh->ing_norm_bool(34);

If supplied scalar is true, it returns 1, otherwise it returns 0. There's one special case - when supplied scalar is undef, ing_norm_bool returns undef which is translated by DBI to NULL.

ing_enable_utf8

    $dbh->{ing_enable_utf8} = 1;

By default, this flag is set to 0. When it is enabled, all strings (CHAR, VARCHAR, C, etc., but not NCHAR/NVARCHAR) retrieved from database which can be interpreted as valid UTF-8 (but not as valid ASCII), will have scalar's ("scalar" means "variable" in Perl world) UTF-8 flag set on.

Note that you should use this attribute only if II_CHARSET is set to UTF8.

ing_is_vectorwise

    # Returns 1 if $dbh is connected to Vectorwise, 0 if it is connected to
    # Ingres

    $dbh->ing_is_vectorwise

This method checks whether database handle is connected to Actian Vectorwise database.

ing_empty_isnull

    $dbh->{ing_empty_isnull} = 1;
    # or:
    $sth->{ing_empty_isnull} = 1;

When this attribute is set to 1, then all empty strings passed to execute or bind_param will be interpreted as NULLs by Ingres.

If you are using this attribute only for statement handle, then you need to set it before binding params, so it will be honoured.

After creation of statement handle, setting ing_empty_isnull attribute in database_handle will have no effect on statement handle.

By default it is set to 0.

FEATURES NOT IMPLEMENTED

state

  $h->state                (undef)

SQLSTATE is not implemented.

disconnect_all

Not implemented

commit and rollback invalidate open cursors

DBD::IngresII 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, i.e., they are closed and the DBI/DBD will warn if an attempt is made to fetch from them.

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

This is needed for

Cached statements

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

bind_param_inout (Procedure calls)

It is possible to call database procedures from DBD::IngresII. It is NOT possible to get return values from the procedure.

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.

UNICODE FAQ

In this section I will answer some questions about Unicode and Ingres.

    Q: What is Unicode, and what is UTF-8, are these different words for same
       thing?

    A: Please read perlunitut, especially the "Definitions" section. To read it
       run "perldoc perlunitut" command or type "perlunitut" in your web search
       engine of choice.

    Q: Is it possible to change II_CHARSET after installation of Ingres?

    A: No, it would corrupt database. You need to reinstall Ingres, this time
       with other II_CHARSET.

    Q: I tried your examples and all I get is some garbage.

    A: There are few possibilites what went wrong:

         - You have created database with "createdb -n dbname", not
           "createdb -i dbname".

         - You are printing string to console without encoding it to console
           charset. For example, for polish Windows you need to encode it to
           cp852 encoding.

UNICODE EXAMPLES

You want to store or retrieve unicode string from Ingres database? Like with everything in Perl, there's more than one way to do it (TMTOWTDI). Here are some examples:

    # Example number one, it uses NVARCHAR, and assumes that II_CHARSET is set
    # to UTF8

    # Database must be created with "createdb -i dbname"

    use utf8;

    use Encode;

    my $dbh = DBI->connect("DBI:IngresII:dbname");
    my $sth = $dbh->prepare("CREATE TABLE foobar (str nvarchar(10))");
    $sth->execute;
    $sth = $dbh->prepare("INSERT INTO foobar values (?)");
    $sth->execute(encode('utf-8', 'ąść')); # Instead of utf-8 use charset
                                           # that is specified in II_CHARSET

    $sth = $dbh->prepare("SELECT * FROM foobar");
    $sth->execute;
    my $hashref = $sth->fetchrow_hashref;

    my $variable = decode('utf-16le', $hashref->{str});

Second one:

    # Example number two, it uses VARCHAR, it will work only with II_CHARSET
    # set to UTF8.

    # Database must be created with "createdb -i dbname"

    use utf8;

    use Encode;

    my $dbh = DBI->connect("DBI:IngresII:dbname");
    my $sth = $dbh->prepare("CREATE TABLE foobar (str varchar(10))");
    $sth->execute;
    $sth = $dbh->prepare("INSERT INTO foobar values (?)");
    $sth->execute('ąść');

    $sth = $dbh->prepare("SELECT * FROM foobar");
    $sth->execute;
    my $hashref = $sth->fetchrow_hashref;

    my $variable = decode('utf-8', $hashref->{str});

Third:

    # Example number three, it uses VARCHAR, it will work only with II_CHARSET
    # set to UTF8.
    # Now we will use automatic UTF-8 handling.

    # Database must be created with "createdb -i dbname"

    use utf8;

    use Encode;

    my $dbh = DBI->connect("DBI:IngresII:dbname");
    $dbh->{ing_enable_utf8} = 1; # Enable UTF-8 support
    my $sth = $dbh->prepare("CREATE TABLE foobar (str varchar(10))");
    $sth->execute;
    $sth = $dbh->prepare("INSERT INTO foobar values (?)");
    $sth->execute('ąść');

    $sth = $dbh->prepare("SELECT * FROM foobar");
    $sth->execute;
    my $hashref = $sth->fetchrow_hashref;

    my $variable = $hashref->{str}; # No need to decode.

Fourth:

    # Example number three, it uses VARCHAR, it will work only with II_CHARSET
    # set to UTF8.
    # Now we will use automatic UTF-8 handling.

    # Database must be created with "createdb -i dbname"

    use utf8;

    use Encode;

    my $dbh = DBI->connect("DBI:IngresII:dbname");
    $dbh->{ing_enable_utf8} = 1; # Enable UTF-8 support
    my $sth = $dbh->prepare("CREATE TABLE foobar (str varchar(10))");
    $sth->execute;
    my $input = $dbh->ing_utf8_quote('ąść');
    $sth = $dbh->prepare("INSERT INTO foobar values ($input)");
    $sth->execute;

    $sth = $dbh->prepare("SELECT * FROM foobar");
    $sth->execute;
    my $hashref = $sth->fetchrow_hashref;

    my $variable = $hashref->{str}; # No need to decode.

INSTALLATION

You can install DBD::IngresII manually, or use CPAN.

Manual installation with basic tests (if you are using Windows, use set instead of export and dmake or nmake instead of make:
```
    perl Makefile.PL
    export DBI_DSN=<my-favourite-test-database-dsn>
    make
    make test
    make install
```
Instead of DBI_DSN, you can use DBI_DBNAME which should contain name of desired test database.

Manual installation with full tests (requires database created with -i flag and II_CHARSET must be set to UTF8).

    perl Makefile.PL
    export DBI_TEST_NCHAR=1
    export DBI_TEST_UTF8=1
    export DBI_DSN=<my-favourite-test-database-dsn>
    make
    make test
    make install

Automatic installation with CPAN.

    export DBI_DSN=<my-favourite-test-database-dsn>
    cpan install DBD::IngresII

SUCCESSFULLY TESTED PLATFORMS

Ingres 10S Enterprise Build 126 + Solaris 10 + gcc on x86
Vectorwise 2.5.1 Enterprise Build 162 + Windows + Visual C++ on x64
Ingres 10.1 Community Build 125 + Windows + Visual C++ on x64
Ingres 10.1 Community Build 125 + Windows + MinGW on x64
Ingres 10.1 Community Build 125 + Linux + gcc on x64
Ingres 10S Enterprise Build 126 + Windows + Visual C++ on x86
Ingres 10.1 Community Build 121 + Windows + Visual C++ on x86
Ingres 10.1 Community Build 121 + Windows + MinGW on x86
Ingres 10.1 Community Build 121 + Windows + Visual C++ on x64
Ingres 10.1 Community Build 121 + Windows + MinGW on x64
Ingres 10.1 Community Build 120 + Linux + gcc on x86
Ingres 10.1 Community Build 120 + Linux + gcc on x64
Ingres 9.2.3 Enterprise Build 101 + Windows + Visual C++ on x86

NOTES

$dbh->table_info, $dbh->column_info, $dbh->get_info

The table_info and column_info functions are just working against tables. Views and synonyms still have to be implemented. The get_info function returns just the newer version strings correctly, since I'm still looking for documentation for the older ones.

I wonder if I have forgotten something?

IF YOU HAVE PROBLEMS

There are few places where you can seek help:

Actian community forums - http://community.actian.com/forum/
Ingres usenet group - comp.databases.ingres
Myself - me@xenu.tk

FOSSIL REPOSITORY

DBD::IngresII Fossil repository is hosted at xenu.tk:

    http://code.xenu.tk/repos.cgi/dbd-ingresii

REPORTING BUGS

Please report bugs at CPAN RT:

    https://rt.cpan.org/Public/Dist/Display.html?Name=DBD-IngresII

Please include full details of which version of Ingres/esql, operating system and Perl you're using. If you are on Windows, include name of Perl distribution that you are using (i.e. "ActivePerl" or "Strawberry Perl").

KNOWN PROBLEMS

TODO

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.

Stefan Reddig, <sreagle@cpan.org> is currently (2008) adopting it to include some more features.

Tomasz Konojacki <me@xenu.tk> has forked DBD::Ingres to DBD::IngresII.

CONTRIBUTORS

Sorted from latest contribution to first one. If I forgot about someone, mail me at me@xenu.tk.

Dennis Roesler
Geraint Jones
Remy Chibois
Mike Battersby
Tim Bunce
Dirk Kraemer
Sebastian Bazley
Bernard Royet
Bruce W. Hoylman
Alan Murray
Dirk Koopman
Ulrich Pfeifer
Jochen Wiedmann
Gil Hirsch
Paul Lindner

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)