C20.0046: Database Management Systems Lecture #18

1. C20.0046: Database Management SystemsLecture #18 Matthew P. Johnson Stern School of Business, NYU Spring, 2004 M.P. Johnson, DBMS, Stern/NYU, Sp2004

2. 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?) M.P. Johnson, DBMS, Stern/NYU, Sp2004

3. Recap: Embedded SQL prog.pc Host language + Embedded SQL Oracle’s Pro*C Preprocessor Preprocessor prog.c Host Language + function calls Host language compiler Host language compiler gcc a.out Executable M.P. Johnson, DBMS, Stern/NYU, Sp2004

4. 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? M.P. Johnson, DBMS, Stern/NYU, Sp2004

5. 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? M.P. Johnson, DBMS, Stern/NYU, Sp2004

6. Dynamic SQL • myquery = a SQL variable • not prefixed by : void someQuery() { EXEC SQL BEGIN DECLARE SECTION; char *command; EXEC SQL END DECLARE SECTION; /* command set to some query string */ EXEC SQL PREPARE myquery FROM :command; EXEC SQL EXECUTE myquery; /* or just: */ EXEC SQL EXECUTE IMMEDIATE myquery; } M.P. Johnson, DBMS, Stern/NYU, Sp2004

7. Dynamic SQL example • Example script: sample8.pc • See Pro*C tutorial • Goal: find employees from department 10 • Start with query as string sales% proc sample8.pc sales% gcc -I/oracle/precomp/public /oracle/lib/libclntsh.so –osample8 sample8.c sales% sample8 M.P. Johnson, DBMS, Stern/NYU, Sp2004

8. Sketch of sqlplus/mysql EXEC SQL BEGIN DECLARE SECTION; char query[MAX QUERY LENGTH]; EXEC SQL END DECLARE SECTION; /* issue SQL> prompt */ /* read user's text into array query */ EXEC SQL EXECUTE IMMEDIATE :query; /* go back to reissue prompt */ M.P. Johnson, DBMS, Stern/NYU, Sp2004

9. Dynamic SQL example 2 • Example script: sample10.pc • See Pro*C tutorial • Goal: recreate sqlplus/mysql • Copy-able commands: http://pages.stern.nyu.edu/~mjohnson/dbms/eg/lec18/proc.txt sales% proc sample10.pc sales% gcc -I/oracle/precomp/public /oracle/lib/libclntsh.so –osample10 sample10.c sales% sample10 M.P. Johnson, DBMS, Stern/NYU, Sp2004

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

11. CLI: Java prog.pc Host language + Embedded SQL Oracle’s Pro*C Preprocessor Preprocessor Prog.java Host Language + function calls Host language compiler Host language compiler javac + jar Proj.class Executable M.P. Johnson, DBMS, Stern/NYU, Sp2004

12. CLI - Overview • Similar to what really happens in embedded SQL implementations. • Major approaches: • SQL/CLI - standard of ODBC • JDBC (Java database connectivity) • See http://cbbrowne.com/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 M.P. Johnson, DBMS, Stern/NYU, Sp2004

13. Next topic: JDBC (Java’s CLI) • As expected: Java too can talk to SQL • In some ways: much nicer • JDBC is an interface • Changes very little • Each vendor writes own plug-in • Dev. Strategy: write to API, compile with jar • See http://servlet.java.sun.com/products/jdbc/drivers for 202 (!) JDBC drivers M.P. Johnson, DBMS, Stern/NYU, Sp2004

14. JDBC • Load JDBC driver for DBMS: • Obtain a connection: Class.forName("oracle.jdbc.driver.OracleDriver") Connection con = DriverManager.getConnection( “jdbc:oracle:thin:@l5.nyu.edu:1521:STN1", username, passwd); M.P. Johnson, DBMS, Stern/NYU, Sp2004

15. JDBC • Obtain a statement object: • Run a query: • Or an update: Statement stmt = con.createStatement(); stmt.executeQuery(“SELECT * FROM table”); stmt.executeUpdate(“INSERT INTO tables” + “VALUES(‘abc’, ‘def’)”); M.P. Johnson, DBMS, Stern/NYU, Sp2004

16. Prepared Statements in JDBC • JDBC also supports prepared statements • Obtain a PreparedStatement object: • Now execute: PreparedStatement ps = con.createStatement( “SELECT * FROM table”); ps.executeQuery(); M.P. Johnson, DBMS, Stern/NYU, Sp2004

17. Obtaining query results • “Cursor” not used, but same idea • executeQuery() return a ResultSet: • rs.next() advances to new row, returns false if EOF • getInt(i) returns ith column (if an int!) from current row ResultSet rs = ps.executeQuery(); while (rs.next()) { String val1 = rs.getString(1); int val2 = rs.getInt(2); … } M.P. Johnson, DBMS, Stern/NYU, Sp2004

18. Java/JDBC/Oracle example • Example program: SQLRunner.java • Goal: run the SQL query passed • Copy-able commands: http://pages.stern.nyu.edu/~mjohnson/dbms/eg/lec18/jdbc.txt sales% cp ~mjohnson/public_html/dbms/eg/lec17/*.java sales% javac SQLRunner.java sales% java SQLRunner "select table_name from user_tables" M.P. Johnson, DBMS, Stern/NYU, Sp2004

19. Java/JDBC/MySQL example • Example program: MySQLRunner.java • Goal: run the SQL query passed • (Nearly) the same as before! • just using different DB/tables/login • mysql.jar is the MySQL J/Connector jar sales% cp ~mjohnson/public_html/dbms/eg/lec17/*.jar sales% javac MySQLRunner.java sales% java -cp .:mysql.jar MySQLRunner "select * from mytab" M.P. Johnson, DBMS, Stern/NYU, Sp2004