Click here to Skip to main content
Click here to Skip to main content

Performing SQL operations through Perl

, 31 Aug 2002
Rate this:
Please Sign up or sign in to vote.
This article explains how to perform operations on your database through Perl using the DBI module.

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!

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

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

Comments and Discussions

 
GeneralConnection to Oracle DB PinsussAnonymous10-Mar-04 20:55 
GeneralJust curious PinmemberMatthew Merritt13-Dec-03 20:10 
GeneralA couple of nitpicks! PinsussAnonymous10-Dec-02 3:20 
GeneralRe: A couple of nitpicks! PinsussAnonymous9-Mar-03 6:59 
GeneralRe: A couple of nitpicks! PinsussAnonymous19-Mar-03 16:32 
GeneralRe: A couple of nitpicks! PinmemberFooOfTheBar4-Aug-03 2:15 
GeneralRe: A couple of nitpicks! PinsussAnonymous28-Aug-03 8:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141216.1 | Last Updated 1 Sep 2002
Article Copyright 2002 by lexxwern
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid