1 / 23

Advanced Database Management Systems: Integrating SQL with Web Technologies

This lecture focuses on essential programming techniques for SQL and web integration using Perl and PHP in the context of Database Management Systems (DBMS) at Stern School of Business, NYU. Covered topics include utilizing MySQL’s bulk loader for data population, building dynamic web interfaces, and employing CGI with Perl for enhanced data interaction. Key objectives are to enable students to retrieve and display database results on the web effectively. Homework and project deadlines are emphasized to encourage timely submissions and hands-on practice.

coy
Télécharger la présentation

Advanced Database Management Systems: Integrating SQL with Web Technologies

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. C20.0046: Database Management SystemsLecture #21 M.P. Johnson Stern School of Business, NYU Spring, 2005 M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  2. 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

  3. 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

  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 M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  5. 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

  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 M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  7. 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

  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/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

  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 M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  10. 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

  11. 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

  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->fetchrow_array() • while (my ($a, $b) = $sth->fetchrow_array()) { • # print out $a and $b • } M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  13. 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

  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 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

  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 M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  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 M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  17. 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

  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. M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  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 M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  20. 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

  21. 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

  22. 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

  23. 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

More Related