Re: Integration of SQL Search Results Into An Existing HTML Template

in reply to Integration of SQL Search Results Into An Existing HTML Template

I am not sufficiently familiar with the part about modifying contents on the webpage; sounds like Perl generating JavaScript to me.

Now, the DBI stuff, I can give you a working example:

Using SQLite to demonstrate:

C:\Steve\Dev\PerlMonks\P-2013-09-21@0144-DBI-Select-Example>"C:\App\SQ
+Lite\sqlite3.exe" test.db
SQLite version 3.3.5
Enter ".help" for instructions
sqlite> CREATE TABLE FOO ( SEQNUM integer primary key, BAR character(3
+0) );
sqlite> insert into FOO values (1, 'George');
sqlite> insert into FOO values (2, 'Mike');
sqlite> .mode col
sqlite> .header on
sqlite> select * from FOO;
SEQNUM      BAR
----------  ----------
1           George
2           Mike
sqlite> .exit
[download]

And here's how you read the data into an array of hashes, which won't display on the output unless you make it happen:

#!/usr/bin/perl -w

use strict;

use DBI;

my    $TRUE    = 1;
my    $FALSE    = 0;

my    $Wrnflg    = $TRUE;                    # Do we let warnings thro
+ugh?

# Establish WARN handler
BEGIN { $SIG{'__WARN__'} = sub { warn $_[0] if $Wrnflg } }

{
     # Set up SQLite DB filename and the SQL statement to execute
     my $Db_dbn = 'test.db';
     my $sqlsmt = 'SELECT * FROM FOO;';

     # A place to put the results
     my $retval = '';

     # Set up presumed statistics
     my $dbargs = {AutoCommit => 0,
                      PrintError => 1};

     # Build connection string
     my $dbscns = "dbi:SQLite:dbname=$Db_dbn";

     # Open the connection to the database server
     my $dbshan = DBI->connect($dbscns,"","",$dbargs);

     if ($dbshan->err())
     {
          $retval = "$DBI::errstr";
          # Do something with this error message.
     }

     # Prepare the SQL statement
     my $sqlhan = $dbshan->prepare($sqlsmt);

     if ($dbshan->err())
     {
          $retval = "$DBI::errstr";
          &debug::debug("\$retval = '\$retval'\n");
     }

     my $qryres = $sqlhan->execute;

     if ($dbshan->err())
     {
          $retval = "$DBI::errstr";
          # Do something with this error message
     }

     # Fetch data
     my @dbrref = @{ $sqlhan->fetchall_arrayref({}) };

     # Commit statement (not really an issue with SELECT but good as a
+n example
     $dbshan->commit();
     if ($dbshan->err())
     {
          $retval = "$DBI::errstr";
          # Do something with this error message
     }

     # Disconnect
     $dbshan->disconnect();
     if ($dbshan->err())
     {
          $retval = "$DBI::errstr";
          # Do something with this error message
     }

     # Display
     foreach my $dbrref (@dbrref)
     {
         print "ROW:";
         my %dbrrec = %$dbrref;
         foreach my $colnam (keys %dbrrec)
         {
             my $colval = $dbrrec{$colnam};
             print "  $colnam = '$colval'";
         }
         print "\n";
     }
}

exit;

__END__
[download]

Results:

C:\Steve\Dev\PerlMonks\P-2013-09-21@0144-DBI-Select-Example>perl dbi-s
+elect-example.pl
ROW:  SEQNUM = '1'  BAR = 'George'
ROW:  SEQNUM = '2'  BAR = 'Mike'
[download]

So all you have to do is not do the display loop

Cheers!

In Section Seekers of Perl Wisdom