Page Index Toggle Pages: 1 Send TopicPrint
Normal Topic Perl SQL DBI snippets (Read 712 times)
Dandello
Forum Administrator
*****
Offline


I love YaBB 2.7!

Posts: 1759
Location: The Land of YaBB
Joined: Feb 12th, 2014
Gender: Female
Perl SQL DBI snippets
Mar 7th, 2016 at 3:18pm
Print Post  
This is a space to post Perl/DBI code for storage/sharing.

I'm working on converting some other of my sites to using mySQL for data storage instead of flat files so I'll be sharing bits of code that I've gotten to work.

1st: the lib/WebDB.pm file. (In YaBB it would go into either Sources or Modules.)

Code (Perl)
Select All
package WebDB;

use strict;
use DBI;

my $host_name = 'localhost';
my $db_name = 'mysite';
my $userid = 'root';
my $password = 'password';
my $dsn = "DBI:mysql:host=$host_name;database=$db_name";

sub connect {
    return (DBI->connect($dsn, $userid, $password,
    {PrintError => 0, RaiseError => 1}));
}

1; 



Notes on 'tables': A DB table is what PerlMongers call a hash of hashes - the values in the field assigned to be the 'index' is the 'name' of the hash while the table field names are the hash keys for all the hashes in the table.


  

Perfection is not possible. Excellence, however, is excellent.
Back to top
WWW  
IP Logged
 
Dandello
Forum Administrator
*****
Offline


I love YaBB 2.7!

Posts: 1759
Location: The Land of YaBB
Joined: Feb 12th, 2014
Gender: Female
Re: Perl SQL DBI snippets
Reply #1 - Mar 7th, 2016 at 3:36pm
Print Post  
Get an item count using selectors:
Code (Perl)
Select All
my $sql = q~
SELECT COUNT(*)
FROM table
WHERE item1 = ? AND item2 = ?
~;
my $dbh = WebDB::connect();
my $sth = $dbh->prepare($sql);
$sth->execute($item1, $item2);
($list) = $sth->fetchrow;
$sth->finish();
$dbh->disconnect();
$numline = qq~<p>There are currently $list items in the 'table' table.</p>~;
 



Some notes on how Perl DBI varies from its PHP equivalents: DBI uses placeholders ('?') to mark data values that will be used.
Code (Perl)
Select All
WHERE  item1 = ? AND item2 = ? 


The number of data values match up with
Code (Perl)
Select All
$sth->execute($item1, $item2); 



In this example $numline is what will be printed to show the number of row the query returned.

  

Perfection is not possible. Excellence, however, is excellent.
Back to top
WWW  
IP Logged
 
Dandello
Forum Administrator
*****
Offline


I love YaBB 2.7!

Posts: 1759
Location: The Land of YaBB
Joined: Feb 12th, 2014
Gender: Female
Re: Perl SQL DBI snippets
Reply #2 - Mar 7th, 2016 at 4:30pm
Print Post  
Add an item to a table from a tab delimited file with the table fields in the 1st row of the file:
Code (Perl)
Select All
## get data ##
open my $DAT, '<', $getfile or croak "cannot open $getfile";
my @data = <$DAT>;
close $DAT or croak 'cannot close data';
chomp @data;

## add data to mySQL table 'table' ##
my $sql = q~
INSERT INTO table ( item1, item2)
VALUES( ?, ? )
~;
my $dbh = WebDB::connect();
    for my $i ( 1 .. $#data ) {
        my ( $item1, $item2 ) = split /\t/xsm, $data[$i];
        my $sth = $dbh->prepare($sql);
        $sth->execute( $item1, $item2 );
    }
$sth->finish();
$dbh->disconnect(); 



To update existing items in the table: (same data file as above)
Code (Perl)
Select All
my $sql = q~
UPDATE table SET item1 = ?, item2 = ?
WHERE item1 = ?
~;
my $dbh = WebDB::connect();

for my $i ( 1 .. $#data ) {
    my ( $item1, $item2 ) = split /\t/xsm, $data[$i];
    my $sth = $dbh->prepare($sql);
    $sth->execute( $item1,  $item2, $item1 );
}
$sth->finish();
$dbh->disconnect();
 



To simply open that table as an array (with selectors and sorted by a key):
Code (Perl)
Select All
my @datab;
my $sql = q~
SELECT table
FROM table
WHERE item1 = ? AND item2 = ?
ORDER by item1
~;
my $dbh = WebDB::connect();
my $sth = $dbh->prepare($sql);
$sth->execute( $item1,  $item2 );
while ( my @val = $sth->fetchrow_array() ) {
    push @datab, [@val];
}

$sth->finish();
$dbh->disconnect();
 


In this example, you can access the data in the array of arrays (@datab) by using:
Code (Perl)
Select All
for my $i (0 .. $#data) {
    my ( $newitem1, $newitem2 ) = @{$datab[$i]};
##Do something with each 'row' in @datab ##
} 

  

Perfection is not possible. Excellence, however, is excellent.
Back to top
WWW  
IP Logged
 
Page Index Toggle Pages: 1
Send TopicPrint