NAME

Ingperl - Perl access to Ingres databases for old ingperl scripts

SYNOPSIS

&sql('...');
&sql_exec('...');
@values = &sql_fetch;
&sql_close;

@types = &sql_types;
@ingtypes = &sql_ingtypes;
@lengths = &sql_lengths;
@nullable = &sql_nullable;
@names = &sql_names;

$sql_version
$sql_error
$sql_sqlcode
$sql_rowcount
$sql_readonly
$sql_showerrors
$sql_debug

@row1 = &sql_eval_row1('select ...');
@col1 = &sql_eval_col1('select ...');

DESCRIPTION

Ingperl is an extension to Perl which allows access to Ingres databases.

The functions that make up the interface are described in the following sections.

All functions return false or undefined (in the Perl sense) to indicate failure.

The text in this document is largely unchanged from the original Perl4 ingperl documentation written by Tim Bunce (timbo@ig.co.uk).

Any comments specific to the DBD::Ingres Ingperl emulation are prefixed by DBD:.

IngPerl Functions

Ingperl function, that access data.

• sql

  &sql('...');

  This functions should be used to

  connect to a database:

  &sql("connect database_name [-sqloptions]");

  where sqloptions are the options defined in the manual for the sql command.

  For example:

  &sql("connect database_name identified by username -xw -Rrole -Ggroup -l");

  Returns true else undef on error.

  DBD: Note that the options must be given in the order database-name username other-options otherwise the check for username wil fail. It is a rather simple scan of the option-string. Further improvements are welcome.

  DBD: The connect must be given as "connect database-name identified by username SQL-options" or "connect database-name -uusername SQL-options".

  DBD:There is (yet) no way of passing a password to the connect call. Suggestion: add "password=password" just after username.

  disconnect from a database:

  &sql("disconnect");

  Note that ingperl will complain if a transaction is active.

  You should &sql_exec 'commit' or 'rollback' before disconnect.

  DBD: The warning on disconnect has another wording now: Ingres: You should commit or rollback before disconnect. Ingres: Any outstanding changes have been rolledback. DBD: Please note the rollback.

  Returns true else undef on error (unlikely!).

  Note that an ingres bug means that $sql_error will contain an error message (E_LQ002E query issued outside of a session) even though the disconnect worked ok.

  DBD: Must check if this is still the case. Don't think so though...

  prepare a statement:

  &sql("select ...");

  Returns true else undef on error.

  If a non-select statement is prepared it will be executed at once.

  DBD: A non-select statement return rowcount ("0E0", 1, 2, ..), while a select statement returns 0. This is the same value as sqlca.sqlerrd[2].

  This function cannot be used to prepare the following statements:

  call,
  get dbevent,
  inquire_sql,
  execute immediate,
  execute procedure,
  execute,
  prepare to commit,
  prepare,
  set.

  Some of these can be performmed by the &sql_exec() function.

  DBD: This is no longer true! There is no difference between the SQL-statements that &sql and &sql_exec can execute. &sql hands off all non-select statements to &sql_exec.

• sql_exec

  &sql_exec('...');

  Execute an sql statement immediately. This function should be used to issue Commit, Rollback, Insert, Delete and Update statements.

  Returns true else undef on error.

  DBD: A non-select statement return rowcount ("0E0", 1, 2, ..), while a select statement returns 0. This is the same value as sqlca.sqlerrd[2].

  It is also often used to execute 'set' commands. For example:

  &sql_exec('set autocommit on');
  &sql_exec('set session with on_error=rollback transaction');
  &sql_exec('set lockmode readlock=nolock');
  &sql_exec('set qep');

  This function cannot be used to prepare the following statements:

  call,
  get dbevent,
  inquire_sql,
  prepare to commit.

• sql_fetch

  @values = &sql_fetch;

  Fetch the next record of data returned from the last prepared select statement. When all records have been returned &sql_fetch will close the select statement cursor and return an empty array.

  For example:

  &sql('select * from iitables') || die $sql_error;
  while(@values = &sql_fetch){
      ...
  }

  Null values are returned as undef elements of the array.

  DBD: &sql_fetch can also be expressed as either &sql("fetch") or &sql_exec("fetch") - to cater for Ingperl 1.0 scripts!

  DBD: &sql_fetch will call &sql_close when the last row of data has been fetched. This has been the way it was supposed to be...

  DBD: &sql_fetch will die with the error Ingperl: No active cursor if an error has occured in the &sql(select..)-statement.

  DBD: $scalar = &sql_fetch returns the first column of data if $sql_sth->{CompatMode} is set; this is the default mode for Ingperl and is the expected behaviour for Perl4. In Perl5 (and with $sql_sth->{CompatMode} unset) the number of columns will be returned. The warning Multi-column row retrieved in scalar context is given if $sql_sth->{Warn} is true.

  DBD: Text columns are returned with trailing blanks if $sql_sth->{CompatMode} is set. Otherwise the trailings blanks are stripped. The default for Ingperl is to have $sql_sth->{CompatMode} set.

• sql_close

  &sql_close;

  This function needs to be called only if you do not use &sql_fetch to fetch all the records and you wish to close the cursor as soon as possible (to release locks etc). Otherwise ignore it. Always returns true.

  DBD: If $sql_sth->{Warn} is false the warning Ingperl: close with no open cursor will be given whenever a closed cursor is reclosed. The default behaviour is to omit the warning.

IngPerl Functions to describe the currently prepared statement. These functions all return an array with one element for each field in the query result.

• sql_types

  @types = &sql_types;

  Returns a list of sprintf type letters to indicate the generic type of each field: d - int f - float s - string

• sql_ingtypes

  @ingtypes = &sql_ingtypes;

  Returns a list of specific ingres type numbers: 3 - date 5 - money 30 - integer 31 - float 20 - char 21 - varchar

• sql_lengths

  @lengths = &sql_lengths;

  Returns a list if ingres data type lengths.

  For strings the length is the maximum width of the field.

  For numbers it is the number of bytes used to store the binary representation of the value, 1, 2, 4 or 8.

  For date and money fields it is 0.

  DBD: This was not documented in the Ingperl documentation, but is, as far as I can discover, how it used to work.

• sql_nullable

  @nullable = &sql_nullable;

  Returns a list of boolean values (0 or 1's). A 1 indicates that the corresponding field may return a null value.

• sql_names

  @names = &sql_names;

  Returns a list of field names.

IngPerl Variables

• $sql_version (read only)

  A constant string compiled into ingperl containing the major and minor version numbers of ingperl, the patchlevel and the date that the ingperl binary was built.

  For example: ingperl 2.0 pl0 (built Apr 8 1994 13:17:03)

  DBD: The variable gives a similar output now, including the Ingperl version and the DBD::Ingres version.

• $sql_error (read only)

  Contains the error message text of the current Ingres error.

  Is empty if last statement succeeded.

  For example: print "$sql_error\n" if $sql_error;

• $sql_sqlcode (read only)

  The current value of sqlda.sqlcode. Only of interest in more sophisticated applications.

  Typically 0, <0 on error, 100=no more rows, 700=message, 710=dbevent.

• $sql_rowcount (read only)

  After a successful Insert, Delete, Update, Select, Modify, Create Index, Create Table As Select or Copy this variable holds the number of rows affected.

• $sql_readonly (default 1)

  If true then prepared sql statements are given read only cursors this is generally a considerable performance gain.

  DBD: Not implemented. All cursors are readonly - there is no way to modify the value of a cursor element, therefore no reason not to make the cursors readonly. The value of this variable was ignored already in Ingperl 2.0.

• $sql_showerrors (default 0)

  If true then ingres error and warning messages are printed by ingperl as they happen. Very useful for testing.

  DBD: Same as $sql_dbh->{printerror}

• $sql_debug (default 0)

  If ingperl has been compiled with -DINGPERL_DEBUG then setting this variable true will enable debugging messages from ingperl internals.

  DBD: Setting this variable will enable debugging from the Ingperl emulation layer. Setting the variable $DBI::dbi_debug enables debug output from the DBI and DBD layers (the value 3 will result in large amounts of output including internal debug of DBD::Ingres).

• $sql_drh

  DBD: This variable is the DBI-internal driver handle for the DBD::Ingres driver. This is rarely used!

• $sql_dbh

  DBD: This variable is the DBI database handle. It can be used to add DBI/DBD statements to an old Ingperl script.

• $sql_sth

  DBD: This is the DBI statement handle for the current SELECT-statement (if any).

IngPerl Library Functions

• sql_eval_row1

  @row1 = &sql_eval_row1('select ...');

  Execute a select statement and return the first row.

  DBD: This is executed in a separate cursor and can therefore be executed while a &sql_fetch-loop is in progres.

• sql_eval_col1

  @col1 = &sql_eval_col1('select ...');

  Execute a select statement and return the first column.

  DBD: As &sql_eval_col1 this is executed in a separate cursor.

NOTES

The DBD::Ingres module has been modelled closely on Tim Bunce's DBD::Oracle module and warnings that apply to DBD::Oracle and the Oraperl emulation interface may also apply to the Ingperl emulation interface.

Your mileage may vary.

WARNINGS

IngPerl comes with no warranty - it works for me - it may not work for you. If it trashes your database I am not responsible!

This file should be included in all applications using ingperl in order to help ensure that scripts will remain compatible with new releases of ingperl.

DBD: The following warning is taken (almost) verbatim from the oraperl emulation module, but is also valid for Ingres.

The Ingperl emulation software shares no code with the original ingperl. It is built on top the the new Perl5 DBI and DBD::Ingres modules. These modules are still evolving. (One of the goals of the Ingperl emulation software is to allow useful work to be done with the DBI and DBD::Ingres modules whilst insulation users from the ongoing changes in their interfaces.)

It is quite possible, indeed probable, that some differences in behaviour will exist. This should be confined to error handling.

All differences in behaviour which are not documented here should be reported to ht@datani.dk and CC'd to dbi-users@fugue.com.

SEE ALSO

Ingres Documentation

SQL Reference Guide

Books Programming Perl by Larry Wall, Randal Schwartz and Tom Christiansen. Learning Perl by Randal Schwartz.

Manual Pages

perl(1)

AUTHORS

Formerly sqlperl by Ted Lemon.

Perl4 version developed and maintained by Tim Bunce, <Tim.Bunce@ig.co.uk> Copyright 1994 Tim Bunce and Ted Lemon

Ingperl emulation using DBD::Ingres by Henrik Tougaard <ht@datani.dk>

Perl by Larry Wall <lwall@netlabs.com>.

cpanm DBD::Ingres

perl -MCPAN -e shell
install DBD::Ingres