C20'0046: Database Management Systems Lecture

1
C20.0046 Database Management SystemsLecture 21

• M.P. Johnson
• Stern School of Business, NYU
• Spring, 2005

2
Homework

• Project part 4 due today
• Topic populating your tables with data
• Using MySQLs bulk loader
• Start early!
• Turn in on time
• Project part 5
• Topic web interface any remaining loose ends
• Posted soon
• Due end of semester

3
Agenda Programming for SQL

• Have now been exposed to
• Embedded SQL ProC
• Java JDBC
• Stored Procedures PL/SQL
• All used good to know about
• Most important for this course
• DB-conn from web scripting languages
• DBI/DBDs in Perl, PHP

4
Goals after this week

• After Tuesday be able to post a hello-web Perl
  script in your sales account
• After Today
• be able to post a hello-web PHP script in your
  sales account
• Be able to modify/extend non-trivial Perl/PHP
  scripts to work with your DB
• Take input from user
• Execute SQL query
• Display formatted results

5
Review CGI/Perl

• Image from http//www.scit.wlv.ac.uk/jphb/cp3024/

6
New topic HTML forms

• Interactive parts of HTML forms
• Intuition for name paper form
• Fill in textboxes, check boxes or not, etc.
• Turn it in (press button)
• HTML form
• contains arb. of INPUTs
• Submits to somewhere (ACTION)
• By GET or POST

7
Form example
ltform method"get" action""gt Enter a
number ltinput type"Text name"number"gtltbrgt ltinp
ut type"Submit" name"submit" value"OK"gt lt/formgt

On clicking Send, we go to the same page, but
with name99sumbitOK

• http//pages.stern.nyu.edu/mjohnson/dbms/perl/inp
  ut.cgi

8
Perl and forms

• Obtain param number
• Goal display text and button
• On submit, tell user what was entered
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/inp
  ut.cgi
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/inp
  utcgi.txt
• Improve also print, say, triple the input

my cgi CGI-gtnew() param cgi-gtparam('number
')
9
Perl error-handling

• Many Perl scripts have lines of the form
• some-statement OR die(something happened)
• What this means
• die exits with error message
• Perl supports both and OR as or operator
• Perl supports boolean short-circuiting
• Boolean eval stops as fast as possible
• Ftns often return 0/null/false for errors
• ? if some-statement fails then we die

10
Perl and databases

• DB connectivity is done through DBI
• Database Interface
• Analogous to Javas JDBC
• The steps correspond roughly 1-1
• Think of DBI as a Java class with static methods
• Use these to obtain a connection, prepare and
  execute queries, etc.

11
Perl DBI

• Open a connection
• Prepare and execute query

my dbh DBI-gt connect("dbimysqldatabasemydbm
ysql2.stern.nyu.eduport3306", user, pass)
my sth dbh-gtprepare(query) sth-gtexecute
12
Perl DBI

• Extract next row of data from statement results,
  if available
• What this means row has two fields, whose values
  are put in a and b, in order
• Other options, but this should suffice
• In general, want to scroll through results
• Braces are required!

my (a, b) sth-gtfetchrow_array()
while (my (a, b) sth-gtfetchrow_array())
print out a and b
13
Limit Perl webpages that do something

• Semi-interesting Perl script
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/loo
  kupcgi.txt
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/loo
  kup.cgi
• Non-trivial but not huge 40 lines
• Works with two-column (a,b) table
• Takes input from user
• Returns rows whose a field contains value
• If no/empty input, returns all rows
• Bad idea in general!

14
lookup.cgi

• Two possible situations for running script
• Page opened for the first time
• User entered parameter and pressed button
• Structure of file
• Print input box and button for next search
• On button click, parameter is sent to this pages
  url
• (Try to) read input parameter
• Open MySQL connection
• Run query
• Print results in a table
• Disconnect from MySQL

15
Higher-level structure

• As one page
• If we have params, display data based on them
• Otherwise, prompt user for params, call self
• Could be
• Page 1 prompt for params, call page 2
• Page 2 display data based on params
• In e.g. always display data for convenience

16
Tutorials on Perl

• Some material drawn from the following good
  tutorials
• http//perl.com
• CGI backend programming using perl
• http//www.scit.wlv.ac.uk/jphb/sst/perl/
• Perl Basics
• http//www.cs.wcupa.edu/rkline/csc417/perl-basics
  -1.html
• CGI Basics
• http//www.cs.wcupa.edu/rkline/csc417/cgi-basics-
  1.html
• MySQL/Perl/CGI example
• http//www.scit.wlv.ac.uk/jphb/sst/perl/ex3d.html
  

17
Thats all, folks!

• Q Is this enough to get a job coding Perl?
• A Probably not!
• But
• Dont like Perl/CGI?
• Dont want to run start a new process for every
  user/pageview/roundtrip of your site?
• Next well do PHP

a couple modified copies of lookup.cgi and/or
cia.cgi some HTML glue ? fairly interesting site
18
Dynamic webpages

• Original prob need webpages to respond to user
  inputs
• Soln 2
• create a an html file embedded with special
  non-html code
• upon url request, execute embedded code to
  generate more html/fill in the file
• Send back the modified html page to user
• An incomplete html page exists on server
• Examples PHP, JSPs, ASPs, etc.

19
Review dynamic webpages

• First option for each request run program,
  produce whole page, send back
• CGI some host language, Java Servlets, etc.
• Second option create html page with missing
  parts for each response, fill in the wholes and
  send back
• Embedded scripting
• PHP and others
• PHP Personal Home Page or
• PHP Hypertext Processor

20
hello.php

• http//pages.stern.nyu.edu/mjohnson/dbms/php/hell
  o.php
• Q What the difference between ltbrgt and \n?

lthtmlgt ltheadgtlttitlegtHello from PHPlt/titlegt lt/headgt
ltbodygt Here is the PHP partltBRgtltBRgt lt?php print
"Hello, World!ltbrgt\n ?gt ltbrgtThat's
it! lt/bodygtlt/htmlgt
21
hello2.php

• Script errors, w/ and w/o display_errors on
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/hel
  lo2.php
• http//pages.stern.nyu.edu/mjohnson/dbms/php/hell
  o2.php
• Local dir must contain .htaccess
• Automatically load GET/POST params as vars
• http//pages.stern.nyu.edu/mjohnson/dbms/php/.hta
  ccess

php_flag display_errors on php_flag
register_globals on
22
For next time

• Run/read these Perl scripts
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/inp
  ut.cgi
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/inp
  utcgi.txt
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/upd
  ate.cgi
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/upd
  atecgi.txt
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/cia
  .cgi
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/cia
  .pl

23
For next time

• 2. Run/read these PHP scripts
• http//pages.stern.nyu.edu/mjohnson/dbms/php/look
  up.php
• http//pages.stern.nyu.edu/mjohnson/dbms/php/look
  upphp.txt
• http//pages.stern.nyu.edu/mjohnson/dbms/php/upda
  te.php
• http//pages.stern.nyu.edu/mjohnson/dbms/php/upda
  tephp.txt
• http//pages.stern.nyu.edu/mjohnson/dbms/php/cia.
  php
• http//pages.stern.nyu.edu/mjohnson/dbms/php/ciap
  hp.txt
• Various others in dbms/perl and dbms/php