Name

SAL::DBI - Database abstraction for SAL

Synopsis

 use SAL::DBI;

 my $dbo_factory = new SAL::DBI;
 my $dbo_sqlite = $dbo_factory->spawn_sqlite($filename);
 my $dbo_mysql = $dbo_factory->spawn_mysql($server, $user, $pass, $database);
 my $dbo_odbc = $dbo_factory->spawn_odbc($dsn, $user, $pass);
 my $dbo_temp = $dbo_factory->spawn_sqlite(':memory:');

 # SQL Queries
 my $rv = $dbo_temp->do(qq|CREATE TABLE SomeTable(some_column varchar(255), some_other_column varchar(255))|);
 my ($w, $h) = $dbo_temp->execute('SELECT * FROM SomeTable WHERE some_column=?', $somevalue);

 # Processing Records
 for (my $i=0; $i<=$h; $i++) {
        # Accessing the data directly...
        my $field_0 = $dbo_temp->{data}->[$i][0];
        my $field_1 = $dbo_temp->{data}->[$i][1];

        # Grab the fields as a list
        my @record = $dbo_temp->get_row($i);
 }

 # Processing entire columns
 for (my $i=0; $i<$w; $i++) {
        my @column = $dbo_temp->get_column($i);
        # do something with the data...
 }

Eponymous Hash

This section describes some useful items in the SAL::DBI eponymous hash. Arrow syntax is used here for readability, but is not strictly required.

Note: Replace $SAL::DBI with the name of your database object... eg. $dbo_temp->{connection}->{dbh}

Connection Information

 $SAL::DBI->{connection}->{dbh} contains the DBI database handle.
 $SAL::DBI->{connection}->{sth} contains the DBI statement handle.

Formatting Control

 $SAL::DBI->{fields}->[$col]{name} contains the name of the field.  (an alias for {fields}{label})
 $SAL::DBI->{fields}->[$col]{label} contains the name of the field. (an alias for {fields}{name})
 $SAL::DBI->{fields}->[$col]{type} contains the datatype for the field
 $SAL::DBI->{fields}->[$col]{visible} contains the visibility status flag for this field
 $SAL::DBI->{fields}->[$col]{writeable} contains a write-access flag.  (Use to indicate field is locked in your apps.)
 $SAL::DBI->{fields}->[$col]{css} contains a CSS string for displaying this field on the web
 $SAL::DBI->{fields}->[$col]{precision} is used to specify the number of digits to the right of a decimail place.
 $SAL::DBI->{fields}->[$col]{commify} is used to force commas in numbers > 999
 $SAL::DBI->{fields}->[$col]{align} is used for aligning the contents of the field (usually for the web).  Default is 'left';
 $SAL::DBI->{fields}->[$col]{prefix} is used to prepend a string to the contents of any data in this column.
 $SAL::DBI->{fields}->[$col]{postfix} is used to append a string to the contents of any data in this column.

The Dataset

 $SAL::DBI->{data}->[$y][$x] is used to access a returned dataset as if it were a two-dimensional array.
 (Yes, I'm lazy. ;-)

Constructors

new()

Builds a basic factory object. Used for spawning database objects.

spawn_mysql($server, $user, $passwd, $database)

Builds a MySQL-specific database object.

spawn_odbc($dsn, $user, $passwd)

Builds an ODBC-specific database object.

spawn_sqlite($dbfile)

Builds a SQLite-specific database object.

Note that temporary databases can be created by passing the string ':memory:' in place of a filename.

Methods

$rv = do($statement)

Executes a SQL command that does not return a dataset. Check $rv (result value) for errors.

($w, $h) = execute($statement, @params)

Executes a SQL command that returns a dataset. The list ($w, $h) contains the size of the SAL::DBI's internal data array. (Useful in for loops ;)

@column = get_column($col)

Return a dataset column as a list.

@record = get_row($row)

Return a dataset record as a list.

$csv = get_csv()

Get the object's dataset as a CSV file

@labels = get_labels()

Get a list containing the dataset's field names.

clean_times($col)

Strip times from a datetime column.

short_dates($col)

Convert a datetime column to use short dates. (Note, use clean_times() first)

Author

Scott Elcomb <psema4@gmail.com>

CPAN RT