c20.0046: database management systems lecture #15

M.P. Johnson, DBMS, Stern/NYU, Sp2004

1

C20.0046: Database Management SystemsLecture #15

Matthew P. Johnson

Stern School of Business, NYU

Spring, 2004


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


3

Future Next: PSMs

(Re)read 8.2 Next Thursday: Midterm

Practice SQL on sqlzoo Responsible (in this order) for

1. all material from class,

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


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


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


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


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


8

Programs with Embedded SQL

Host language + Embedded SQL

Preprocessor

Host Language + function calls

Host language compiler

Executable

Preprocessor

Host language compiler

Oracle’sPro*C

gcc

prog.pc

prog.c

a.out


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;

EXEC SQL BEGIN DECLARE SECTION;char productName[30];char SQLSTATE[6];



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


11

Embedded SQL example: insertvoid 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); }

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); }


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 :pFROM ProductWHERE Product.name = ‘Windows’;

return p;}

int getWindowsPrice() {


EXEC SQL SELECT price INTO :pFROM ProductWHERE Product.name = ‘Windows’;

return p;}


13

Embedded SQL example: look-up What about search for arbitrary product?

Q: Will this work?

int getPrice(char *name) {


EXEC SQL SELECT price INTO :pFROM ProductWHERE Product.name = :name;

return p;}



EXEC SQL SELECT price INTO :pFROM ProductWHERE Product.name = :name;

return p;}


14

Embedded SQL example: look-upint 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 :pFROM ProductWHERE Product.name = :n;

return p;}


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 :pFROM ProductWHERE Product.name = :n;

return p;}


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:

1. Declare the cursor2. Open the cursor3. Fetch tuples one by one4. Close the cursor


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?

#define NO_MORE_TUPLES !(strcmp(SQLSTATE,”02000”))#define NO_MORE_TUPLES !(strcmp(SQLSTATE,”02000”))

if(NO_MORE_TUPLES) break;if(NO_MORE_TUPLES) break;


17

Multiple-row look-up examplevoid product2XML() {

EXEC SQL BEGIN DECLARE SECTION;char n[20], c[30];int p, q;char SQLSTATE[6];


EXEC SQL DECLARE crs CURSOR FOR

SELECT pname, price, quantity, maker

FROM Product;

EXEC SQL OPEN crs;

...

void product2XML() {

EXEC SQL BEGIN DECLARE SECTION;char n[20], c[30];int p, q;char SQLSTATE[6];



SELECT pname, price, quantity, maker

FROM Product;

EXEC SQL OPEN crs;

...


18

Multiple look-up exampleprintf(“<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”);}

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”);

}


printf(“</allProducts>\n”);}


19

Cursor on query not table

EXEC SQL DECLARE c CURSOR FORSELECT beer, priceFROM SellsWHERE bar = 'Joe''s Bar';EXEC SQL OPEN CURSOR c;while(1) {

EXEC SQL FETCH cINTO :theBeer, :thePrice;if(NOT FOUND) break;/* format and print beer and price */

}EXEC SQL CLOSE CURSOR c;

EXEC SQL DECLARE c CURSOR FORSELECT beer, priceFROM SellsWHERE bar = 'Joe''s Bar';EXEC SQL OPEN CURSOR c;while(1) {

EXEC SQL FETCH cINTO :theBeer, :thePrice;if(NOT FOUND) break;/* format and print beer and price */

}EXEC SQL CLOSE CURSOR c;


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


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


22

Modification by cursor examplevoid doublePrices() {

EXEC SQL BEGIN DECLARE SECTION;int p;char SQLSTATE[6];



SELECT price

FROM Product;

EXEC SQL OPEN crs;...

void doublePrices() {

EXEC SQL BEGIN DECLARE SECTION;int p;char SQLSTATE[6];



SELECT price

FROM Product;

EXEC SQL OPEN crs;...


23

Modification by cursor examplewhile (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;


}


}

while (1) {

EXEC SQL FETCH FROM crs INTO :p;

if (NO_MORE_TUPLES) break;

if (p < 100)

EXEC SQL DELETE FROM Product


else

EXEC SQL UPDATE Product

SET price = 2*price;


}


}


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 INSENSITIVE CURSOR FOR




FOR READ ONLY;



FOR READ ONLY;


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;

EXEC SQL DECLARE crs SCROLL CURSOR FOR

Product;


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.pcsales% gcc -osample1 -I/oracle/precomp/public

/oracle/lib/libclntsh.so sample1.csales% sample1

sales% cp /oracle/precomp/demo/proc/sample1.pc .sales% proc sample1.pcsales% gcc -osample1 -I/oracle/precomp/public

/oracle/lib/libclntsh.so sample1.csales% sample1


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

c20.0046: database management systems lecture #15

Documents