| [Home] [Credit Search] [Category Browser] [Staff Roll Call] | The LINUX.COM Article Archive |
| Originally Published: Monday, 19 March 2001 | Author: Marcelo Pham |
| Published to: featured_articles/Featured Articles | Page: 1/1 - [Printable] |
Writing Database Oriented Web-based Applications with Perl - Part III
In the latest segment of his continuing series, Marcelo explains how to interface Perl with an actual database.
|
|
| Page 1 of 1 | |
Interfacing Perl with the database (I):12. Overview of DBI-DBD structure Now that you have already chosen your preferred database engine, let's see how to connect to the database from a Perl script. 12. Overview of DBI-DBD structure:Perl was widely enhanced with database connectivity capability by Tim Bunce. Tim created the DBI interface, which allows programmers to have four totally independent layers when designing applications. These layers protect hackers from picky and tricky coding details when connecting to databases from different vendors, and also helps them achieve code reusability when switching databases. Each layer is independent and has its own functionality: +--------+ +--------+ +------------+ +-----------+| Script +-->+ DBI +---+-->+ DBD Oracle +--->+ Oracle DB | +--------+ +--------+ | +------------+ +-----------+ | | +------------+ +-----------+ +-->| DBD Postgre|--->| Postgre DB| | +------------+ +-----------+ | | +------------+ +-----------+ +-->| DBD mySQL |--->| mySQL DB | +------------+ +-----------+ the Perl script layer: this is the program from which you connect to the database to inquire, update or delete records in your database tables the DBI (DataBase Independent) layer: this is the Perl module that allows you to use objects (called handles) to connect, prepare and execute SQL commands from your Perl script to your database the DBD (DataBase Dependent) layer: this is the Perl module specific for each database vendor. For example, you have DBD for Oracle, DBD for Postgres, etc. Usually the notation is DBD::<dbtype> like DBD::Pg for Postgres the Database layer: the database you chose in the second installment, for example PostgreSQL The Perl script is the front-end program where you will draw the data from the database, and in which you will declare the mid two layers. This script will use the DBI module to create objects to connect, retrieve data and execute database commands or statements, called handles. There are three types of objects, sorry, handles , for DBI: the driver handle, the database handle and the statement handle. The driver handle is the one you usually use to connect to the database, through the connect() method. This handle is a DBI object method. The database handle is the one responsible for actually connecting to
the database, through the driver handle. In other words, the database handle
is a child of the driver handle and allows you to specify the DBD driver,
the host name, username and password. Finally, the statement handle is in charge of the execute database command
and, as you might have already suspected, is a child of the database handle. Let me give you an example so you can get the whole picture. Do not mind the code details, we will go over that in Section 15 , just abstract the object scheme: use DBI;$dbh=DBI->connect("dbi:Pg:dbname=taz:localhost","user","password"); | Drv Hnd +------->| DB Hnd +------>| Stm Hnd | +---------+ +---------+ +---------+ (=DBI->) ($dbh) ($sth) $dbh is the database handle created based on the DBI instance
(the driver handle), which is created with the DBD driver, the database name,
host name, user and password. Then, $sth is the statement handle
instance created as a child of the database handle, which contains the statement
to be executed. The statement handle prepares the query to be executed with
the method prepare(). Finally, the statement handle invokes the
method execute to run the query. Still confused?
Section 15 may make it clearer. One interesting point to mention is that if you have several databases and you installed all the corresponding DBD drivers, you can connect to several databases at the same time from the same script. For example, you can connect to your Oracle database where you store all your local customers, connect to your mySQL database where you store all your web-site customers, and make a valuable comparative report (people from sales and marketing depts. will love you!). Another interesting point to mention is that when you create the database handle, you can specify the database driver, in this example, Postgres. Now, if in your application you encapsulate the routine to connect to the database in such a way that all your scripts will call that same routine to connect to the database, DBI gives you the ability to switch databases, just by changing the mentioned function code to connect to the database. Now, knowing that you can connect to virtually any database, you may ask
"why did I do an exhaustive database analysis and design to carefully
choose a database, if now I can switch databases in a blink of an eye if
I want to?" Technically, yes, you can switch databases seamlessly with
DBI. However, you will have some work to do. You will have to check all your
database related code (for differences in SQL syntax for both databases),
and also the data migration will keep you entertained for a while. Let's say you originally chose mySQL for your project -because you were looking for good performance, and then 6 months later after your application went live, you realized that you're frequently getting data corruption due to connection issues through the Internet. Now you decide the best solution is to use transactions: mySQL does not support transactions. Now you want to migrate to PostgreSQL, because it does have a transaction handler and other features that you want to use. First problem: you must review all the code to find differences in SQL syntax. For example: In mySQL you created text fields using longtext mySQL column type. Postgres does not have that column type, you must use text instead. In mySQL, you created several unsigned integers columns. Postgres does not understand the syntax age int unsigned (it'll give you an error). Second problem: you must migrate your existing data from mySQL to Postgres. This means you should study how the mySQL export process works, and how the Postgres import routines behave. So, back to what I stated in the second part, you should carefully choose the database to try to avoid all these headaches. Yes, DBI is powerful enough to give you the ability to change databases without losing the logic of your current application, but that does not mean that you can avoid or put off the decision of choosing the database. You won't have this problem if you do a good design of your application. DBI architecture is solid and robust, gives you a lot of flexibility, the programming is easy to understand and remember, it is very popular amongst developers, and the error trapping is simple and good at tracing bugs. You'll also have a lot of support from Web sites and user groups. I highly recommend this book: Or, you can go to the egroups web site and join the DBI group. Tim Bunce is an active collaborator in this user group -he helped me a couple of times when I was having problems. 13. Getting and installing DBI:DBI is a Perl module that you install on your Linux box. Of course, it requires Perl to be properly installed. You can get the latest (as of today) version (1.14), which requires Perl 5.004 or higher, from the Comprehensive Perl Archive Network (CPAN). Download the file called DNI-1.14.tar.gz and move it to your /usr/src directory. To install DBI, you must first unpack the compressed file by typing: # cd /usr/src After the compressed file is unpacked, you must unpack the installation file, by typing: # tar xvf DBI-1.14.tar (You can now delete the compressed and installation files if you want, they're not going to be needed anymore). This will create a directory called DBI-1.14 with all the installation files under an /usr/src directory. You should now prepare and install the files as with any other Perl module, by typing: # cd /usr/src/DBI-1.14 When building the test file (make test), you should see lines telling you what the compiler is doing, and the result at the very end of each line. These results must be all OK. When it finishes, the last line will tell you whether the test was successful or not. If it was not, please read the readme.html file that comes with the distribution file, or post a complete log of the building process and the output of perl -v, to the DBI mailing list at dbi-users@isc.org. If everything went fine, you should have the DBI module correctly installed. A file called DBI.pm contains useful information, or you can type perldoc DBI for additional documentation. To use the DBI module from your Perl script, you declare it as any other external Perl module #!/usr/bin/perl at the beginning of your program. After installing DBI, you need to install the DBD driver for your specific database. 14. Getting and installing DBD:You can download the DBD for your specific database vendor from the
CPAN ftp site. After downloading the compressed installation file, again, move it to /usr/src and unpack the compressed file. For example, if you downloaded DBD-Pg-0.95 (PostgreSQL DBD driver) you must type: # cd /usr/src And then unpack the installation file, by typing: # tar xvf DBD-Pg-0.95.tar This will create a directory under /usr/src called DBD-Pg-0.95 . To prepare and install the DBD module, first login as the PostgreSQL (or your specific database) superuser account, and then type: $ cd /usr/src/DBD-Pg-0.95 You cannot do this as root, since the Makefile script checks environment variables (path for database libraries, database files, etc.) and connects to the database when testing the installation. If the regression test was successful, you can do the final installation of DBD by typing: $ make install You now have DBD installed in your Linux box. If you had problems during the installation, you can post a message to the DBI users mailing list at dbi-users@isc.org , or also dbi-users@fugue.com , along with the version of DBI, DBD, database version and output of Perl -v. * Note: DBD drivers require minimum database and DBI versions. For example, the DBD driver for PostgreSQL v0.95 requires at least Perl 5.005, PostgreSQL 6.5 and DBI 1.0. Please refer to the README file included in the installation files. 15. Connecting to the database from Perl :Assuming that everything is fine, we will now proceed with the basic coding to connect to a database. Usually the structure of your script should be something like this: #!/usr/bin/perl use DBI; # Connect to database: # All your SQL statements go here # Disconnect # The rest of your script Let's go over the connect syntax. Basically connect has three parameters: the connection string, the username and the password. The connection string contains three declaration sections: the DBD driver,
the database name and the hostname, but the format may vary for different
database drivers. DBI->connect("dbi:Pg:dbname=taz:localhost","user","password"); (where taz is your database name, and localhost is the name of the host where Postgres is installed) while mySQL would go like: DBI->connect("dbi:mySQL:taz:localhost","user","password"); (mySQL does not use the tag dbname) and Oracle would be like DBI->connect("dbi:Oracle:LISTENER","user","password"); (Oracle knows the database and host name through the Listener, so make
sure the listener for the database you want to connect is running). Usually you can omit the hostname and DBI will connect to the local host,
but if you want to connect to a remote database, you must specify the remote
host name, along with the username and password of the database supersuser
account of the remote database. After doing all the required database operations, you should disconnect the database handle by including this statement in your script: # Disconnect from database: Let's make a simple connection script. Create a text file called testdb.cgi containing the following lines (this example is for PostgreSQL, please change the connection string to your specific database needs based on what you read above): #!/usr/bin/perl $dbh= $dbh->disconnect; Where dbname (in this example taz) is your database
name, and user and password are the username and password
for the database superuser account. # perl testdb.cgi You should see the message Connection successful! If you get an error message, the connection could not be made (no kidding! ). DBI gives good informative message errors so you should not have any problem figuring out what went wrong. But usually, you will get errors if: The database daemon is not running (for Oracle, make sure both Server Manager and Listener are running). The database does not exist in the specified hostname. You can try letting DBI choose the default host by eliminating the hostname, for example connect("dbi:Pg:dbname=taz","user","password"). The database superuser account username and/or password are incorrect (check from the console -- logging in as the database superuser, using that same username and password). The database name is incorrect. (Check the database name case. Remember that almost everything is case-sensitive in Linux.) The DBI or DBD driver was not correctly installed and/or configured (refer to the DBI or DBD readme files for trobleshooting). If you cannot understand what is wrong, post a help message to the DBI users mailing list . And, if you didn't get any error message, good job! * Tip: you can create a Perl library script with the connection string, username and password as functions, and invoke these functions from each script that uses a database connection. For example, a library called "dbconn.lib" could contain lines like these: #!/usr/bin/perl# Database library sample: sub connectionstring { my $dbtype="mySQL"; my $dbname="taz"; my $dbhost="localhost"; my $connstr="dbi:".$dbtype.":".$dbname.":".$dbhost return $connstr; } #End connectionstring sub dbusername { my $username="username"; return $username; } #End dbusername sub dbpassword { my $password="password"; return $password; } #End dbpassword 1; # End of library script And, from your script: #!/usr/bin/perl # Script to inquiry customer status use DBI; require "dbconn.lib"; my $dbh=DBI->connect(&connectionstring;&dbuser;&dbpassword); # (inquiry code) # Disconnect from database: $dbh->disconnect; # (rest of your script) This is an example of how you can encapsulate these variables. If you decide to, for example, change the database superuser password, you just have to modify one script instead of all of them. Always try to create functions that return desired values, never use global variables unless absolutely necessary. Also, always try to connect only when you have to, and disconnect as soon as you finish with all your database duties, as this will improve performance and save server resources for other users. In other words, please do not open a database connection at the very beginning of your script and not disconnect until the very end. 16. Basic code:We'll start this section with an overview of SQL commands. If you are an expert in SQL scripting, I recommend you to skip this paragraph or you may get really bored! For those new to SQL (you can spell it, or you can say 'sequel' ), SQL stands for Structured Query Language and is nothing more and nothing less than a standard language for database queries (SQL is not a product). SQL is a high-level language that allows programmers and database administrators to easily and efficiently update and extract information from different relational database management systems or RDBMS. There are standards for SQL and the most utilized one is ANSI 92 (because it was established in 1992). RDBMS's like PostgreSQL and Oracle use this standard. The five basic SQL commands are: create, insert, select, update and delete. Create is used along with the database element name, table being the most used. So, to create a table, you should type in your database shell: testdb=# create table test (code int, name char(25)); Syntax for column types, referential integrity and other issues will not be discussed here (they would take lots of pages). Please refer to your database documentation, in the Developer section, for further information. You can also create indexes (create index), sequences (create sequence), views, users, and other database elements. Insert is invoked when you want to insert a new record or row to the specified table: testdb=# insert into test values (100, "Taz"); Select (perhaps the most famous command in SQL) is called to inquiry rows from one or many tables or views: testdb=# select code,name from test; This will return: code | name------+------------------------- 100 | Taz 1 row(s) returned Update is called whenever you want to update information from a row or a range of records: testdb=# update test set name="Taz Mania" where code=100; If you inquiry the table again, you will see: testdb=# select code,name from test;code | name ------+------------------------- 100 | Taz Mania 1 row(s) returned You use delete to delete row(s) from a specific table: testdb=# delete from test where code=100; There are of course much more SQL commands but for now with these five you'll have more than enough to play around with. Please refer to any of the database books I recommended in the last installment\\ toget a more detailed knowledge of SQL commands. Ok! Let's go back to Perl. After connecting to the database, you can create a statement handle to prepare and execute a SQL statement. For example: #!/usr/bin/perl# Script to inquiry contact name and phone number use DBI; require "dbconn.lib"; my $dbh=DBI->connect(&connectionstring;&dbuser;&dbpassword); my $sql="select code,last_name,first_name,phone from contact"; $sth=$dbh->prepare($sql); $sth->execute(); When you execute a select statement, the statement handle returns an array with the result of the query, which you can retrieve with the fetchrow_array() method: @rows=$sth->fetchrow_array; The array @rows will contain in each subindex the correspondent column from the select statement. For example, if your statement is select code, first_name from contact, then @rows will have in $rows[0] the value for the first column (code), and $rows[1] will have the returned value for the second column from your select statement, in this case first_name. No result is returned when doing update, insert or delete. For example, if you type: my $sql="update contact set name='Carina L.' where code='CLM'"; $sth will be null since the operation does not return any value. Let's make a simple query script to inquiry the table contact we created throughout these articles, and output the results to an HTML page. Your script would go like: #!/usr/bin/perl use DBI; my $dbh=DBI->connect(&connectionstring;&dbuser;&dbpassword); my $sql="select code,last_name,first_name,phone from contactorder by last_name"; $sth=$dbh->prepare($sql); $sth->execute(); # Disconnect from database $dbh->disconnect; # Mime tag print "Content-type: text/html\n\n"; print "List of my contacts, sorted by last name:\n"; my @rows; my total=0; while (@rows=$sth->fetchrow_array) { print "Contact Name: $rows[0],$rows[1]<br>\n"; print "Contact Phone: $rows[2] <br><br>\n"; total++; } print "Total listed: $total\n"; # End of script If you are only inserting, updating or deleting rows, you can use the method do instead of preparing and executing the command. This will speed up a little bit the process. For example: $cmd="delete from test where code='CLM'"; Let's go over a very basic example of an entry screen. First, I will list the HTML code of the entry page (add.html), and then the Perl script (add.cgi). <html><title>Contact Data Entry (neotaz.com)</title> <br> <form method='post' action='cgi-bin/add.cgi'> Contact Data Entry:<br><br> Enter Contact Code: <input type='text' name='code'><br> Enter First Name: <input type='text' name='first'><br> Enter Last name: <input type='text' name='last'><br> Enter Phone number: <input type='text' name='phone'><br> <br> <input type='submit' value=' Add '> <input type='reset' value='Reset Form'> </form> </html> (Please do no complain about the 'beauty' of the screens. This is just a sample to show you how it works!) Now the Perl script (add.cgi): #!/usr/bin/perl# Add.cgi: script that adds a new contact (from add.html) # Required libraries use DBI; require "parseform.lib"; require "dbconn.lib"; # Parse fields from form &Parse_Form; # Pass into local variables: my $code=$formdata{'code'}; my $first=$formdata{'first'}; my $last=$formdata{'last'}; my $phone=$formdata{'phone'}; # Mime tag print "Content-type: text/html\n\n"; # Simple validation code: if ($code eq '') { print "Contact code cannot be empty!\n"; exit(0); } # Save to database # Connect to the database my $dbh=DBI->connect(&connectionstring;&dbuser;&dbpassword); my $sql="insert into contact values ('$code','$last','$first','phone'"; $sth=$dbh->do($sql); # Disconnect from database $dbh->disconnect; # Inform user print "Contact added successfully!\n"; print "<a href='add.html'>New entry</a>\n"; # End of script Brief description of add.cgi: #!/usr/bin/perl: this is the shebang line and indicates where to look for the Perl interpreter use DBI: to use the DBI module require "parseform.lib": this is a routine to parse the form. There is not enough space to show the code, but you can see it in detail here. Basically by calling the function &Parse_Form, you will have the value of the input fields of the HTML form, in the format $formdata{'<field name>'}, where <field name> is the input field name from the HTML form (in Perl, this type of array with string indexes is often called a hash). You don't have to know this program by heart or even understand it, , all you have to know is how to call the function and the resulting hash. Feel free to download and use this example. require "dbconn.lib": the example that I mentioned above, about encapsulating the connection string, username and password. &Parse_Form: function to parse the form and retrieve the values (from parseform.lib). # Pass into local variables: passing of form values to local variables. # Mime tag: prepares the script to output (with print) to an HTML page # Simple validation code: validates if the contact code is empty # Save to the database: connects, updates and disconnects the database # Inform the user: let the user know that the operation was successful Remember to always disconnect the database after you're done. Even though DBI implicitly closes the connection after you leave the script, it is recommended to explicitly disconnect from the database. First, to make sure the database handle is disconnected -freeing server resources-, and second, it's a good programming ethic (it's like shutting off the lights when leaving a room). I hope this example has given you a good overview of how to organize and implement your web-based database application using Perl. Remember that these are just samples and you have to adjust the coding of your application to your design. 17. Links and books:There are good reference books for both Perl and DBI: Summary:
18. Next issue:The next issue will be 'Interfacing Perl with a Database (II)' and the last part of this series of articles. We will go over more script examples (editing and deleting records), programming standards, some tips and tricks, and a little bit about security. Marcelo Pham is a writer for the Linux.com Develop Section. Marcelo is a hardcore developer and systems consultant for BCA Inc. , and specializes in System Design, Business Applications and Cross-platform Integration.
| |
| Page 1 of 1 | |