Advanced PERL programming with Database and CGI

Document Sample
Advanced PERL programming with Database and CGI Powered By Docstoc
					                   What is PERL?
    Perl is an acronym for "Practical
     Extraction and Report Language",
    It is an interpreted language that is
     optimized for string manipulation, I/O,
     and system tasks. It incorporates
     syntax elements from the Bourne shell,
     csh, awk, sed, grep, and C.
    It is FREE!


30 April 2001                                  2
  Form of a Perl Program
    Perl is a “scripted” language
         Commands are usually placed in a
          text file which is then interpreted by
          the Perl interpreteer
    Perl program are free-form
         whitespace is ignored between
          tokens
         semicolon(;) used as statement
          separator


30 April 2001                                      3
                    Hello World!
      A simple program
                #!/usr/bin/perl
                print “Hello world\n”;

         run a Perl program
         $ perl programname

30 April 2001                            4
                Variables in PERL
      Three types of variables
            Scalar: Single valued variables
            Array: A list of scalars indexed by
                     number
            Hash: A list of scalars indexed by
                    strings
      All variables are global unless
       otherwise declared.

30 April 2001                                      5
                   Scalars
    All scalars start with the $ character
    Scalars are typeless...no differentiation
     between string, integer,floating point,
     or character values
    No declaration needed to use a scalar
     $a=12; $b=„hello‟;          $c=„11‟ ;
     $d=$c * 2;
    # $d is 24
     $e=“hello”;

30 April 2001                                6
           Variable Interpolation
    Within double-quoted string, Perl performs direct
     substitution of a variable‟s value within that string.
    In single-quoted strings, no substitution is performed.
       $d=224;

       $a=141;

       Print $d,”\n”;          # 224 followed by newline
       Print “$d\n”;           # same thing
       $sir=“is Perl”;

       $str=“$sir $a”;      # $str equals “is Perl 141”
       print “This $str.\n”;           #This is Perl 141.
       Print „This $str.\n‟ ;           #This is $str. \n


30 April 2001                                             7
                            Arrays
    Arrays start with the @ character
    Dynamically allocated; no size limits
    No „out of bound‟ errors
    Arrays require no pre-initialization
    Heterogeneous values
      $a   =1; $b =2; $c =3;
      @a   =(1,2,3);                  # 1 2 3
      @b   =(„Hello, „Baba‟, „Mama‟); # Hello Baba Mama
      @c   =(123, „xyz‟, 24, „what‟); # 123 xyz 24 what
      @d   =($a, $b, $c);             # 1 2 3
      @e   =(@a, @b);                 # 1 2 3 Hello Baba mama
 Note that arrays are “flat”, @e has six scalar elements,
  not two array elements

30 April 2001                                                8
                Scalar and Array Distinction
   The Scalar $a and array @a refer to different
     data values
    $a = 141;
    @a = („yes‟, „no‟, „maybe‟);
    Print “$a”;                #141
    Print “@a”;               # yes no maybe




30 April 2001                                      9
     Accessing Individual Elements of an
                   Array
    @a = („yes‟, „no‟, „maybe‟);
    $b = $a[1];        # $b is “no”
    $c = $a[2];        # $C is “maybe”
    We use the “$” here (instead of the “@‟) because the
     elements of an array are scalars. If we use the „@‟, we
     get back a list:

    @c = @a[2];         # @c = („maybe‟)
    @d = @a[0,2,0,2,1];
    # @d= („yes‟, „maybe‟,       „yes‟, maybe,     „no‟)
    Print $a[3];
    # nothing

30 April 2001                                               10
  Interpolation of Arrays

    If an array is placed in a double-quoted string, the
     elements of the array are substituted (separated by
     spaces):
       @what= („an‟, „array‟);
       print “ This is @what.”
       # “This is an array.”




30 April 2001                                               11
                           Array Functions
            join („expr‟,list)
               Joins the elements of list into a single string with
                elements separated by expr
             @c = („Union‟, „City of‟, „New Jersey‟, 08);
             print join(„:‟, @c);
             # Union:City of:New Jersey:08

            split (/expr/, string)
               Splits string delimited by expr into an array of strings

             $x = „Union:City:New Jersey:08‟;
             @a = split(/:/, $x);
             #@a= („Union‟, „City‟, „New Jerey‟ ‟08‟)


30 April 2001                                                              12
                    More Array Functions
       push (array, expr)
          Adds expr to the end of array
         @a = (1,2,3);
          push (@a, „what‟);         # @a = (1, 2, 3, „what‟)

       pop(array)
          Removes the last element of array and returns it
         $b = pop(@a);             # $b = „what‟, @a=(1,2,3)

       unshift (array, expr)
            Similar to push(), but adds expr to the beginning of array
       shift (array)
            Similar to pop(), but removes the first element of array and
             returns it

30 April 2001                                                        13
  Continue
    sort (array)
        returns the elements of array in sorted order
       @a = („abc‟, 12, „bob‟);
        @b = sort(@a);
        # @b = (12, „abc‟, „bob‟); @a unchanged

    reverse (array)
      returns the elements of array in reverse order
        @a = („abc‟, ‟12‟, „bob‟);
        @b = reverse(@a);
        # @b = („bob‟, 12, „abc‟)


30 April 2001                                            14
                         Hashes
    A hash stores a list of scalars indexed by
     string instead of numbers
    Hash variables start with % character
    Elements in a hash are unordered
    Also called “associative arrays”

    Hash Initialization:
         %users = („Adel‟ => „Right‟,
                   „Hong‟ => „Rainbow‟,
                   „Ali‟ => „Power‟);


30 April 2001                                     15
                   Hash Access
       %users = ( „Adel‟ => „Right‟,
                 „Hong‟ => „Rainbow‟,
                 „Ali‟ => „Power‟ );
      $a = $users{„Adel‟);     # $a contains „Right‟
      $b = $users{ „Hong‟);    # $b contain „Rainbow‟
      ($c, $d) = @users{„Adel‟,‟Hong‟};
       # multiple elements can be assigned to
       scalars
          # simultaneously




30 April 2001                                      16
                   Hash Functions
 Keys (hash)
       returns an unordered list of the keys in hash
      %users = („Adel => „Right‟,„Hong‟ =>
        „Rainbow‟, „Ali‟ => „Power‟);
      # %user = („Adel‟, „Right‟, „Hong‟, „Rainbow‟,
        „Ali‟, „Powel‟);
         @k = keys(%users);
         # @k = ( „Hong‟, „Ali‟, Adel‟)
    values(hash)
         returns an unordered list of values in hash
          @v = values(%users);
         # @v = („Rainbow, Power‟, „Right‟)

30 April 2001                                           17
                 Foreach Looping
         If, for while
         C/C++/Java like for loop
           Foreach loop ( new )
                @words = (“Now”, “is”, “the”,
                “time”);
                foreach $w (@words) {    print
                “$w”;}
                # Nowisthetime

30 April 2001                                    18
                               File I/O
       Special Filehandles
            STDIN              Compare C++ cin
            STDOUT                         cout
            STDERR                         cerr


      Diamond Operator: <>
            Read input from keyboard
             $name = <STDIN>;

            Reads lines from files given on the command line or STDIN
             if nongiven
            Returns false (undef) on end-of-file


30 April 2001                                                     19
  User Defined Subroutines
 sub Hi
 {    ($name, $city) = @_;
       $name = $_[0];
       $city = $_[1];
      print “Hello $name! Welcome to $city”;
 }
     Hi ( );
     # Hello! Welcome to!
     Hi („everybody‟);
     #Hello everybody! Welcome to
     Hi („everybody‟,‟Kean University‟) ;
       #Hello everybody! Welcome to Kean University
30 April 2001                                      20
  Wed application using
  PERL/CGI

                      A simple adder
                 Enter the first number
                Enter the second number

                                       Reset
                      Add




30 April 2001                                  21
    Html code
<html><head><title>Adder</title></head>
<body><p align="center”>A simple adder</p>
<form method="POST" action=
  "http://www.askli.com/cgi-bin/add.cgi">
<p align="center">Enter the first
  number<input type="text" name="num1" > </p>
 <p align="center">Enter the second
  number<input type="text" name="num2" ></p>
  <p align="center"><input type="submit"
  value="Add" name="B1"></p>
</form></body></html>

  30 April 2001                            22
      Wed application using
      PERL/CGI
          Read input from HTML form post method
read(STDIN, $data, ENV{"CONTENT_LENGTH"});
@field = split(/&/, $data);
foreach $item (@field){
    ($name, $value) = split(/=/, $item);
    $value{$name} = $value;
}

    30 April 2001                                  23
  Continue
      Read input from HTML get method
      Use CGI qw(param)
         $num1= param("num1");
         $num2 = param(“num2");
      Where num and sec are names of input
       fields from HTML form


30 April 2001                            24
    Create HTML document
#!/usr/bin/perl
use CGI qw(param);
read(STDIN, $data, ENV{"CONTENT_LENGTH"});
@field = split(/&/, $data);
foreach $item (@field){
  ($name, $value) = split(/=/, $item);
  $value{$name} = $value;
}
print "Content-type: text/html\n\n";
print “<html><head>”;
Print <<start;
<title> Grade Summary/title></head><body >Summation =
Start
Print “$sum”; Print “</body></html>”;
  30 April 2001                                    25
  Hands-on
   1.     Open add.html
   2.     If you wish, you can add more in your
          HTML
   3.     Set the method attribute in form as
          either get or post
   4.     Set the
          action=“http://www.askli.com/cgi-
          bin/add_yourname.cgi”

30 April 2001                                26
  Continue:
   Edit the perl/cgi program
 1. Copy add.cgi to add_yourname.cgi
 2. open add_yourname.cgi from notepad
 3. After 3rd line insert, if you use get method in html
    $num1= param("num1");
    $num2 = param(“num2");
 4. Insert following if you use post method in html
 read(STDIN, $data, ENV{"CONTENT_LENGTH"});
 @field = split(/&/, $data);
 foreach $item (@field){
    ($name, $value) = split(/=/, $item);
    $value{$name} = $value;}
    $num1 = $value{“num1”};
     $num2
30 April 2001   = $value{“num2”};                          27
  Continue
   $sum = $num1+$num2;
   print "Content-type:
     text/html\n\n";
   print “<html><head>”;
   Print <<start;
   <title> Grade Summary/title></head>
   <body >Summation =
   Start
   Print “$sum”;
   Print “</body></html>”;
30 April 2001                       28
  continue
      Save the add_yourname.cgi
      Use the ftp to upload the
       add_yourname.cgi to server
      Test: open add.htm click on add




30 April 2001                            29
       PERL AS A DATABASE TOOL
              USING DBI
    What is DBI( DataBase Inteface)?
    DBI is an interface specification for accessing
     databases from Perl programs
      Allows Perl programs to access databases in
        Oracle, mySQL, Informix, SQL Server, etc.
    DBI is a database-independent set of routines
     for manipulating databases
    DBD (DataBase Driver)modules interface
     between DBI and the underlying database
     management system (these are specified to a
     particular database you intend to use)

30 April 2001                                     30
  Continue
      How do I find out which drivers (DBDs) are
       installed?
            #!/usr/bin/perl
            Use DBI;
            @drivers = DBI -> available_drivers ();
            Foreach $driver (@drivers) { print “
             $driver\n”;}
      For example, the above code may produce:
            Proxy
            Mysql
            Oracle


30 April 2001                                          31
         Connecting to a database
      #!/usr/bin/perl
      use DBI;
      $datasource =
       “dbi:mysql:database_name”;
      $Username = “guest”;
      $password = “changeme”;
      $dbh = DBI - > connect ($databsource,
       $username, $password);
      # Perform some operations on the
       database
      $dbh ->disconnect();
30 April 2001                                  32
        Executing SQL Statements
      Use the do () routine:
            $dbh -> do (“ CREAT TABLE people ( lname
             CHAR(10), fname CHAR(10), city CHAR(20),
             country CHAR(10))”);

            $dbh -> do(“ INSERT INTO people VALUES
             („ALI‟, „Setoodehnia‟, „Sirjan‟,‟IRAN‟)”);

            $dbh -> do(INSERT INTO people VALUES
             („HONG‟, „LI‟,‟Zhengzhou‟,‟CHINA‟)”);
            $dbh -> do(INSERT INTO people VALUES
             („Adel‟, „Setoodehnia‟,‟Norman‟,‟OK‟)”);


30 April 2001                                           33
                Querying a database
      prepare/execute
       $sql = “SELECT * FROM people”;
       $sth = $dbh ->prepare ($sql”);
       $sth ->execute ();
      retrieving data
          @row = $sth -> fetchrow_array();
          # return one row as array from the query

          $href = $sth -> fetchrow_hashref();
          %row = %{$href};



30 April 2001                                         34
                Querying a database
  #!/user/bin/perl
  Use DBI;
  $dbh = DBI -> connect („dbi: mysql:test‟,
                           „user‟, „pass‟);
  $sth = $dbh -> prepare (“SELECT * FROM people”);
  $sth -> execute ();
  while (@row = $sth -> fetchrow_array ( )) {
   ($lname, $fname, $city, $country) = @row;
    print “$fname $lname is from $city ,
   $country\n”;}
  $dbh ->disconnect ();
    Ali Setoodehnia is from Sirjan, Iran
   Hong Li is from Zhengzhou, CHINA
   Adel Setoodehnia is from Norman,OK

30 April 2001                                    35
  Continue
      #!/user/bin/perl
       use DBI;
       $dbh = DBI -> connect („dbi:mysql:test‟, „user‟,
       „pass‟);
       $sth = $dbh -> prepare(“ SELECT fname, lname, city
       WHERE Country = „CHINA‟ ORDER BY fname”);
       $sth -> execute();
      while (@row = $sth -> fetchrow_array ( ))
      { ($lname, $fname, $city) = @row;
       print “$fname $lname is from $city CHINA\n”;}
                Hong Li is from Zhengzhou, CHINA
                Jin Li is from Zhengzhou, CHINA


30 April 2001                                           36
                Example Application
      http://www.kean.edu/~ASEE
      http://www.askli.com




30 April 2001                         37
    Answer using do()
     #!/usr/bin/perl
     $dbh = DBI -> connect („dbi:mysql:test‟, „user‟,
     „pass‟);
      open (INFILE, “<tfile”) || die :”can‟t open tfile”;
     while ($line = <INFILE>) {
       Chomp $line;

       ($lname, $fname, $city, $country)= split(/:/,
        $line);
       $dbh->do(“INSERT INTO people VALUES („$lname‟,
        „$fname‟,‟$city‟, „$country‟)”);
       }

    $dbh -> disconnect;
    This method is inefficient because that the SQL must be
     read and parsed for each row
    It is also unsafe due to quoting issues
30 April 2001                                              38
  Better answer using
  placeholders
       What are placeholders?
      Executing the same SQL statement with different set of
       data
       #!/usr/bin/perl
       $dbh = DBI -> connect(„dbi:mysql:test‟, „user‟,„pass‟);
       $sth = $dbh -> prepare(“INSERT INTO people VALUES
                                (?,?,?,?)”);
       Open (INFILE, “<tfile”) || die “Can‟t open tfile”;
       while ($line = >INFILE>)
          { chomp $line;
             ($lname, $fname, $city, $country) = split(/:/,$line);
             $sth ->execute ($lname, $fname, $city, $country);
           }
       $dbh -> disconnect;


30 April 2001                                                  39
  Error Handling in DBI
       How do you know when thing goes wrong?
             DBI routines return a false value when there is an
              error
             The special variable $DBI: : errstr is set to an
              appropriate error message

             $dbh -> connect („dbi:mysql:x‟, ‟user‟, „pass‟) ||
                 die”Unable to connect - $DBI: :errstr\n”;
             $sth = $dbh ->prepare(“SELECT * FROM people”) ||
                  die “You have an error in your SQL - $DBI: :
               errstr\n”;
             $sth -> execute() ||
                die “Error executing the SQL - $DBI: :errstr\n”;

30 April 2001                                                      40
  RESOURCES
      DBI-
       http://rpmfind.net/linux/rpm2html/search.php?query=perl-
       DBI

      DBD-http://www.perl.com/CPAN-local/modules/by-
       module/DBD/




30 April 2001                                               41