C20.0046: Database Management Systems Lecture #15

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

2. Agenda • Last time: Views, Constraints, Triggers • This time: • Begin programming for SQL • Embedded SQL • Pro*C, SQLJ • PSMs • CLI • SQL/CLI in C • JDBC in Java • DBI/DBDs in Perl • Future: • Midterm on Thursday M.P. Johnson, DBMS, Stern/NYU, Sp2004

3. Future • Next: PSMs • (Re)read 8.2 • Next Thursday: Midterm • Practice SQL on sqlzoo • Responsible (in this order) for • all material from class, • all material assigned reading • Feedback is very important! • Please fill out midterm course evals before MT • Else lose 30 points on MT • Details TBA by email over week M.P. Johnson, DBMS, Stern/NYU, Sp2004

4. New topic: SQL Programming (8.1) • Can write SQL queries in a SQL interpreter • Command prompt • SQL*Plus (sqlplus) in Oracle • mysql in MySQL • Good for experimenting, not for anything non-trivial • Better: use a standard programming language • Host language talks to SQL/DB M.P. Johnson, DBMS, Stern/NYU, Sp2004

5. Using two languages • Q: Why not use just one language? • “[W]e can quickly dispense with the idea…” (p351) • Q: Why not do everything in the host lang.? • A: What SQL provides is highly non-trivial • Query interpretation, optimizing • Queries stay constant across host languages • Q: Why not do everything in SQL? • A: Not designed as a general-purpose language • No recursion (no factorial!) • No, e.g., Swing library • Germ of OO: modularize M.P. Johnson, DBMS, Stern/NYU, Sp2004

6. Impedance mismatch problem • Big problem, though: impedance mismatch • Data structures in our app-programming lang. don’t automatically map onto those in SQL • Different types/representations for data • In SQL: tables with scalar fields • In C: scalars, records (containing records…), pointers, arrays • In Java: scalars, objects, references, arrays • In Perl: scalars, lists/arrays, hashes M.P. Johnson, DBMS, Stern/NYU, Sp2004

7. SQL/host interface in embedded SQL • So Q: how to transfer data between? • A: Shared variables • Some vars in are program can be used by SQL • Prefix var with a : • After query, look here for received data • SQL commands embedded in app. Code • Identified by EXEC SQL • Source code is preprocessed before regular compilation • Result is a C (e.g.) program with library calls M.P. Johnson, DBMS, Stern/NYU, Sp2004

8. Programs with 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

9. Interface: SQL / Host Language • Values get passed through shared variables. • Colons precede shared variables in SQL statements • EXEC SQL demarcates every SQL statement • The variable SQLSTATE provides error messages and status reports • “00000” ~ success • “02000” ~ tuple not found • Used in loops EXEC SQL BEGIN DECLARE SECTION; char productName[30]; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; M.P. Johnson, DBMS, Stern/NYU, Sp2004

10. Embedded SQL example • Context: • Product (pname, price, quantity, maker) • Purchase (buyer, seller, store, pname) • Company (cname, city) • Person(name, phone, city) • Goal 1: Insert a new row in Purchase • Goal 2: Look up price of product by name M.P. Johnson, DBMS, Stern/NYU, Sp2004

11. Embedded SQL example: insert void simpleInsert() { EXEC SQL BEGIN DECLARE SECTION; char n[20], c[30]; /* product-name, company-name */ int p, q; /* price, quantity */ char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; /* get values for name, price and company somehow */ EXEC SQL INSERT INTO Product(pname, price, quantity, maker) VALUES (:n, :p, :q, :c); } M.P. Johnson, DBMS, Stern/NYU, Sp2004

12. Embedded SQL example: look-up int getWindowsPrice() { EXEC SQL BEGIN DECLARE SECTION; int p; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; EXEC SQL SELECT price INTO :p FROM Product WHERE Product.name = ‘Windows’; return p; } M.P. Johnson, DBMS, Stern/NYU, Sp2004

13. Embedded SQL example: look-up • What about search for arbitrary product? • Q: Will this work? int getPrice(char *name) { EXEC SQL BEGIN DECLARE SECTION; int p; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; EXEC SQL SELECT price INTO :p FROM Product WHERE Product.name = :name; return p; } M.P. Johnson, DBMS, Stern/NYU, Sp2004

14. Embedded SQL example: look-up int getPrice(char *name) { EXEC SQL BEGIN DECLARE SECTION; char n[20]; int p; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; strcpy(n, name); /* copy name to local var */ EXEC SQL SELECT price INTO :p FROM Product WHERE Product.name = :n; return p; } M.P. Johnson, DBMS, Stern/NYU, Sp2004

15. Cursors • For product’s price, looked up single (scalar) value • Q: What if we SELECT multiple fields? • E.g., find all info for some product • A: Just list destination vars separated by commas • Q: What if find multiple rows? • E.g., find all products above a certain price • Use a cursor to step through the results • Each result placed in an array • Using cursors: • Declare the cursor • Open the cursor • Fetch tuples one by one • Close the cursor M.P. Johnson, DBMS, Stern/NYU, Sp2004

16. Cursor loop structure • Each time around loop, we • Do a FETCH to obtain next row • Examine SQLSTATE to check success • Can say: • What is NO_MORE_TUPLES? if(NO_MORE_TUPLES) break; #define NO_MORE_TUPLES !(strcmp(SQLSTATE,”02000”)) M.P. Johnson, DBMS, Stern/NYU, Sp2004

17. Multiple-row look-up example void product2XML() { EXEC SQL BEGIN DECLARE SECTION; char n[20], c[30]; int p, q; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE crs CURSOR FOR SELECT pname, price, quantity, maker FROM Product; EXEC SQL OPEN crs; ... M.P. Johnson, DBMS, Stern/NYU, Sp2004

18. Multiple look-up example printf(“<allProducts>\n”); while (1) { EXEC SQL FETCH FROM crs INTO :n, :p, :q,:c; if (NO_MORE_TUPLES) break; printf(“<product>\n”); printf(“ <name>%s</name>\n”, n); printf(“ <price>%d</price>\n”, p); printf(“ <quantity>%d</quantity>\n”, q); printf(“ <maker>%s</maker>\n”, c); printf(“</product>\n”); } EXECT SQL CLOSE crs; printf(“</allProducts>\n”); } M.P. Johnson, DBMS, Stern/NYU, Sp2004

19. Cursor on query not table EXEC SQL DECLARE c CURSOR FOR SELECT beer, price FROM Sells WHERE bar = 'Joe''s Bar'; EXEC SQL OPEN CURSOR c; while(1) { EXEC SQL FETCH c INTO :theBeer, :thePrice; if(NOT FOUND) break; /* format and print beer and price */ } EXEC SQL CLOSE CURSOR c; M.P. Johnson, DBMS, Stern/NYU, Sp2004

20. More on Cursors • Cursors can modify a relation as well as read it. • Cursors can be protected against changes to the underlying relations • Can determine the order in which the cursor will get tuples by the ORDER BY keyword in the SQL query • The cursor can be a scrolling one: can go forward, backward • +n, -n, Abs(n), Abs(-n) • Cursors can traverse both stored tables and queries M.P. Johnson, DBMS, Stern/NYU, Sp2004

21. Modifications with cursors • As we traverse through result set, can modify the current row • NB: In regular SQL, usually modify sets of rows (UPDATE WHERE …) • With cursors, we update the last row fetched • Simple example: in Product table, we decide we want • to raise (i.e., double) all our prices • Unless price < 100, in which case they’re deleted M.P. Johnson, DBMS, Stern/NYU, Sp2004

22. Modification by cursor example void doublePrices() { EXEC SQL BEGIN DECLARE SECTION; int p; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE crs CURSOR FOR SELECT price FROM Product; EXEC SQL OPEN crs; ... M.P. Johnson, DBMS, Stern/NYU, Sp2004

23. Modification by cursor example while (1) { EXEC SQL FETCH FROM crs INTO :p; if (NO_MORE_TUPLES) break; if (p < 100) EXEC SQL DELETE FROM Product WHERE CURRENT OF Product; else EXEC SQL UPDATE Product SET price = 2*price; WHERE CURRENT OF Product; } EXECT SQL CLOSE crs; } M.P. Johnson, DBMS, Stern/NYU, Sp2004

24. A mention of concurrent access • What if the DB changes while our cursor is looping? • I.e., after we opened the cursor, but while we’re fetching • Should we see the changes? Maybe, maybe not • make these changes invisible by declaring insensitive • Q: How is this accomplished? • One crude way: delay any changes until all insensitive cursors close • Good idea: indicate read-only cursors so they won’t be held up: EXEC SQL DECLARE crs INSENSITIVE CURSOR FOR SELECT price FROM Product; EXEC SQL DECLARE crs CURSOR FOR SELECT price FROM Product; FOR READ ONLY; M.P. Johnson, DBMS, Stern/NYU, Sp2004

25. Scrolling cursors • Usually cursor just walks through rows 1 by 1 • Other options: • NEXT (default) or PREVIOUS • FIRST or LAST • RELATIVE +/-n • RELATIVE 1 ~ NEXT • RELATIVE –1 ~ ? • ABSOLUTE +/-n • ABSOLUTE 1 ~ FIRST (not 0!) • ABSOLUTE –1 ~ LAST • To use these, declare as SCROLL cursor EXEC SQL DECLARE crs SCROLL CURSOR FOR Product; M.P. Johnson, DBMS, Stern/NYU, Sp2004

26. Pro*C on sales • Example script: sample1.pc • See Pro*C tutorial • Pro*C compiler is proc • Must include/oracle/precomp/public • Must link with shared library/oracle/lib/libclntsh.so • Includes makefile proc.mk, but may require modifications sales% cp /oracle/precomp/demo/proc/sample1.pc . sales% proc sample1.pc sales% gcc -osample1 -I/oracle/precomp/public /oracle/lib/libclntsh.so sample1.c sales% sample1 M.P. Johnson, DBMS, Stern/NYU, Sp2004

27. Indeterminate-speed review • Look at integrity violations in http://sqlzoo.net/howto/x04insertoracle.htm • Go over some R.A./SQL questions from hw… • SQL questions located at http://pages.stern.nyu.edu/~mjohnson/dbms/hw2sqlquestions.txt • Some “solutions” available at http://pages.stern.nyu.edu/~mjohnson/dbms/hw2solns.txt • Test by running at http://sqlzoo.net/1a.htm M.P. Johnson, DBMS, Stern/NYU, Sp2004