Click here to Skip to main content
15,867,780 members
Articles / Programming Languages / Perl

Performing SQL operations through Perl

Rate me:
Please Sign up or sign in to vote.
4.00/5 (3 votes)
31 Aug 2002 157.3K   10   7
This article explains how to perform operations on your database through Perl using the DBI module.

Introduction

This article explains 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.

PERL
#!/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.

PERL
#!/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.

PERL
#!/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.

PERL
#!/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.

PERL
#!/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.


Written By
Web Developer
India India
About
:: a fulltime college student from new delhi, india.

Latest
:: college 1st semester.

Comments and Discussions

 
GeneralConnection to Oracle DB Pin
Anonymous10-Mar-04 19:55
Anonymous10-Mar-04 19:55 
GeneralJust curious Pin
Matthew Merritt13-Dec-03 19:10
Matthew Merritt13-Dec-03 19:10 
GeneralA couple of nitpicks! Pin
Anonymous10-Dec-02 2:20
Anonymous10-Dec-02 2:20 
GeneralRe: A couple of nitpicks! Pin
Anonymous9-Mar-03 5:59
Anonymous9-Mar-03 5:59 
GeneralRe: A couple of nitpicks! Pin
Anonymous19-Mar-03 15:32
Anonymous19-Mar-03 15:32 
WTF | :WTF: Hard escapes in any SQL statement are bad...

It's more correct to use the DBI packages methods to handle special characters which are contained in any data interpolated into a sql statement rather than manual escaping.

This is direct from O'Rielly's Perl In a Nutshell, Second Edition ( chapter 12: Databases and Perl, page 420)



quote

$sql = $db_handle->quote(string)

Escapes special characters in a string for use in an SQL statement.

string is the string to convert.



You should never EVER hardcode into a string and always use the modules methods for handling this sort of thing. It may sound anal, but it's best practices. Wink | ;)
GeneralRe: A couple of nitpicks! Pin
FooOfTheBar4-Aug-03 1:15
FooOfTheBar4-Aug-03 1:15 
GeneralRe: A couple of nitpicks! Pin
Anonymous28-Aug-03 7:28
Anonymous28-Aug-03 7:28 

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

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