Goals
In this session you will:
- use DBI::DBD to connect to a RDMS
- build graphics on-the-fly
- use cookies to save user information across sessions
POST
- make a CGI app named day4post.cgi. It will leverage CGI module.
- use the built-in functions of CGI for the MIME header, the start of the html page (with title) and the end of the page. (Hint:
man CGI and the Day3 webpage.)
- You will invoke your script with a URL that will simulate data coming from a form (ie, GET). The data passed should include color of car, number of doors, and number of cylinders.
- Parse the results with CGI.pm, and print them out.
Preparing to use DBI
Some normal selects:
- SELECTING
SELECT * FROM TableName
SELECT * FROM TableName where SerialNumber < 100
SELECT SerialNumber, Name FROM TableName
UPDATE TableName SET Name = 'Fred' WHERE SerialNumber = 2
- INSERTing
INSERT INTO table_name (col1, col2...) VALUES(value1, value2...)
- UPDATEing
UPDATE table_name
SET columnname1 = value1
[, columname2 = value2]...
WHERE search_condition
- DELETEing
DELETE FROM tablename
WHERE condition
dbish provides DBI syntax at the shell level; use this for testing your connection and privileges.
dbish DSN username
/table_info gets info on tables in the database
/describe tablename gets info on tables in the database
- SQL commands terminate with the front-slash /
leveraging DBD::DBI
use DBI;
@ary = DBI->available_drivers;
Connecting to the database
Syntax:
$dbh = DBI->connect(DSN, user, pass, \%attr);
Example: $dbh = DBI->connect( 'dbi:mysql:database=mydb;host=myserver;mysql_compression=1', 'myusername', 'mypass', {RaiseError => 1} );
Preparing the SQL query
$sth = $dbh->prepare("SELECT * from Sometable");
Executing the SQL query
$sth->execute;
($sth->dump_results() for testing)
Looping through your SELECTed values
You will use a while() loop wrapped around one of these. Note that these are called on your executed statement handle:
- @row_ary = $sth->fetchrow_array;
- $ary_ref = $sth->fetchrow_arrayref;
- $hash_ref = $sth->fetchrow_hashref;
There are also ways of returning a reference to the entire cursor at once.
SELECTING a single value, or a single reference to an entire cursor
These do not require preparation or execution, since you are returning only one thing.
- @row_ary = $dbh->selectrow_array($statement);
- $ary_ref = $dbh->selectrow_array($statement);
- $hash_ref = $dbh->selectrow_hashref($statement);
INSERTs, UPDATEs, and DELETEs
Any insertions or updates must be prepared for insertion using the $dbh->quote() method. Also makes strangely-quoted WHERE statements easier to use.
$dbh->do("INSERT INTO tablename (fields) VALUES (values)");
Practicum
- write a non-CGI script that accesses the db and dumps the results
- write a CGI app to display all the contents of the db in table
- write a CGI app to display the contents of the db in table where the SELECT matches a criteria
- write a CGI form to get new info, and a CGI app to add it to the database
building graphics
use GD; #leverage the perl interface to the GD library
# make a graphic object of a given size
$image = new GD::Image(100, 150); #w, h
# make some color objects from hex:
$white = $im->colorAllocate(255,255,255);
$red = $im->colorAllocate(255,0,0);
$black = $im->colorAllocate(0,0,0);
# make rectangle
$myImage->filledRectangle(10,10,90,140,$white);
# write some text
$image->string(gdSmallFont,5,10,"Hello, world!",$red);
# explicitly set the binary mode
binmode STDOUT;
print $image->png;
using cookies
- generate some kind of ID, then set the cookie with a header
It is important that cookie IDs not be predictable.
- collect data, and save the ID (primary key) and data in a database
use a form or other technique
- upon future visits, read back the cookie and load user prefs from the database
building the cookie
$cookieRef = $query->cookie(
-name=>'sessionID',
-value=>'xyzzy',
-expires=>'+1h',
-path=>'/cgi-bin/database',
-domain=>'.capricorn.org',
-secure=>1);
Setting a cookie
print $query->header( -cookie=>$cookieRef) ;
Setting multiple cookies
print $query->header(-cookie=>[$cookie1,$cookie2]);
reading a cookie
# simple scalar cookie, suitable for our purposes.
$riddle = $query->cookie('riddle_name');
# hash and array vals are ok, too
%answers = $query->cookie('answers');
Conclusion
Where to go from here
- cookies + javascript
- PHP + PHPMyAdmin
- database design
- PostgreSQL
http://www.mousetrap.net/syllabus/cgiperl/day4.html
$Id: day4.orb,v 1.12 2002/12/05 04:16:13 mouse Exp $
Remember, your login is based on your machine's hostname, not on any other number.
~/[initials] refers to the subdirectory under your homedir, named after your initials. Everything except for .dotfiles will be stored in your ~/[initials] directory.
