1 / 32

C20.0046: Database Management Systems Lecture #20

C20.0046: Database Management Systems Lecture #20. M.P. Johnson Stern School of Business, NYU Spring, 2005. Homework. Project part 4 due Thursday Topic: populating your tables with data Using MySQL’s bulk loader Start early! Turn in on time Project part 5

kamana
Download Presentation

C20.0046: Database Management Systems Lecture #20

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 #20 M.P. Johnson Stern School of Business, NYU Spring, 2005 M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  2. Homework • Project part 4 due Thursday • 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 • Assigned after Thursday • 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 • 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 M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  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 it’s url is requested • HTTP is stateless (so?) • This doesn’t solve our problem M.P. Johnson, DBMS, Stern/NYU, Spring 2005

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

  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 url’s program, • sends back the program’s output M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  8. Client Program HTTP Request Data for program HTML Generated HTML Server On-the-fly content with CGI • Image from http://www.scit.wlv.ac.uk/~jphb/cp3024/ M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  9. Using CGI • CGI works with any prog./scripting lang. • Really? • Well, no, not really… M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  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 ifnobody has permission to use them • and if the necessary DB software is installed • Plausible choices: Perl, Python, C, sh M.P. Johnson, DBMS, Stern/NYU, Spring 2005

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

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

  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: name=a&submit=Search M.P. Johnson, DBMS, Stern/NYU, Spring 2005

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

  15. Our use of CGI • We’ll 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 don’t know Perl and PHP M.P. Johnson, DBMS, Stern/NYU, Spring 2005

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

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

  18. Hello, World - hello.pl • Run from browser: • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/hello.pl • What’s wrong? • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/hello.cgi • What’s wrong? • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/hello2.cgi • What’s wrong? M.P. Johnson, DBMS, Stern/NYU, Spring 2005

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

  20. Troubleshooting hello.cgi 5. Make sure you’re printing the HTML header #! /usr/bin/perl -w print "Content-type: text/html\n\n"; print "Hello World\n"; M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  21. Troubleshooting hello.cgi 5. Show errors and warnings: http://pages.stern.nyu.edu/~mjohnson/dbms/perl/hello3.cgi • Is case-sensitive #! /usr/bin/perl -w use CGI qw(:standard); use CGI::Carp qw( fatalsToBrowser warningsToBrowser ); print header(); pr int "Hello World\n"; M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  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 CGI::header(); M.P. Johnson, DBMS, Stern/NYU, Spring 2005

  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/cgipm.pl • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/cgipm.cgi • Somewhat simpler, but another thing to learn • Mostly won’t cover Review: Hello, World M.P. Johnson, DBMS, Stern/NYU, Spring 2005

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

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

  26. Perl and strings • Can compare numbers (as numbers) with usual operators • < > <=, etc. • 3 < 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” M.P. Johnson, DBMS, Stern/NYU, Spring 2005

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

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

  29. Perl syntax examples • Access member/field of object :: • object::member • Access member pointed to by object -> • rowhash->field • Can access array members with indices • Can access hash members with strings • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/controls.cgi • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/controlscgi.txt M.P. Johnson, DBMS, Stern/NYU, Spring 2005

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

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

  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/controls.cgi • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/controlscgi.txt • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/lookup.cgi • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/lookupcgi.txt M.P. Johnson, DBMS, Stern/NYU, Spring 2005

More Related