C20.0046 Database Management SystemsLecture #18.ppt

M.P. Johnson, DBMS, Stern/NYU, Sp2004 C20.0046: Database Management SystemsLecture #18 Matthew P. Johnson Stern School of Business, NYU Spring, 2004 Agenda Previously: Pro*C Next: Project part 3 is due next week More programming for SQL Embedded SQL Pro*C, SQLJ PSMs CLI SQL/CLI in C JDBC in Java DBI/DBDs in Perl PHP (HTML?) Recap: Embedded SQL Limitation of embedded SQL Okay for apps with a fixed set of queries/updates Maybe very simple kiosks But consider, say, sqlplus or the sqlzoo website Processes arbitrary queries from user Can we do this with embedded SQL? Dynamic SQL In dynamic SQL, query string can be taken as a parameter, passed to DB Two steps: Prepare: compiles/optimizes the string Execute: executes the query Combine together: EXECUTE IMMEDIATE But separate if query is executed many times (why?) Something like the sqlplus program could be written as a simple dynamic SQL future homework idea? Dynamic SQL myquery = a SQL variable not prefixed by : Dynamic SQL example Example script: sample8.pc See Pro*C tutorial Goal: find employees from department 10 Start with query as string Sketch of sqlplus/mysql Dynamic SQL example 2 Example script: sample10.pc See Pro*C tutorial Goal: recreate sqlplus/mysql Copy-able commands: /~mjohnson/dbms/eg/lec18/proc.txt Next topic: SQL/CLI Pro*C converts EXEC SQL code --into what? If we know the API (“Call-Level Interface”), can call library routines by hand Is this better or worse? Pros cons Won’t cover in depth CLI: Java CLI - Overview Similar to what really happens in embedded SQL implementations. Major approaches: SQL/CLI - standard of ODBC JDBC (Java database connectivity) See /info/middleware.html for many options Advantages over embedded SQL: Avoid preprocessor-stage, easier to debug In th., use same program with several DBMS Disadvantages: Must keep up to date with API changes DBMS may have conflicting APIs Next topic: JDBC (Java’s CLI) As expected: Java too can talk to SQL In some ways: much nicer