Chapter 1.4: Database Handlings

 

NAME

Genelet::DBI – Generic Relational Database Class

 

SYNOPSIS
use DBI;
use Genelet::DBI;

my $dbh = DBI->connect(...);
my $dbi = Genelet::DBI->new(dbh=>$dbh);

my $error = $dbi->do_sql(SQL, @bind_values);

my $lists = [];
my $error = $dbi->select_sql($lists, SQL, @bind_values);

my $lists = [];
my $hash = {};
my $outs = [column, column, ...];
my $error = $dbi->select_do_proc($lists, $hash, $outs, PROCEDURE, @bind_values);

 

DESCRIPTION

This class handles database operations using raw SQL statements or stored procedures.

 

CONSTRUCTOR
my $dbi = Genelet::DBI->new(dbh=>$dbh);

where $dbh is a database handler object from DBI->connect(…).

 

MAIN METHODS

 

do_sql
my $error = $dbi->do_sql(SQL, @bind_values);

This is for INSERT, UPDATE and DELETE. Pass binding variables as arguments. It catches error as the return.

select_sql
my $lists = [];
my $error = $dbi->select_sql($lists, SQL, @bind_values);

This is for SELECT. The searched results are saved in $lists as an array reference, in which each row is a hash reference using column names as keys. Catch error as the return.

select_do_proc
my $lists = [];
my $hash = {};
my $outs = [column, column, ...];
my $error = $dbi->select_do_proc($lists, $hash, $outs, PROCEDURE, @bind_values);

A Stored Procedure could output values and produce multiple searches a the same time. After a successful run, the output of variables $outs are saved in $hash, and the searched results in $lists, as an array reference. Each item in $lists is itself an array reference, storing one SELECT data set as in select_sql().

 

OTHER METHODS

 

do_sqls
my $error = $dbi->do_sqls(SQL, \@bind_variables, \@bind_variables, ...);

If you insert multiple records at once, you may use do_sqls (with s) instead of running multiple do_sql. Each argument is one row containing the binding variables as an array reference.

select_sql_label
my $lists = [];
my $labels = [label, label, ...];
 my $error = $dbi->select_sql($lists, SQL, $labels, @bind_values);

It is similar to select_sql but uses given labels as hash keys.

do_proc
my $error = $dbi->do_proc(PROCEDURE, @bind_values);

or

my $outs = [column, column, ...];
my $hash = {};
my $error = $dbi->do_proc($hash, $outs, PROCEDURE, @bind_values);

Runs a Stored Procedure without search. The first form outputs nothing. The second form outputs the values of variables $out in $hash.

select_proc
my $lists = [];
my $error = $dbi->select_proc($lists, PROCEDURE, @bind_values);

Runs a Stored Procedure without output variable. It saves the searched results in $lists, which, like select_do_proc, is an array reference of array references of hashes.