SAL::DBI - Database abstraction for SAL
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... }
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}
$SAL::DBI->{connection}->{dbh} contains the DBI database handle. $SAL::DBI->{connection}->{sth} contains the DBI statement handle.
$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.
$SAL::DBI->{data}->[$y][$x] is used to access a returned dataset as if it were a two-dimensional array. (Yes, I'm lazy. ;-)
Builds a basic factory object. Used for spawning database objects.
Builds a MySQL-specific database object.
Builds an ODBC-specific database object.
Builds a SQLite-specific database object.
Note that temporary databases can be created by passing the string ':memory:' in place of a filename.
Executes a SQL command that does not return a dataset. Check $rv (result value) for errors.
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 ;)
Return a dataset column as a list.
Return a dataset record as a list.
Get the object's dataset as a CSV file
Get a list containing the dataset's field names.
Strip times from a datetime column.
Convert a datetime column to use short dates. (Note, use clean_times() first)
Scott Elcomb <psema4@gmail.com>
SAL, SAL::WebDDR, SAL::Graph, SAL::WebApplication
To install SAL, copy and paste the appropriate command in to your terminal.
cpanm
cpanm SAL
CPAN shell
perl -MCPAN -e shell install SAL
For more information on module installation, please visit the detailed CPAN module installation guide.