DATABASE OPERATIONS

http://in.geocities.com/rsramsam/perldemo.htm

DATABASE OPERATIONS

USING

PERL & PHP-TRIAD

( published in DeveloperIQ.. )

www.developeriq.com

R.S.RAMASWAMY

(rsramsam@yahoo.co.in)

part-1

-------

cgi-perl & ODBC db operations

Locating the Database in a webserver and accessing it from browser, is one of the main purposes of Web-based applications.These are known as Web-Server Technologies. There are seven Web-Server Technologies as listed below.

1) ASP

2) Servlets

3) JSP

4) PERL

5) PHP

6) ColdFusion

7) ASP.Net

( ASP.NET is much more than mere webserver technology, however).

At the outset, we should note that all the above technologies can be used in Windows platform. ASP is based on VB-script and is mainly Microsoft technology.Despite the great superiority of ASP.net over ASP, it appears that ASP is still going strong as many companies are already locked into it and find the transition, too much work!With a lot of COM objects in ready use, it seems that ASP.NET & VB.net would take some time to gain full acceptance among Corporate Windows users, till COM to DOTNET interop problem is solved completely! Chillisoft is an attempt to use ASP in Unix platform, but not very successful. ASP.net has been till now, Windows Technology but thanks to MONO project, may become available for UNIX and Linux platforms,soon. As a columnist observed,

the fight is now between 'LegacyCOM' windows and 'DotNet' windows, within Microsoft camp!

For servlets and JSP, we can either instal Tomcat over Apache in Windows or use Tomcat directly in windows. ColdFusion Server can be installed over Personal WebServer.(Look for a tutorial on ColdFusion dbdemo here). Finally, by using a software known as PHP-TRIAD, we can learn PHP & Perl in Windows environment. As PERL & PHP are very often clubbed with UNIX/LINUX/APACHE, students may get the impression that we cannot learn PERL/PHP except in Linux environment. That impression is not correct. Apache webserver, MySql database and PHP/PERL can be installed in Windows platform without needing Linux to be installed.

( However, we may get the warning that performance is not guaranteed and result may be unpredictable ,sometimes).

A group of Free Software Evangelists are providing all such software which can be learnt and used in both Linux & Windows platforms. That may be the best approach instead of frightening away the newcomers with all that talk of 'Nightly builds' and 'source -code compilation'. Most students and learners have ready access to Windows machines only and when we introduce all the Free software like Apache,MySql and PERL/PYTHON/PHP through Windows platform, learners gain confidence and can easily switch over to Linux/Unix when required. [By using Cygwin..now owned by RedHat...,we can learn Unix shell programming also in Windows platform.]

PHP-TRIAD is a fine & famous software. It can be easily installed over Windows platform.It is called a 'Triad', because it gives Apache Web Server, MySql database and PHP engine in a single package.It provides support for Perl also,provided we instal Perl5 separately. So, this will be the best thing for students who want to learn PHP ,PERL and MySql.

---

We can instal PHPtriad ( the zip file is about 10MB and has been given in DeveloperIQ cd.). It automatically intalls Apache server , MySql and PHP. We can go to C drive and see that Apache folder is available now.

We find that there are eleven subfolders under Apache folder as follows:

1) bin 2) cgi-bin

3) conf 4) download

5) htdocs 6) icons

7) logs 8) modules

9) mysql 10) php

11) proxy

For the present, the folders shown in bold, are important.

We should instal Perl5 also in our system.

This, we have done in G: DRIVE. ( ActiveState Perl5 , provided in DeveloperIQ cd).

---

(IMPORTANT)

We need to make a very important addition in

Apache httpd.conf file, as provided in PHP-Triad, before we can execute CGI-Perl.

cd to c:\apache\conf folder.

Open c:\apache\conf\httpd.conf file,preferably with 'editplus'( this also has been given in Developeriq cd.

The advantage is that we get line numbers.and it can open a great variety of file-types.)

In case, we do not have editplus, we can open the file with 'edit' program, in DOS window.

In line 642, we will find the following entry:

AddHandler cgi-script .cgi

Change it to read :

AddHandler cgi-script .cgi .pl

(uncomment the line , if it is commented)

save the change and exit.

Now , we are ready to experiment with CGI-PL.

( student readers can find a very fine article on Apache administration ..'Tips for Configuring Apache' by Anand Awasthi in page.38 of DeveloperIQ-Jan-2004. They are also encouraged to get a printout of httpd.conf file and study the details thoroughly. Server administration knowledge is equally important.

DeveloperIQ -Jan-2004 issue was dedicated to Apache server.It would be nice, if we can explain the significance and importance of all those entries in httpd.conf file.)

There have already been a number of tutorials on Perl in DevIQ . In this article, we jump right into Database operations.

We create the following file:

-----------------------------------------------

dbdemo.htm

==============

<html>

<form method=post

action="http://localhost/cgi-bin/dbdemo.pl">

SQL<input type=text size=60 name='text1'> <br>

</form>

</body>

</html>

---------------------------------------------

We place the above file in : c:\apache\htdocs\ourperl folder.

( ourperl folder has been created by us).

--------------------------------------------

When we enter the sql in text1 and submit,

dbdemo.pl in the server is executed.

----------------------------------------------

dbdemo.pl

=========

#!g:\perl\bin\perl.exe

read(STDIN, $s, $ENV {'CONTENT_LENGTH'} );

@pairs=split(/&/, $s);

foreach $pair (@pairs)

{

($name,$value) = split(/=/,$pair);

$value =~ tr/+/ /;

$value =~

s/%([a-fA-F0-9][a-fA-F0-9])

/pack("C",hex($1))/eg;

# type the above in a single line

$FORM{$name}=$value;

}

print"Content-type:text/html\n\n"; # important

print "<html>";

print "<body bgcolor=yellow>";

#----------------------------------------------

print $FORM{"text1"};

print "<br>";

$sql = $FORM{"text1"}; # query

print $sql;

print "<br>";

print"=============";

print "<br>";

use OLE;

# available in g:\perl\site folder.

$con=CreateObject OLE "ADODB.Connection";

$con->Open("dbdemo");

$rs=$con->Execute($sql);

$r="";

while(!$rs->EOF())

{

$s1=$rs->Fields('name')->value;

$s2=$rs->Fields('place')->value;

$r=$r+$s1+"<br>"+$s2+"<br>"+"---"+"<br>";

$rs->MoveNext();

}

$rs->Close();

print r;

print "</body>";

print "</html>";

-----------------------------------------------

We will first execute the program and then have some explanation for the code.

The dbdemo.pl file, as given above should be placed in 'c:\apache\cgi-bin' folder.

From the 'Start' menu, navigate to PHPtriad and 'start apache'. This will start the 'apache' server.

( WE MUST USE 'stop apache' in the same fashion

for clean shutdown).

Start the browser and type the URL as:

'http://localhost/ourperl/dbdemo.htm'.

You will get the form. Type the sql

"select * from table1" and submit.

We get the data. (assuming that we have created a simple Access db named 'dbdemo' , having table1 with two fields 'name' & 'place', with a few entries.Also, registered with ODBC.).

---------------------------

Here is a brief explanation for the code.

The very first line begins with 'shebang' character. It gives path to the Perl.exe available in the system, to which Apache should refer. As we have installed Perl in G: DRIVE,

we have given path accordingly.

As we are using the 'POST' method in HTML form,

the input from the client-side is read as $s,with the syntax as shown.

If the form had text1 and text2 and the user submitted 'sam' in text1 and 'madras' in text2,

it will reach the server as text1=sam&text2=madras.

So the first thing that we do is to separate

the various name-value pairs, by 'split' command, '&' being the tokenizer.

In the next step, we split each name-value pair by the tokenizer '='.

In each value thus obtained, if there had been blank space, it would have been encoded with'+'. For example, 'Thomas Mathew' would have been recieved as 'Thomas+Mathew'. So we translate '+' as ' '.(blank space).

To remove any special encoding characters,

we use a regular expression,which says that lowercase a to f, uppercase A to F,and decimals 0 to 9 ( this is just hexa value) should be suitablly substuituted.

Though all this might be too cryptic and intimidating, luckily, it is just boiler-plate code which can be copied and pasted in each perl file.

----------------------------------------

Is this part of Perl difficult? Not to an ASP coder.We can easily see the great similarity between the above Perl code and ASP code given below.

--------

//c:\inetpub\wwwroot\dbdemo.asp

<html>

<body>

sql =request.form("text1")

set con=Server.CreateObject("adodb.connection")

con.open "dbdemo"

set rs=con.execute(sql)

r=" "

do while not rs.eof

r = r + rs(0)+ "<br>"+rs(1)+"<br>"+"--"+"<br>"

rs.movenext

loop

rs.close

response.write (r)

</body>

</html>

-----------------------------------------------

We are not studying SQL now, but only how to execute sql . So, we created a very simple table with just two fields for demo. Even if a real-life table has to be used with a lot of fields , it does not make any difference atall.

We come acroos the acronym 'CRUD' often in recent programming books. It stands for CREATE,READ,UPDATE & DELETE. These are the usual DB operations. Now, we will see how these could be done.

We begin with a html form, providing there,

as many textboxes as there are fields and an extra textbox for the criterion field in update opearation. Also a combo, for selecting either

'add','modify','delete' or 'verify'.

The html file , is placed in:

c:\apache\htdocs\ourperl folder.

----------------------------------------------

<html>

<form method=post

action="http://localhost/cgi-bin/demo10.pl">

Select Option

<option value="addnew">addnew</option>

<option value="remove">remove</option>

<option value="modify">modify</option>

<option value="verify">verify</option>

</select>

<br>

Name

Place

Criterion

</form>

</body>

</html>

===============================================

The above html file has the action field as :'http://localhost/cgi-bin/dbedit.pl'

The corresponding file is given below.

--------------------------------------

c:\apache\cgi-bin\dbedit.pl

#!g:\perl\bin\perl.exe

read(STDIN, $s, $ENV {'CONTENT_LENGTH'} );

@pairs=split(/&/, $s);

foreach $pair (@pairs)

{

($name,$value)=split(/=/,$pair);

$value =~ tr/+/ /;

# to be typed in continuous line

$value =~ s/%

([a-fA-F0-9][a-fA-F0-9])/pack("C",hex($1))/eg;

$FORM{$name}=$value;

}

print "Content-type:text/html\n\n";

print "<html>";

print "<body bgcolor=yellow>";

#-------------------------------------------------------

print "<br>";

$a = $FORM{"text1"};

print $a;

print "<br>";

$b = $FORM{"text2"};

print $b;

print "<br>";

$c = $FORM{"text3"};

print $c;

print "<br>";

$d = $FORM{"combo1"};

print $d;

print "<br>";

print"=============";

print "<br>";

#------------------------------------------------

use OLE;

$con=CreateObject OLE "ADODB.Connection";

$con->Open("dbdemo");

if($d eq "addnew")

{

$sql =

"insert into table1 values ('$a','$b') ";

print $sql;

print "<br>";

$con->Execute($sql);

print "added";

}

if($d eq "modify")

{

$sql = "update table1

set table1.name='$a',table1.place='$b'

where table1.name='$c' ";

print $sql;

print "<br>";

$con->Execute($sql);

print "modified";

}

if($d eq "remove")

{ $sql = "delete from table1

where name='$a' ";

print $sql;

$con->Execute($sql);

print "<br>";

print "deleted";

}

#---------------------------------------------

if($d eq "verify")

{

$sql="select * from table1

where name='$a'";

print $sql;

print "<br>";

$rs=$con->Execute($sql);

while(!$rs->EOF())<