Web Conference 2004 Writing Perl/CGI Scripts for ITS/ASET Web services SQLite

Databases and dynamic Web content

See also: Lesson on using SQLite from PHP.

• Databases, from spreadsheets (like Excell) to Access and beyond - storing atoms of data in tables, creating relationships and querying data

• Relational Database Management Systems (RDBMS)

  • Examples: MySQL, PostgreSQL, SQL Server, Oracle, DB2

• Structured Query Language (SQL)

  • select <items> from <tables> [with some conditions]

  • insert into <table> values <items...>

• SQL92 - a standard that no one fully supports, but the common ground from which each is measured

• Programmatic access to databases - include Open DataBase Conntectivity (ODBC), Java DataBase Connectivity (JDBC), Perl DBI and DBDs,

Perl and databases

Like many other programming languages, Perl has a means to access databases. The Perl DataBase Interface (DBI) is a module of Perl that can access just about any database based on SQL. The DBI shields the programmer from the details of the specific drivers, Perl DataBase Driver (DBD) modules, that need to be loaded for each DBMS.

#!/usr/local/bin/perl

use DBI;

$database_handle = DBI->connect( "dbi:mysql:database=FinanceDB;host=192.168.46.59;port=3306", "joe", "fel1X" );

$query = $database_handle -> prepare("select * from accounts_payable where credit > 20000");

$query -> execute();

print "Query status: " . $database_handle -> state . "\n";

while ( @rows = $query->fetchrow_array() ){
	print "[ ";
	foreach $item (@rows){
		print "{ $item }";
	}
	print " ]\n";
}
$query->finish();

$database_handle->disconnect();

SQLite - a simple file-based database

SQLite is a database system that stores all of its data into a single file. There is no server or running process to connect to like other RDBMSs, and all of the program code to handle reading and writing the file is stored in the library ("module") or program code portion. It implements SQL92, so has relational abilities like mainstream RDBMSs, however there is no security other than what the filesystem grants to the database file. It also doesn't scale beyond the capabilities of access to a single file, but is useful for learning Perl DBI without the work needed to access a regular RDBMS.

SQLite is installed in PASS and usable in Perl CGI scripts from the ITS/ASET Web servers, such as test.scripts.psu.edu.

sql_interface.cgi - a Perl CGI script that uses SQLite. You can install your own copy by following these instructions:

1. Go to the sql_interface.cgi page, and download the source code for "this script" (sql_interface.cgi) and the "internal script" (sql_internal.perl). Use the right mouse button to "Save Target As..." to download as you did for helloworld.cgi.

2. Open sql_internal.perl in WordPad as you did helloworld.cgi.

3. Change the line:

   my $db_file = '/.../dce.psu.edu/fs/users/j/c/jcd/www/useful/webcon/2004/db/database_file';

   To be the path to your database file (which will be created). Such as:

   my $db_file = '/.../dce.psu.edu/fs/users/x/y/xyz123/www/scripts/db/database_file';

4. Edit sql_interface.cgi in WordPad as you did helloworld.cgi and change the HTML to suit your tastes. This step is optional.

5. Upload both files to your scripts folder and make sure you saved them as Text Documents and uploaded them in ASCII mode as you did other CGI script files.

6. While still in SSH Secure File Transfer, create a sub-folder in your scripts folder and name it "db". You can create a "New Folder (Ctrl+N)" via the "Operation" menu.

7. Go to ACL Explorer (via https://www.work.psu.edu/) and set Read and eXecute permissions for everyone on both sql_interface.cgi and sql_internal.perl as you did for other CGI scripts.

8. While still in ACL Explorer, change the permissions on the folder db. Set the permissions for the group test.scripts.psu.edu to have Read, Write, eXecute, Insert and Delete.

If you have any questions, feel free to ask me - mailto:jcd@psu.edu

Content by: Jeff D'Angelo <jcd@psu.edu> © 2004

Last update on: Friday, 27-Apr-2007 10:20:11 EDT