Accessing databases via external Perl scripts

Last edited on

Overview

This is an example of accessing a database thru external Perl scripts which perfom the actual database accesses.

The example shows how to write log statements to a database and how to read them out of the database again.

Create a new database table

First of all, a database is required to write the log entries to. The following script will create such a database:

use DBI;
#https://search.cpan.org/~adamk/DBD-SQLite-1.35/lib/DBD/SQLite.pm
my $db = shift;
print "using $db\n";
my $connection = DBI->connect("dbi:SQLite:dbname=$db","","",
{RaiseError => 1} )
    or die $DBI::errstr;
my $table = 'TestResult';
my @rows = qw(time file text);
$connection->do("create table $table (" . join(',',@rows) . ')');
$connection->disconnect;
createlogtable.pl

Usage example - Unix:

perl createlogtable.pl database_name
In a shell

(database_name must be replaced by the actual database name.)

Usage example - Windows:

C:\perl\bin\perl.exe createlogtable.pl database_name
In cmd.exe

(database_name must be replaced by the actual database name.)

An external script to execute queries

require Encode;

use DBI;

my $db = shift;
my $query = shift;
my $connection = DBI->connect("dbi:SQLite:dbname=$db","","", {RaiseError => 1} )
or die $DBI::errstr;
my $records = $connection->selectall_arrayref($query);
foreach my $record (@$records) {
    my @row = @$record;
    foreach $column (0..$#row) {
        print("\t") if $column > 0;
        my $field = $record->[$column];
        Encode::_utf8_on($field);
        print("'$field'");
    }
    print("\n");
}
$connection->disconnect;
query.pl

Log to database and list the records from Squish test script

sub dbLog {
    my $testfile = shift;
    my $msg = shift;
    my $time = localtime; 
    $msg =~ s/\n/\\n/g;
    $msg =~ s/"/"""/g;
    my $query = "INSERT INTO TestResult VALUES('$time','$testfile','$msg')";
    open(IN, "-|", "c:/perl/bin/perl c:/query.pl 
    c:/testresult.db \"$query\""); 
    while(<IN>) {
        test::log( "debug: $_" );
    } 
}

sub listRecords {
    my $query = "SELECT * FROM TestResult";
    open(IN, "-|", "c:/perl/bin/perl c:/query.pl c:/testresult.db \"$query\""); 
    while(<IN>) {
        my ($time,$file,$msg) = split /\t/;
        test::log( "At $time test $file said $msg");
    }
}

sub main {
    dbLog(squishinfo->testCase, "start addressbook");
    startApplication("addressbook_swt.bat");
    # ...
    dbLog(squishinfo->testCase, "end addressbook");
    listRecords
}
test.pl