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.

Collapse Copy Code

#!/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
$DBI::errstr";
}

Example two

Inserting a record.

Collapse Copy Code

#!/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
$DBI::errstr";
}

Example three

Updating a record.

Collapse Copy Code

#!/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
$DBI::errstr";
}

Example four

Deleting a record.

Collapse Copy Code

#!/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
$DBI::errstr";
}

Example five

Viewing all records.

Collapse Copy Code

#!/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}
$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!

License

About the Author

lexxwern Member

About :: a fulltime college student from new delhi, india. Latest :: college 1st semester. Occupation: Web Developer Location: India