Performing SQL operations through Perl

Introduction

This article explains on how to perform operations on your database through Perl, using the DBI module. This assumes that you have basic knowledge about Perl/CGI and SQL. We will be making a simple table and performing basic SQL operations on it.

Comments

Like all Perl code, this code too is self explanatory. If you need detailed information, don't hesitate to use the article forums.

Example one

Creating a table.

#!/usr/local/bin/perl
use DBI;

$username = '';$password = '';$database = '';$hostname = '';
$dbh = DBI->connect("dbi:mysql:database=$database;" . 
   "host=$hostname;port=3306", $username, $password);

$SQL= "create table user(ID integer primary key " . 
  "auto_increment, username text not null," . 
  " password text not null, email text not null)";

$CreateTable = $dbh->do($SQL);

print "Content-type:text/html\n\n\n";
if($CreateTable){
print "Success";
}
else{
print "Failure<br/>$DBI::errstr";
}

Example two

Inserting a record.

#!/usr/local/bin/perl
use DBI;

$username = '';$password = '';$database = '';$hostname = '';
$dbh = DBI->connect("dbi:mysql:database=$database;" . 
  "host=$hostname;port=3306", $username, $password);

$SQL= "insert into user (username, password, email)" .
  " values('lexxwern', 'password', 'email@host')";

$InsertRecord = $dbh->do($SQL);

print "Content-type:text/html\n\n\n";
if($InsertRecord){
print "Success";
}
else{
print "Failure<br/>$DBI::errstr";
}

Example three

Updating a record.

#!/usr/local/bin/perl
use DBI;

$username = '';$password = '';$database = '';$hostname = '';
$dbh = DBI->connect("dbi:mysql:database=$database;" .
  "host=$hostname;port=3306", $username, $password);

$SQL= "update user set email = ".
  "'lexxwern@yahoo.com' where username = 'lexxwern'";

$UpdateRecord = $dbh->do($SQL);

print "Content-type:text/html\n\n\n";
if($UpdateRecord){
print "Success";
}
else{
print "Failure<br/>$DBI::errstr";
}

Example four

Deleting a record.

#!/usr/local/bin/perl
use DBI;

$username = '';$password = '';$database = '';$hostname = '';
$dbh = DBI->connect("dbi:mysql:database=$database;" .
  "host=$hostname;port=3306", $username, $password);

$SQL= "delete from user where ID=1";

$DeleteRecord = $dbh->do($SQL);

print "Content-type:text/html\n\n\n";
if($DeleteRecord){
print "Success";
}
else{
print "Failure<br/>$DBI::errstr";
}

Example five

Viewing all records.

#!/usr/local/bin/perl
print "Content-type:text/html\n\n";

use DBI;

$username = '';$password = '';$database = '';$hostname = '';
$dbh = DBI->connect("dbi:mysql:database=$database;" .
 "host=$hostname;port=3306", $username, $password);

$SQL= "select * from user";

$Select = $dbh->prepare($SQL);
$Select->execute();

while($Row=$Select->fetchrow_hashref)
{
  print "$Row->{username}<br/>$Row->{email}";
}

Conclusion

Hopefully these examples can give you a neat preview of the capabilities of the DBI module. This site will be of further help. Good luck!