view release on metacpan or  search on metacpan

IngresII.pm  view on Meta::CPAN

received from Ingres. If no event was fetched, C<undef> is returned.
See F<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->{$_};
  }

=head2 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
C<INSERT>, C<DELETE> and C<UPDATE>. For them, a call to C<PREPARE> is
made, possible existing parameters are bound and a subsequent C<EXECUTE>
does the job. C<SELECT> isn't supported since $dbh->do doesn't give back
a statement handler hence no way to retrieve data.

=head2 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.

=head2 Long Data Types

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

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:

=over 4

=item *

When inserting blobs, use bind variables with types specified.

=item *

When fetching blobs, set LongReadLen and LongTruncOk in the $dbh.

=item *

Blob fields are returned as undef if LongReadLen is 0.

=back

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

=head2 ing_readonly

Normally cursors are declared C<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

=over 4

=item *

the C<select> statement contains an C<for update of> clause, or

=item *

the C<$dbh-E<gt>prepare> calls includes the attribute
C<{ing_readonly =E<gt> 0}>.

=back

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 C<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. B<Note> that an C<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 C<for update>
part.

If you wish to update such a cursor you B<must> include the C<ing_readonly>
attribute.

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

=head2 ing_rollback

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

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

Default value is B<off>.

B<NOTE> Since DBD::IngresII version 0.53 ing_rollback has also an impact
on the behavior on C<disconnect> . Earlier versions always did a
C<rollback>, when disconnecting while a transaction was active. Now
despite the state of C<AutoCommit> the action (rollback/commit) is
determined on the state of C<ing_rollback>. If it's on, a rollback is
done, otherwise a commit takes place. So if C<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 C<commit> at the end of the transaction,
before disconnecting...

=head2 ing_statement

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

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

=head2 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:

=over 4

=item '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.

=item '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)

=item 'l': long / blob

IngresII.pm  view on Meta::CPAN

=head2 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, C<ing_norm_bool>
returns undef which is translated by DBI to NULL.

=head2 ing_enable_utf8

    $dbh->{ing_enable_utf8} = 1;

By default, this flag is set to 0. When it is enabled, all strings (C<CHAR>,
C<VARCHAR>, C<C>, etc., but not C<NCHAR>/C<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 C<II_CHARSET> is set to C<UTF8>.

=head2 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.

=head2 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 C<execute> or
C<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 C<ing_empty_isnull> attribute in
database_handle will have no effect on statement handle.

By default it is set to 0.

=head1 FEATURES NOT IMPLEMENTED

=head2 state

  $h->state                (undef)

SQLSTATE is not implemented.

=head2 disconnect_all

Not implemented

=head2 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

=head2 Cached statements

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

=head2 bind_param_inout (Procedure calls)

It is possible to call database procedures from DBD::IngresII. It is B<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 L<get_event>) if the need arises and
someone is willing to do it.

=head1 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.

=head1 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;