230 likes | 342 Views
C20.0046: Database Management Systems Lecture #21. M.P. Johnson Stern School of Business, NYU Spring, 2005. Homework. Project part 4 due today Topic: populating your tables with data Using MySQL’s bulk loader Start early! Turn in on time Project part 5
E N D
C20.0046: Database Management SystemsLecture #21 M.P. Johnson Stern School of Business, NYU Spring, 2005 M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Homework • Project part 4 due today • Topic: populating your tables with data • Using MySQL’s bulk loader • Start early! • Turn in on time • Project part 5 • Topic: web interface + any remaining loose ends • Posted soon… • Due: end of semester M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Agenda: Programming for SQL • Have now been exposed to: • Embedded SQL: Pro*C • 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 M.P. Johnson, DBMS, Stern/NYU, Spring 2005
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 M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Client Program HTTP Request Data for program HTML Generated HTML Server Review: CGI/Perl • Image from http://www.scit.wlv.ac.uk/~jphb/cp3024/ M.P. Johnson, DBMS, Stern/NYU, Spring 2005
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 M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Form example <form method="get" action=""> Enter a number: <input type="Text“ name="number"><br> <input type="Submit" name="submit" value="OK"> </form> On clicking Send, we go to the same page, but with “name=99&sumbit=OK” • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/input.cgi M.P. Johnson, DBMS, Stern/NYU, Spring 2005
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/input.cgi • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/inputcgi.txt • Improve: also print, say, triple the input… my $cgi = CGI->new(); $param = $cgi->param('number'); M.P. Johnson, DBMS, Stern/NYU, Spring 2005
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 M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Perl and databases • DB connectivity is done through DBI • Database Interface • Analogous to Java’s 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. M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Perl DBI • Open a connection: • Prepare and execute query: my $dbh = DBI-> connect("dbi:mysql:database=mydb;mysql2.stern.nyu.edu;port=3306", user, pass); my $sth = $dbh->prepare($query); $sth->execute; M.P. Johnson, DBMS, Stern/NYU, Spring 2005
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->fetchrow_array() • while (my ($a, $b) = $sth->fetchrow_array()) { • # print out $a and $b • } M.P. Johnson, DBMS, Stern/NYU, Spring 2005
Limit: Perl webpages that do something • Semi-interesting Perl script: • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/lookupcgi.txt • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/lookup.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! M.P. Johnson, DBMS, Stern/NYU, Spring 2005
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 page’s url • (Try to) read input parameter • Open MySQL connection • Run query • Print results in a table • Disconnect from MySQL M.P. Johnson, DBMS, Stern/NYU, Spring 2005
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 M.P. Johnson, DBMS, Stern/NYU, Spring 2005
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 M.P. Johnson, DBMS, Stern/NYU, Spring 2005
That’s all, folks! • Q: Is this enough to get a job coding Perl? • A: Probably not! • But: • Don’t like Perl/CGI? • Don’t want to run start a new process for every user/pageview/roundtrip of your site? • Next we’ll do PHP… a couple modified copies of lookup.cgi and/or cia.cgi + some HTML glue fairly interesting site M.P. Johnson, DBMS, Stern/NYU, Spring 2005
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. M.P. Johnson, DBMS, Stern/NYU, Spring 2005
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 M.P. Johnson, DBMS, Stern/NYU, Spring 2005
hello.php • http://pages.stern.nyu.edu/~mjohnson/dbms/php/hello.php • Q: What the difference between <br> and \n? <html> <head><title>Hello from PHP</title> </head> <body> Here is the PHP part:<BR><BR> <?php print "Hello, World!<br>\n“; ?> <br>That's it! </body></html> M.P. Johnson, DBMS, Stern/NYU, Spring 2005
hello2.php • Script errors, w/ and w/o display_errors on: • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/hello2.php • http://pages.stern.nyu.edu/~mjohnson/dbms/php/hello2.php • Local dir must contain .htaccess: • Automatically load GET/POST params as vars • http://pages.stern.nyu.edu/~mjohnson/dbms/php/.htaccess php_flag display_errors on php_flag register_globals on M.P. Johnson, DBMS, Stern/NYU, Spring 2005
For next time… • Run/read these Perl scripts: • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/input.cgi • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/inputcgi.txt • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/update.cgi • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/updatecgi.txt • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/cia.cgi • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/cia.pl M.P. Johnson, DBMS, Stern/NYU, Spring 2005
For next time… 2. Run/read these PHP scripts: • http://pages.stern.nyu.edu/~mjohnson/dbms/php/lookup.php • http://pages.stern.nyu.edu/~mjohnson/dbms/php/lookupphp.txt • http://pages.stern.nyu.edu/~mjohnson/dbms/php/update.php • http://pages.stern.nyu.edu/~mjohnson/dbms/php/updatephp.txt • http://pages.stern.nyu.edu/~mjohnson/dbms/php/cia.php • http://pages.stern.nyu.edu/~mjohnson/dbms/php/ciaphp.txt • Various others in dbms/perl and dbms/php… M.P. Johnson, DBMS, Stern/NYU, Spring 2005