Page Index Toggle Pages: 1 ReplyAdd Poll Send Topic
Normal Topic Perl SQL DBI snippets (Read 4018 times)
 
Paste Member Name in Quick Reply Box Dandello
Forum Administrator
YaBB Modder
*****
Offline


I love YaBB 2.7!

Posts: 2234
Location: The Land of YaBB
Joined: Feb 12th, 2014
Gender: Female
Mood: Annoyed
Zodiac sign: Virgo
Perl SQL DBI snippets
Mar 7th, 2016 at 3:18pm
Mark & QuoteQuote  
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
 
Paste Member Name in Quick Reply Box Dandello
Forum Administrator
YaBB Modder
*****
Offline


I love YaBB 2.7!

Posts: 2234
Location: The Land of YaBB
Joined: Feb 12th, 2014
Gender: Female
Mood: Annoyed
Zodiac sign: Virgo
Re: Perl SQL DBI snippets
Reply #1 - Mar 7th, 2016 at 3:36pm
Mark & QuoteQuote  
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
 
Paste Member Name in Quick Reply Box Dandello
Forum Administrator
YaBB Modder
*****
Offline


I love YaBB 2.7!

Posts: 2234
Location: The Land of YaBB
Joined: Feb 12th, 2014
Gender: Female
Mood: Annoyed
Zodiac sign: Virgo
Re: Perl SQL DBI snippets
Reply #2 - Mar 7th, 2016 at 4:30pm
Mark & QuoteQuote  
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
ReplyAdd Poll Send Topic
Bookmarks: del.icio.us Digg Facebook Google LinkedIn reddit Twitter Yahoo
Perl SQL DBI snippets

Please type the characters exactly as they appear in the image,
without the first 2 and last 2 characters.
The characters must be typed in the same order,
and they are case-sensitive.
Open Preview Preview

You can resize the textbox by dragging the right or bottom border.
Off Topic Comment Insert Spoiler
Insert Hyperlink Insert FTP Link Insert Image Insert E-mail Insert Media Insert Table Insert Table Row Insert Table Column Insert Horizontal Rule Insert Teletype Insert Code Insert Quote Edited Superscript Subscript Insert List /me - my name Insert Marquee Insert Timestamp No Parse
Bold Italicized Underline Insert Strikethrough Highlight
                       
Change Text Color
Insert Preformatted Text Left Align Centered Right Align
resize_wb
resize_hb







Max 5000 characters. Remaining characters:
Text size: %
More Smilies
View All Smilies
Collapse additional features Collapse/Expand additional features Smiley Wink Cheesy Grin Angry Sad Shocked Cool Huh Roll Eyes Tongue Embarrassed Lips Sealed Undecided Kiss Cry