ABSTRACT

The Vip application is designed to run with one of two database systems: mysql or Oracle. Hence SQL statements should follow strictly the basic SQL standard.

The definition of tables and constraints is done in scripts of which there are always two versions, one for mysql, the other for Oracle. All other scripts - which access and/or update data on the database - should be written in a way, that they run with both database systems.

This class defines methods to access the database. It is a class with class methods only. Connection to the database is handled internally in this class.

The module is designed for mod_perl. It keeps the database handle as long at it lives, i.e. it connects to the VIP database when a method which needs the database is called, and disconnects only when the module is unloaded.

To be independent of the underlying database (Oracle or mysql), the follwing convention should be used when reading or writing data from/to the database:


SYNOPSIS

  use VipDB;
  ($v1, $v2, $v3) = VipDB->selectrow("select col1, col2, col3 from tablex where ...");
  $single_val = VipDB->selectrow("select col from tablex where ...");
  $aref = VipDB->selectall("select col1, col2, col3 from tablex");
  foreach my $row (@$aref) {
      my($v1, $v2, $v3) = @$row;
      ...
  }
  $rc = $VipDB->do("delete from tablex where ...");
  VipDB->commit;
  ...
  $new = "contains ' and \\ and \"";
  $rc = VipDB->do('update tablez set col1=' . VipDB->quote($new) );
  VipDB->commit;
  ...
  $rc = VipDB->do('insert into x (a, b) values (1,2)');
  $key = VipDB->last_inserted_key;
  VipDB->commit;


DESCRIPTION

Methods

$rc = VipDB->do( $sql );
Calls DBI method do and returns its return value. $sql can be any SQL statement except select.

$integer = VipDB->last_inserted_key( );
If the last Vip->do() call executed a SQL insert statement, the key of the inserted row can be retrieved with a call to method VipDB->last_inserted_key before VipDB->commit is called.

VipDB->commit( );
Terminates a transaction. There is no rollback method.

$string = VipDB->quote( $string );
Calls DBI method quote and returns its return value. Empty strings which will be stored in varchar fields should be converted to undef: $quoted_string = VipDB->quote( $string eq '' ? undef : $string)

$array_ref = VipDB->selectall( $sql );
Calls DBI method selectall_arrayref and returns its return value. $sql should be a select statement.

$scalar = VipDB->selectrow( $sql );
@array = VipDB->selectrow( $sql );
Calls DBI method selectrow_array. In array context, the array returned by selectrow_array is returned, in scalar context the first element of that array. $sql should be a select statement which returns one row of the database.

Definition of Autoincrementing Keys in Oracle

Create a sequence which may be used for all tables.

 VipDB->do("create sequence Xseq");
 VipDB->commit;

Create a table and a trigger:

 VipDB->do("create table T
            ( id number(12) not null,
              amount number(10,2) not null,
              constraint pk_T primary key (id)
            )"
           );
 VipDB->do("create or replace trigger id_T\n" .
           "before insert on T\n" .
           "for each row\n" .
           "begin\n" .
           "select Xseq.nextval into :new.id from dual;\n" .
           "end id_T;\n" .
           "/\n"
          );
 VipDB->commit;

Definition of Autoincrementing Keys in mysql

Create the table:

 VipDB->do("create table T
            ( id mediumint not null auto_increment,
              amount decimal(10,2) not null,
              primary key (id)
            )"
           );
 VipDB->commit;

Inserting a row will be the same for Oracle and mysql:

 VipDB->do ( "insert into T (amount) values (1234567.00)" );
 my $id = VipDB->last_inserted_key;
 VipDB->commit;


SEE ALSO

Access to the database through CPAN DBI.pm.


COPYRIGHT

Copyright 2004-2013 Thedi gerber@id.ethz.ch