Pages

Monday, December 8, 2003

Accessing SQL Databases in HTML::Mason

Introduction

In the past 3 articles, we discussed about the different components of HTML::Mason. Most web sites require database access so when you create your HTML::Mason pages, you should also consider this.

Data base access in HTML::Mason

Accessing database in HTML::Mason is similar to accessing it in an ordinary Perl script. Let's say you have a database called article and you want to list all the rows in the article table. Assuming that your database is MySQL, you would do this in an ordinary Perl script:

use DBI;
my $db = DBI->connect('DBI:mysql:database=article',    'userid', 'password');
my $sql = qq { SELECT DATEPUBLISHED, TITLE FROM ARTICLE };
$sth = $db->prepare($sql);
$sth->execute();

In HTML::Mason, you also need to connect to your database and issue SQL statements. If you have only one page that will require database access, you can hard code the connection statement. However, if you have 10 pages that require database access, you can still hard code the connection statement. But if you need to change the userid or password, you will need to change them in all these pages.

<%perl>
use DBI;
return DBI->connect('DBI;mysql;database=article','userid', 'password');

Then you can call this component from all webpages that need database access:

<%init>
use DBI;
my $db = $m->comp('connect.html');
my $sql = qq { SELECT DATEPUBLISHED, TITLE FROM ARTICLE };
my $sth = $db->prepare($sql);$sth->execute();

If you did it this way, any changes to the database connection statement will not require that you change all 100 scripts. Just go in and change the connect.html statement and you are all set!

Displaying All Rows

Now that you have selected a set of rows, you need to display them. To do this, you can display them in a table like this:

<html>
<head>
</head>
<body>
<table border="1">
<tr>
    <th>Date</th><th>Title</th>
    </tr>
% while (my $rec = $sth-&gt;fetchrow_hashref() ) {
<tr>
        <td>&lt;% $rec-&gt;{DATEPUBLISHED} %&gt;</td>
        <td><a href="https://www.blogger.com/view.html?date=%3C%" rec-="">{DATEPUBLISHED} %&gt; &gt;
            &lt;% $rec-&gt;{TITLE} %&gt;</a>
        </td>
    </tr>
% }
</table>
</body>
</html>

The result will look something like this:

 DateTime 
 2001-04-24 CGI Programming - Part 4
 2001-05-24 Using DBI to Access SQL Databases
 2001-07-24Perl Packages 
 2001-08-24Perl Packages part 2 (Variable Scoping) 

A simpler way is to separate the connection script and call it, say, connect.html

No comments:

Post a Comment