Retrieve Data from MYSQL DB using PERL

Retrieve Data from MYSQL DB Examples:(Note: how many ways to retrieve the data from db using perl?)


1. @row = $conn->fetchrow_array;

2. $row = $conn->fetchrow_arrayref;

3. $row = $conn->fetchrow_hashref;


fetchrow_array:

dbopen.pl

#!/usr/bin/perl
require "dbconfig.pl";
$dbconnect = DBI->connect($dbase,$user,$pass) or die "connection Error:$DBI::errstr\n";

dbconfig.pl

#!/usr/bin/perl
$dbase = 'dbi:mysql:siva';
$user = 'root';
$pass = 'siva123';

dbclose.pl

#!/usr/bin/perl
$dbconnect->disconnect();


Test.pl

#!/usr/bin/perl
use DBI;
require "dbopen.pl";
$sql = "select id,emp_fname,emp_lname,emp_id from stech_emp limit 30,10";
$conn =$dbconnect->prepare($sql);
$conn->execute or die "SQL Error:$DBI::errstr\n";
while (@row = $conn->fetchrow_array) {

print "$row[0]\t$row[1]\t$row[3]\n";

}
$conn->finish();
require "dbclose.pl";

OUTPUT:

root@itadmin-desktop:/var/www/cgi# perl Test.pl
44 Loganayagi 1010
45 Amala 1014
46 Vidhya 1009
47 Nithya 4006
48 Vanitha 1020
49 Saraswathi 1021
50 Maheswari 4009
51 Kalpana 4010
52 Renuga 4017
53 Nisha 4015

fetchrow_arrayref :

dbopen.pl

#!/usr/bin/perl
require "dbconfig.pl";
$dbconnect = DBI->connect($dbase,$user,$pass) or die "connection Error:$DBI::errstr\n";

dbconfig.pl

#!/usr/bin/perl
$dbase = 'dbi:mysql:siva';
$user = 'root';
$pass = 'siva123';

dbclose.pl

#!/usr/bin/perl
$dbconnect->disconnect();


Test1.pl

#!/usr/bin/perl
use DBI;
require "dbopen.pl";
$sql = "select id,emp_fname,emp_lname,emp_id from stech_emp limit 30,10";
$conn =$dbconnect->prepare($sql);
$conn->execute or die "SQL Error:$DBI::errstr\n";
while (@row = $conn->fetchrow_arrayref) {

print "$row->[0]\t$row->[1]\t$row->[3]\n";

}
$conn->finish();
require "dbclose.pl";

OUTPUT:

root@itadmin-desktop:/var/www/cgi# perl Test1.pl
44 Loganayagi 1010
45 Amala 1014
46 Vidhya 1009
47 Nithya 4006
48 Vanitha 1020
49 Saraswathi 1021
50 Maheswari 4009
51 Kalpana 4010
52 Renuga 4017
53 Nisha 4015

fetchrow_hashref :

dbopen.pl

#!/usr/bin/perl
require "dbconfig.pl";
$dbconnect = DBI->connect($dbase,$user,$pass) or die "connection Error:$DBI::errstr\n";

dbconfig.pl

#!/usr/bin/perl
$dbase = 'dbi:mysql:siva';
$user = 'root';
$pass = 'siva123';

dbclose.pl

#!/usr/bin/perl
$dbconnect->disconnect();


Test2.pl

#!/usr/bin/perl
use DBI;
require "dbopen.pl";
$sql = "select id,emp_fname,emp_id from stech_emp limit 30,10";
$conn =$dbconnect->prepare($sql);
$conn->execute or die "SQL Error:$DBI::errstr\n";
while ($row = $conn->fetchrow_hashref) {

print "$row->{id}\t$row->{emp_fname}\t$row->{emp_id}\n";

}
$conn->finish();
require "dbclose.pl";

OUTPUT:

root@itadmin-desktop:/var/www/cgi# perl Test2.pl
44 Loganayagi 1010
45 Amala 1014
46 Vidhya 1009
47 Nithya 4006
48 Vanitha 1020
49 Saraswathi 1021
50 Maheswari 4009
51 Kalpana 4010
52 Renuga 4017
53 Nisha 4015