The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.


DBIx::CGI - Easy to Use DBI interface for CGI scripts


  use CGI;
  my $cgi = new CGI;
  use DBIx::CGI;
  my $dbi_interface = new DBIx::CGI ($cgi, qw(Pg template1));

  $dbi_interface -> insert ('transaction',
                   id => serial ('transaction', 'transactionid'),
                   time => \$dbi_interface -> now);


DBIx::CGI is an easy to use DBI interface for CGI scripts. Currently only the Pg, mSQL and mysql drivers are supported.


  $dbi_interface = new DBIx::CGI ($cgi qw(Pg template1));

The required parameters are a CGI object, the database driver and the database name. Additional parameters are the database user.


  sub fatal {
    my ($statement, $err, $msg) = @_;
    die ("$0: Statement \"$statement\" failed (ERRNO: $err, ERRMSG: $msg)\n");
  $dbi_interface -> install_handler (\&fatal);

If any of the DBI methods fails, either die will be invoked or an error handler installed with install_handler will be called.


process statement
  $sth = $dbi_interface -> process ("SELECT * FROM foo");
  print "Table foo contains ", $sth -> rows, " rows.\n";

Processes statement by just combining the prepare and execute steps of the DBI. Returns statement handle in case of success.

insert table column value [column value] ...
  $sth = $dbi_interface -> insert ('bar', drink => 'Caipirinha');

Inserts the given column/value pairs into table. Determines from the SQL data type which values has to been quoted. Just pass a reference to the value to protect values with SQL functions from quoting.

update table conditions column value [column value] ...
  $dbi_interface -> update ('components', "table='ram'", price => 100);

Updates any row of table which fulfill the conditions by inserting the given column/value pairs. .

rows table [conditions]
  $components = $dbi_interface -> rows ('components');
  $components_needed = $dbi_interface -> rows ('components', 'stock = 0');

Returns the number of rows within table satisfying conditions if any.

serial table sequence

Returns a serial number for table by querying the next value from sequence. Depending on the DBMS one of the parameters is ignored. This is sequence for mSQL resp. table for PostgreSQL. mysql doesn't support sequences, but the AUTO_INCREMENT keyword for fields. In this case this method returns 0 and mysql generates a serial number for this field.

fill sth hashref [flag column ...]

Fetches the next table row from the result stored into sth and records the value of each field in hashref. If flag is set, only the fields specified by the column arguments are considered, otherwise the fields specified by the column arguments are omitted.

view table [name value ...]
  foreach my $table (sort $dbi_interface -> tables)
    print $cgi -> h2 ('Contents of ', $cgi -> code ($table));
    print $dbi_interface -> view ($table);

Produces HTML code for a table displaying the contents of the database table table. This method accepts the following options as name/value pairs:

order: Which column to sort the row after. column_link: URI for the column names. A %s will be replaced by the column name.

  print $dbi_interface -> view ($table,
                                order => $cgi -> param ('order') || '',
                                column_link => $cgi->url()
                                . "&order=%s");


  $dbi_interface -> insert ('transaction',
                   id => serial ('transaction', 'transactionid'),
                   time => \$dbi_interface -> now);

Returns representation for the current time. Uses special values of the DBMS if possible.


money2num money

Converts the monetary value money to a numeric one.


Stefan Hornburg,