Access your MySQL Database with Perl

One of the most common Perl-related questions at the SitePoint Forums is, "How do I access my database with Perl?" Perl can work with nearly every type of database on the market, but today I’ll use MySQL as an example.

For the purposes of this demonstration, let’s say we have a database named "shoes" with one table named "subscribers". Here’s the layout of the table:

create table subscribers (
uid int not null primary key auto_increment,
username varchar(32) not null,
emailaddr varchar(32) not null
);

We will connect to MySQL with the DBI modules and the DBD::mysql database driver. These modules don’t come with the standard Perl distribution, so you’ll have to obtain them yourself. If you maintain your own server, follow the instructions below, but if you have a Web host, they should be able to take care of the installation for you, if they don’t already have the modules installed.

Install the Modules

To install the DBI and DBD::mysql modules on a Unix-like OS or Perl on Windows installed as part of Cygwin, run the following commands.

Note that you must have MySQL already installed or you will not be able to install the DBD::mysql module. Also, if you haven’t installed any other modules this way, you’ll be asked for configuration options. Luckily, these are self-explanatory. Once you’ve finished entering the configuration values, the module installation will start.

perl –MCPAN –e 'install DBI'
perl –MCPAN –e 'install DBD::mysql'

You’ll see several screens of text fly by, but eventually it should return you to the friendly command prompt.

There will rarely be any errors, because these modules have been tested by thousands of people. However, if you are unlucky enough to encounter errors, a quick search on Google will probably find you the solution.

Work with the Database

Now that the modules and database drivers are installed, we can progress to the actual Perl code to work with the database. For now we’ll just connect to the database.

# set the data source name
# format: dbi:db type:db name:host:port
# mysql's default port is 3306
# if you are running mysql on another host or port,
# you must change it
my $dsn = 'dbi:mysql:shoes:localhost:3306';

# set the user and password
my $user = 'user';
my $pass = 'pass';

# now connect and get a database handle
my $dbh = DBI->connect($dsn, $user, $pass)
or die "Can't connect to the DB: $DBI::errstrn";

OK, there were a lot of new things in that code. First, we defined the DSN, or data source name. This tells the DBI where to find your database server, and what database you will operate on. Then, we set the username and password to connect to the database as. Finally, we call DBI->connect to actually connect to the database (don’t forget to enter your own username and password!), and if the connection was unsuccessful, we print out an error. From that command, we get a database handle, which we will use to run queries on the database.

The execution of a query takes two steps: preparation and execution. First, you must prepare the query like this:

my $sth = $dbh->prepare('insert into subscribers(username, emailaddr)
values "jim", "[email protected]")');

Then you can execute it:

$sth->execute();
Because we ran an INSERT query, there weren’t any results to retrieve. But what if we used SELECT? How would we get the data? Like this:

my $sth = $dbh->prepare("select username, email from subscribers");
$sth->execute;

while(@row = $sth->fetchrow_array()) {
print "$row[0]: $row[1]<br>";
}

As you can see, $sth->fetchrow_array returns an array of the results. If you like, you could write the loop like this to make it a bit more readable:

while(my($username, $email) = $sth->fetchrow_array()) {
print "$username: $email<br>";
}

Instead of assigning the values to a normal array, this assigns the rows’ values each to a different variable.

Build the Web App

Now let’s use the DBI to create a (somewhat) useful Web application to manage catalog subscribers of a fictitious shoe store. There will be two pages, view.pl and add.pl, and the database schema will be the same as the one at the top of this guide. Again, remember that you have to enter your own username and password in these scripts instead of the default "user" and "pass".

Here is add.pl:

use CGI;   use DBI;  

print CGI::header();  

my $username = dbquote(CGI::param('name'));   my $email    = dbquote(CGI::param('email'));  

unless($username) {     print <<PAGE;   <h1>Add a Subscriber</h1>   <form action=add.pl method=post>   Name: <input type=text name=name><br>   Email: <input type=text name=email><br>   <input type=submit value="Add Subscriber">   </form>   PAGE  

exit;   }  

# connect   my $dbh = DBI->connect("dbi:mysql:shoes:localhost:3306", "user", "pass");  

# prepare the query   my $sth = $dbh->prepare("insert into subscribers(username, emailaddr)    values('$username', '$email')");  

# execute the query   $sth->execute();  

print <<PAGE;   <h1>User Added</h1>   The user $username was just added.  Want to    <a href=add.pl>add another</a>? PAGE  

sub dbquote {     my($str) = @_;  

$str =~ s/"/\"/g;     $str =~ s/\/\\/g;     $str =~ s/'/\'/g;  

return $str;   }  And here is view.pl: use DBI;   use CGI;  

print CGI::header();  

print "<h1>Subscribers</h1>";  

# connect   my $dbh = DBI->connect("dbi:mysql:shoes:localhost:3306", "user", "pass");  

# prepare the query   my $sth = $dbh->prepare("select username, emailaddr from subscribers");  

# execute the query   $sth->execute();  

while(my ($username, $email) = $sth->fetchrow_array()) {     print "$username: $email<br>n";   }  That's it!  If you're after more information, try these resources:

Replay

Category: programming Time: 2001-08-30 Views: 3
Tags:

Related post

  • How To Back Up MySQL Databases With mylvmbackup On Debian Squeeze 2012-02-21

    How To Back Up MySQL Databases With mylvmbackup On Debian Squeeze Version 1.0 Author: Falko Timme Follow me on Twitter mylvmbackup is a Perl script for quickly creating MySQL backups. It uses LVM's snapshot feature to do so. To perform a backup, mylv

  • How To Back Up MySQL Databases With mylvmbackup On Ubuntu 12.10 2013-09-14

    How To Back Up MySQL Databases With mylvmbackup On Ubuntu 12.10 Version 1.0 Author: Falko Timme Follow me on Twitter mylvmbackup is a Perl script for quickly creating MySQL backups. It uses LVM's snapshot feature to do so. To perform a backup, mylvmb

  • How To Back Up MySQL Databases With mylvmbackup On Debian Lenny 2015-03-14

    How To Back Up MySQL Databases With mylvmbackup On Debian Lenny Version 1.0 Author: Falko Timme Follow me on Twitter mylvmbackup is a Perl script for quickly creating MySQL backups. It uses LVM's snapshot feature to do so. To perform a backup, mylvmb

  • Access Remote mysql database without allowing remote connections 2009-11-14

    How can I run an app locally to access the mysql database on my server without enabling remote access to the mysql server. I have full SSH access. Is this possible? Reverse SSH? VPN? (but it seems like it would be a bit confusing) --------------Solut

  • Access WordPress MySQL database remotely 2014-10-15

    I want to access a MySql database on a foreign host. I have permission from the owner, but they are clueless with regards to technology (and it seems, so am I). I went through the WordPress codebase and may have found the database credentials in a fi

  • Design interface for WebService to access several MySQL databases 2015-05-06

    I am currently trying to design an interface for a WebService that can access several MySQL databases. There will be 4 operations available, Add(), Read(), Update() and Delete(). The WebService just needs to assemble an SQL statement based on the cal

  • Is it possible to access a MYSQL database table from a sharepoint hosted app? 2015-09-02

    I'm alittle confused, when I google this all i seem to find is how to connect to sharepoint using a cloud hosted app hosted in windows azure, that's not what I'm looking for. I've been creating Java applications that communicate with a MYSQL database

  • How to access the MySQL Database which is kept online through Java Swing Application 2016-01-15

    I have developed an Java Swing Application with MySQL database for one of my client which runs on one computer and at one place and now he has the requirement such that he wants to see the Application remotely from other town. So In order to pursue t

  • Restore mysql database with different name 2011-12-07

    How can we restore mysql database with different name from mysqldump file. I dont't want to open dump file and edit it. Any other better methods? --------------Solutions------------- You can let mysqldump create the dump in such a way that it does no

  • Can I access a mysql database from a Sharepoint 2013 page? 2015-03-30

    I've recently figured out how to add, remove, update list items on a Sharepoint site using javascript. But can I access a mysql database from a Sharepoint 2013 page using javascript? I want to query the mysql database in order to retrieve information

  • How to connect to mysql database with Erlang, Yaws 2016-01-28

    I am new to Erlang and Yaws... I am trying to connect to a mysql database but I am not sure about a few things.. Questions. 1. Do I need additional library to connect to mysql database with Erlang? 2. How Do I list available database drivers with Erl

  • Synchronizing local mysql database with remote one with a script or automatically? 2011-09-05

    Is there a way to sync a MySQL database with a remote one either automatically or with a script? The data is not time sensitive, but I don't want to have to export it out of the local database and then import it into the remote one. I am one Windows

  • Monitoring number of mysql databases with Nagios 2012-06-18

    I would like to monitor the number of MySQL databases with Nagios. I've installed the official Nagios plugins. I'm using 'check_mysql_query': ./check_nrpe -H 192.168.1.10 -c check_mysql_query Nagios agent: command[check_mysql_query]=/usr/lib64/nagios

  • Is it possible to show markers from MySQL database with Javascript? 2012-08-29

    I'm interested if it's possible to show markers (latit, longit) from MySQL database with Javascript libraries like Leaflet? --------------Solutions------------- yes, it's possible. As long you have the coordinate, then you can display either using Op

  • How can I backup/dump MYSQL database with foreign key constraints? 2014-02-19

    I wanted to take the dump of the MySQL database with all the constraints on that database. The dump (.sql file) I am having right now don't show any reference key(foreign key) relationship. I am taking the dump with following command: mysqldump -u ro

  • OpenSuSE 10.2: create MySQL database with "Access denied for user 'root'@'localhost'" 2015-06-12

    I have to install Wordpress on a very old OpenSuSE 10.2 web server. And for this I´m trying to create a mysql database. Unfortunately the mysql server tells me for every attempt "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using pa

  • Can't access MySQL database with created user through dll 2016-01-22

    I have a web service that connects to a MySQL database from a dll, both of them are on the same machine. If I use the root user and password it will connect no problem but if I use a user that I created it comes back with Access denied for user '####

  • Is it possible to have Word 2007 access a MySQL database? 2009-09-18

    What I essentially want is some way to expand my mail merge system. If an account number gets sent to a word document via mail merge, I would like some way for Word to connect to a MySQL database and use that account number to execute additional quer

  • Replicate MySQL database with "non-permanent" write 2010-02-23

    I'm working with a production MySQL database and I would like to: Create a read-only slave copy of the database that gets updates from the master. The master is the only one that can be written onto. Have "non-permanent write" access to the slav

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 1.431 (s). 13 q(s)