PERL : Database

rpm -qa | grep perl #get the perl packeges like perl-DBI-1.32-5 and perl-DBD-MySQL-2.1021-3
perlscript -> perlDBI(database_interface) -> perl DBD (database_driver) -> DATABASE
http://localhost/phpmyadmin # you can see the databases
you can use : select, insert, update, delete


pico db1.pl
#!/usr/bin/perl -w
use warnings;
use strict;
use DBI;
#Step 1 - create connection objection. Object that gants you access to the database
my $dsn = 'DBI:mysql:contacts';#driver-mysql, database-contacts
my $user = 'user';
my $password = 'pass';
my $conn = DBI->connect($dns, $user,$password) || die "Error connecting" . DBI->errstr;
#Step 2 - define query
my $query1 = $conn->prepare('SELECT * FROM addressbook') || dir "Error preparing query" .$conn->errstr; #check if the sqlstatemant is correct
#Step 3 - execute the query
$query1->execute || die "Error Executing query" .$query1->errstr;
#Step 4 - return results from query
my @results;
while (@results = $query1->fetchrow_array())
{
my $firstname = $results[0];
print $firstname\n;
foreach(@results)
{
print $_\n;#prints all the values
}
}
if($query1->rows == 0)
{
print "No Records \n";
}
#end


cp di1.pl db2.pl
#!/usr/bin/perl -w
use warnings;
use strict;
use DBI;
#Step 1 - create connection objection. Object that gants you access to the database
my $dsn = 'DBI:mysql:contacts';#driver-mysql, database-contacts
my $user = 'user';
my $password = 'pass';
my $conn = DBI->connect($dns, $user,$password) || die "Error connecting" . DBI->errstr;
my $firstname = "Mark";
my $lastname = "Sloan";


#Step 2 - INSERT/UPDATE/DELETE queries are shortend to 2 steps
$conn->do("INSERT INTO addressbook(firstname,lastname) VALUES ('$firstname','$lastname')") || dir "Error preparing query" .$conn->errstr; 
#end


nano dbinsert.txt
Mark Sloan 101010
George Bush 232323
Hilary Clinton 32121
Tony Macarony 43198


#!/usr/bin/perl -w
use warnings;
use strict;
use DBI;
#Step 1 - create connection objection. Object that gants you access to the database
my $dsn = 'DBI:mysql:contacts';#driver-mysql, database-contacts
my $user = 'user';
my $password = 'pass';
my $conn = DBI->connect($dns, $user,$password) || die "Error connecting" . DBI->errstr;
open (han1, "dbinsert.txt") || die "Error : $!";
my @newrecords = <han1>;
foreach(@newrecords)
{
my @columns = split;
$firstname = $columns [0]; 
$lastname = $columns [1]; 
$zip = $columns [2]; 
$conn->do("INSERT INTO addressbook(firstname,lastname,zip) VALUES ('$firstname','$lastname','$zip')") || dir "Error preparing query" .$conn->errstr; 
}
#end


nano db3.pl
#!/usr/bin/perl -w
use warnings;
use strict;
use DBI;
#Step 1 - create connection objection. Object that gants you access to the database
my $dsn = 'DBI:mysql:contacts';#driver-mysql, database-contacts
my $dsn2 = 'DBI:mysql:contacts2:192.168.1.10';
my $user = 'user';
my $password = 'pass';
my $conn = DBI->connect($dns, $user,$password) || die "Error connecting" . DBI->errstr;
$firstname = $Marko; 
$lastname = $columns [1]; 
$zip = $columns [2]; 
$conn->do("UPDATE addressbook SET firstname='$firstname' WHERE firstname='Mark'") || dir "Error preparing query" .$conn->errstr; 
$conn->do("DELETE addressbook WHERE firstname='George'") || dir "Error preparing query" .$conn->errstr; 
#end