C20'0046: Database Management Systems Lecture

1
C20.0046 Database Management SystemsLecture 20

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

2
Homework

• Project part 4 due Thursday
• 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
• Assigned after Thursday
• 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

• Understand dynamic webpages
• CGI
• PHP-like scripting
• Today be able to post a hello-web Perl program
  in your sales account
• This week Be able to write simple dynamic
  webpages in
• In Perl
• In PHP
• that
• That do look-ups with user-entered parameters
• And display the results
• Based on examples from class

5
New topic web apps

• Goal web front-end to database
• Present dynamic content, on demand
• Not canned (static) pages/not canned queries
• (perhaps) modify DB on demand
• Naïve soln static webpage HTTP
• index.html written, stored, put on server,
  displayed when its url is requested
• HTTP is stateless (so?)
• This doesnt solve our problem

6
Dynamic webpages

• Soln 1 upon url request
• somehow decide to dynamically generate an html
  page (from scratch)
• send back new html page to user
• No html file exists on server, just created on
  demand
• CGI/Perl, Java servlets, etc.

7
New topic CGI

• First, and still very popular method
• CGI Common Gateway Interface
• Not a programming language!
• Just an interface (connection) between the
  webserver and an outside program
• Webserver webserver software, e.g., Apache
• Very simple basic idea
• user chooses an url
• ? webserver runs that urls program,
• sends back the programs output

8
On-the-fly content with CGI

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

9
Using CGI

• CGI works with any prog./scripting lang.
• Really?
• Well, no, not really

10
CGI works

• if the webserver machine can run program
• pages/soho, not sales
• and if the user the webserver is running as (e.g.
  nobody) can can run your program
• and if the necessary jars/libraries are available
• and if nobody has permission to use them
• and if the necessary DB software is installed
• Plausible choices Perl, Python, C, sh

11
CGI admin

• Most webservers CGI program/script must either
• End in .cgi and/or
• Reside in cgi-bin
• Ours needs .cgi extension
• If an actual program, the cgi file is just the
  name of the executable

gcc -o myprog.cgi myproc.gcc
12
CGI admin

• In a script, first (shebang) line says which
  interpreter to use
• Either way, cgi file must be executable
• Make sure your cgi file runs at cmd prompt
• But not a guarantee!

!/usr/local/bin/perl
sales chmod x .cgi
sales ./myprog.cgi
13
CGI input

• CGI programs must respond to input
• Two mechanisms
• GET read env. var. QUERY_STRING
• POST get length from env. var. CONTENT_LENGTH
  read from STDIN
• This diff. mostly invis. to Perl, PHP
• Both send a sequence of name/value pairs,
  separated by s

nameasubmitSearch
14
CGI input

• Appearance/security differences
• GET string is part of the URL, following a ?
• POST string can be read by program from an
  environmental variable
• Vars not visible to the browser user
• Not automatically put in server log, etc.

http//google.com
http//pages.stern.nyu.edu/mjohnson/dbms/perl/loo
kup.cgi
15
Our use of CGI

• Well discuss CGI and Perl
• One option for your project
• Can try C, C, etc.
• But not recommended!
• For CGI, only Perl will be supported
• Scripting languages v. programming languages
• Development v. IT
• Other languages are still not recommended
  especially if you dont know Perl and PHP

16
New topic Just Enough Perl

• Very popular, powerful scripting language
• Very good at regular expressions, text
  manipulation, but not very relevant to us
• Instead
• simple text/html production
• Basic language constructs
• MySQL connectivity
• Perl Practical Extraction and Report Language
• Pathologically Eclectic Rubbish Lister

perl -pi -e 's/tcsh/sh/' HOME/.login
See http//perl.org.il/pipermail/perl/2003-Februar
y/001047.html
17
hello.pl

• Hello, World - hello.pl
• Running at command prompt

!/usr/bin/perl -w print "Hello World\n"
sales perl hello.pl Hello World sales
18
Hello, World - hello.pl

• Run from browser
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/hel
  lo.pl
• Whats wrong?
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/hel
  lo.cgi
• Whats wrong?
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/hel
  lo2.cgi
• Whats wrong?

19
Troubleshooting hello.cgi

• Get the extension right
• Try running with perl
• Are there Perl errors?
• Try running as program
• Are the execute permissions on?

sales cp hello.pl hello.cgi
sales perl hello.cgi
sales ./hello.cgi sales chmod x hello.cgi
20
Troubleshooting hello.cgi

• 5. Make sure youre printing the HTML header

! /usr/bin/perl -w print "Content-type
text/html\n\n" print "Hello World\n"
21
Troubleshooting hello.cgi

• 5. Show errors and warnings
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/he
  llo3.cgi
• Is case-sensitive

! /usr/bin/perl -w use CGI qw(standard) use
CGICarp qw( fatalsToBrowser warningsToBrowser
) print header() pr int "Hello World\n"
22
Perl and HTML headers

• Data sent to a browser is prefaced with a header
  describe type of data
• Hand-generated html must print this before
  anything else
• Or
• When use-ing CGI

Content-type text/html\n\n
print "Content-type text/html\n\n"
print CGIheader()
23
Perl, HTML, and CGI.pm

• CGI.pm offers a front-end to HTML
• Replaces mark-up language with an API
• Very simple example
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/cgi
  pm.pl
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/cgi
  pm.cgi
• Somewhat simpler, but another thing to learn
• Mostly wont cover

Review Hello, World
24
More on Perl

• Perl is mostly C-like
• Perl is case-sensitive
• Use for rest-of-line comments
• Creation of functions is supported but optional
• Like PL/SQL
• Perl has modules/packages
• CGI module
• Provides header() function, easy access to CGI
  params
• Mysql module

use CGI qw(standard)
use Mysql
25
Perl and strings

• Can use for strings
• Concatenate with . op
• Print text with print function
• Or, parentheses can be dropped!

Hi . there\n
print (Hi there)
print Hi there
26
Perl and strings

• Can compare numbers (as numbers) with usual
  operators
• lt gt lt, etc.
• 3 lt 5
• These do not apply to strings
• String ops are based on initials of operations
• eq, ne, lt, gt, le, ge
• hi ne there
• hi le hi there

27
Perl and variables

• All regular variables begin with
• input, query
• Declare vars with my
• Q What about var types?
• A Perl is loosely typed!

my s "hi" my query "select "
my s "hi" s 10 s 3.5
28
Perl, strings, and variables

• print takes var-many arguments
• Variables are always escaped
• Vars may appear within strings
• Prints out Hello Dolly.
• To prevent escaping, use single quotes 'name

print ("Hello ", "Dolly", ".\n")
name "Dolly"
print ("Hello name.\n")
29
Perl syntax examples

• Access member/field of object
• objectmember
• Access member pointed to by object -gt
• rowhash-gtfield
• Can access array members with indices
• Can access hash members with strings
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/con
  trols.cgi
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/con
  trolscgi.txt

30
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/Perl/perl-basics-1
  .html
• CGI Basics
• http//www.cs.wcupa.edu/rkline/Perl/cgi-basics-1.
  html
• MySQL/Perl/CGI example
• http//www.scit.wlv.ac.uk/jphb/sst/perl/ex3d.html
  

31
Tutorials on PHP

• Some material drawn from the following good
  tutorials
• http//php.net
• PHP introduction and examples
• http//www.scit.wlv.ac.uk/jphb/sst/php/
• Interactive PHP with database access
• http//www.scit.wlv.ac.uk/jphb/sst/php/gazdb.html
  
• Longer PHP/MySQL Tutorial from webmonkey
• http//hotwired.lycos.com/webmonkey/99/21/index2a.
  html
• Nice insert/update/delete example from webmonkey
• http//hotwired.lycos.com/webmonkey/99/21/index3a.
  html
• MySQL/Perl/PHP page from U-Wash
• http//www.washington.edu/computing/web/publishing
  /mysql-script.html

32
For next time

• Go through at least one tutorial each on Perl and
  PHP
• Try posting a hello-web Perl script in your sales
  account
• Run/read these
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/con
  trols.cgi
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/con
  trolscgi.txt
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/loo
  kup.cgi
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/loo
  kupcgi.txt