squeak!
Syllabus Homepage
Course Overview
Course resources
Day 1
Day 2
Day 3
Day 4
Common errors
Internet Glossary
About Your Instructor
Credits: This site powered by the vi text editor, apache webserver, perl scripting, and Debian linux.
squeak!

CGI/Perl - Day 3

Goals

In this session you will:
  • learn perl references
  • use the CGI.pm module
  • use Carp

POST

  1. log into your Solaris workstation
  2. ssh to emperor
  3. go to your virtual webserver's directory
  4. make an html file called day2post.html. That page should have a form that points to the CGI app in the next step. The form should ask for first and last name and zipcode. Use the POST method.
  5. make a day2post.cgi to parse the preceding form. The script should use cgi-lib.pl to parse and display.

Preparing for CGI.pm: references

references

Perl references are scalars that contain a "reference" (think: pointer) to another variable.
Description Definition Accessing
scalar$scalar = 'frogsticker';print $scalar;
array@array = ('frogsticker', 'toadjumper');print $array[0];
print @array;
hash %hash = ('mynickname', 'frogsticker', 'yournickname', 'toadjumper');

%hash = (mynickname => 'frogsticker', yournickname => 'toadjumper');

print $hash{mynickname};
scalar reference$scalarRef = \$scalar;print $$scalar;
array reference$arrayRef = \@arrayprint $$arrayRef[0];
print $arrayRef->[0];
print @$arrayRef;
hash reference$hashRef = \%hash;print $$hashRef{mynickname};
$hashref->{'mynickname'};
constant reference$constant = \'frogsticker';print $constant;
anonymous array reference$anonArrayRef = ['frogsticker', 'toadjumper']print $$anonArrayref[0];
print $anonArrayRef->[0];
print @$anonArrayRef;
anonymous hash reference$hashRef = {mynickname => 'frogsticker', yournickname => 'toadjumper'};print $$anonHashRef{mynickname};
print $anonHashRef->{'mynickname'};
  • 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

    1. write a non-CGI script that accesses the db and dumps the results
    2. write a CGI app to display all the contents of the db in table
    3. write a CGI app to display the contents of the db in table where the SELECT matches a criteria
    4. write a CGI form to get new info, and a CGI app to add it to the database

    using cookies

    1. generate some kind of ID, then set the cookie with a header
      It is important that cookie IDs not be predictable.
    2. collect data, and save the ID (primary key) and data in a database
      use a form or other technique
    3. 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');

    Homework

    • SQL

    http://www.mousetrap.net/syllabus/cgiperl/day3.html
    $Id: day3.orb,v 1.17 2002/12/05 04:17:26 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.

    • © 1995-2001 jason carr
    Distributed under the terms of the GNU Free Documentation License.