OCL2 Oracle 10g: SQL

1
OCL2 Oracle 10gSQL PL/SQLSession 6

• Matthew P. Johnson
• CISDD, CUNY
• Fall, 2004

2
Agenda

• Last time Views, Constraints
• This time
• Begin programming for SQL
• Embedded SQL
• ProC, SQLJ
• CLI
• SQL/CLI in C
• JDBC in Java
• DBI/DBDs in Perl
• PSMs
• Future
• PL/SQL, Triggers

3
Todays agenda

• Go through Join exercises
• Discuss ProC
• First part of lab
• Break
• Discuss CLI, Embedded SQL, JDBC
• Second part of lab

4
New topic SQL Programming

• Can write SQL queries in a SQL interpreter
• Command prompt
• SQLPlus (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?
• We can quickly dispense with the idea
  (Ullman, p351)
• Q Why not do everything in the host lang.?
• A What Oracle 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!), not Turing-complete
• 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.
  dont 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/assoc.

7
SQL/host interface in embedded SQL

• So Q how to transfer data between?
• A Shared variables
• Some vars in the 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 (e.g.) a C program with library calls

8
Programs with Embedded SQL
prog.pc
Host language Embedded SQL
Oracles ProC
Preprocessor
Preprocessor
prog.c
Host Language function calls
Host language compiler
Host language compiler
gcc
a.out
Executable
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
productName30 char SQLSTATE6 EXEC SQL END
DECLARE SECTION
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 insert
void simpleInsert() EXEC SQL BEGIN DECLARE
SECTION char pn20, cn30 / product-name,
company-name / double p, int q / price,
quantity / char SQLSTATE6 EXEC SQL END
DECLARE SECTION / get values for name,
price and company somehow / EXEC SQL INSERT
INTO Product(pname, price, quantity,
maker) VALUES (pn, p, q, cn)
12
Embedded SQL example look-up
int getWindowsPrice() EXEC SQL BEGIN DECLARE
SECTION double p
char SQLSTATE6 EXEC SQL END DECLARE
SECTION EXEC SQL SELECT price INTO
p FROM Product WHERE 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 BEGIN
DECLARE SECTION int p
char SQLSTATE6 EXEC SQL END DECLARE
SECTION EXEC SQL SELECT price INTO
p FROM Product WHERE Product.name name
return p
14
Embedded SQL example look-up
int getPrice(char name) EXEC SQL BEGIN
DECLARE SECTION char n20
int p char SQLSTATE6
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
15
Cursors

• For products 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

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))
17
Multiple-row look-up example
void productToXML() EXEC SQL BEGIN DECLARE
SECTION char pn20, cn30 double p int
q char SQLSTATE6 EXEC SQL END DECLARE
SECTION EXEC SQL DECLARE crs CURSOR FOR
SELECT pname, price, quantity, maker FROM
Product EXEC SQL OPEN crs ...
18
Multiple look-up example
printf(ltallProductsgt\n) while (1) EXEC
SQL FETCH FROM crs INTO n, p, q,c if
(NO_MORE_TUPLES) break printf(ltproductgt\n)
printf( ltnamegtslt/namegt\n, n) printf(
ltpricegtdlt/pricegt\n, p) printf(
ltquantitygtdlt/quantitygt\n, q) printf(
ltmakergtslt/makergt\n, c) printf(lt/productgt\n)
EXEC SQL CLOSE crs printf(lt/allProductsgt\
n)
19
More on Cursors

• Cursors can traverse both stored tables and
  queries
• 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)

20
Cursor on query not table
EXEC SQL DECLARE c CURSOR FOR SELECT beer,
price FROM Sells WHERE bar Izzy''s' 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
21
Modifications with cursors

• As we traverse through result set, can modify the
  current row
• Can also modify with arb. WHERE clauses
• NB In regular SQL, usually modify sets of rows
  (UPDATE WHERE )
• With cursors, we update the last row fetched
• Simple example in RentStab table, we decide we
  want
• to raise (e.g., by 5) all our prices
• Unless price gt 2000, in which case theyre deleted

22
Modification by cursor example
void raisePrices() EXEC SQL BEGIN DECLARE
SECTION double p char SQLSTATE6 EXEC
SQL END DECLARE SECTION EXEC SQL DECLARE crs
CURSOR FOR SELECT price FROM RentStab EXEC
SQL OPEN crs ...
23
Modification by cursor example
while (1) EXEC SQL FETCH FROM crs INTO
p if (NO_MORE_TUPLES) break if (p lt
2000) EXEC SQL UPDATE RentStab SET
price 1.05price WHERE CURRENT OF
RentStab else EXEC SQL DELETE FROM
RentStab WHERE CURRENT OF
RentStab EXECT SQL CLOSE crs
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 were
  fetching
• Should we see the changes? Maybe, maybe not
• make these changes invisible by declaring
  insensitive
• Q How can this be implemented?
• One crude way delay any changes until all open
  insensitive cursors close
• Good idea indicate read-only cursors so they
  wont 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
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
26
ProC e.g.

• Example script sample1.pc
• See ProC tutorial
• ProC compiler is proc
• Must include /oracle/precomp/public
• Must link with shared library /oracle/lib/libclnts
  h.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

27
Interface SQL/Host Language

• Two languages SQL, host (C/Java/whatever)
• Benefits
• DB code (SQL is portable)
• SQL, host language focus on own strengths
• SQL code placed in host language code
• SQL and host language have diff. data types
• impedance mismatch
• Data transferred with shared variables
• Use cursors to access/modify data
• Error messages placed in SQLSTATE

28
Agenda

• Previously ProC
• Next
• Project part 3 is due next week
• More programming for SQL
• Embedded SQL
• ProC, SQLJ
• CLI
• SQL/CLI in C
• JDBC in Java
• DBI/DBDs in Perl
• PHP (HTML?)
• SPs

29
Recap Embedded SQL
prog.pc
Host language Embedded SQL
Oracles ProC
Preprocessor
Preprocessor
prog.c
Host Language function calls
Host language compiler
Host language compiler
gcc
a.out
Executable
30
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?

31
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?)

32
Dynamic SQL

• myquery a SQL variable
• not prefixed by

void runQuery() 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 command
33
Dynamic SQL example

• Example script sample8.pc
• See ProC 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
34
Sketch of sqlplus

• Something like the sqlplus program could be
  written as a simple dynamic SQL
• future lab idea?

EXEC SQL BEGIN DECLARE SECTION char queryMAX
QUERY LENGTH EXEC SQL END DECLARE SECTION /
issue SQLgt prompt / / read user's text into
array query / EXEC SQL EXECUTE IMMEDIATE
query / go back to reissue prompt /
35
Dynamic SQL example 2

• Example script sample10.pc
• See ProC tutorial
• Goal recreate sqlplus
• 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
36
Next topic SQL/CLI

• ProC 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
• Wont cover in depth

37
CLI Java
prog.pc
Host language Embedded SQL
Oracles ProC
Preprocessor
Preprocessor
Prog.java
Host Language function calls
Host language compiler
Host language compiler
javac jar
Proj.class
Executable
38
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

39
Next topic JDBC (Javas 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/driv
  ers for 219 (!) JDBC drivers

40
JDBC

• Load JDBC driver for DBMS
• Obtain a connection

Class.forName("oracle.jdbc.driver.OracleDriver")
Connection con DriverManager.getConnection( jd
bcoraclethin_at_l5.nyu.edu1521STN1", username,
passwd)
41
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))
42
Prepared Statements in JDBC

• JDBC also supports prepared statements
• Obtain a PreparedStatement object
• Now execute

PreparedStatement ps con.createStatement( SELE
CT FROM table)
ps.executeQuery()
43
Obtaining query results

• Cursor not used, but same idea
• executeQuery() returns 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)
44
Java/JDBC/Oracle example

• Example program SQLRunner.java
• Goal run the SQL query passed
• Copy-able commands
• http//pages.stern.nyu.edu/mjohnson/oracle/sqlrun
  instr.txt

C\procgtc\j2sdk1.4.2_04\bin\javac
SQLRunner.java C\procgtc\j2sdk1.4.2_04\bin\java
-cp .C\OraHome_1\jdbc\lib\ojdbc14.jar
SQLRunner "select from emp"
45
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

C\procgtc\j2sdk1.4.2_04\bin\javac
MySQLRunner.java C\procgtc\j2sdk1.4.2_04\bin\java
cp .mysql.jar MySQLRunner "select from emp"
46
Recap JDBC
prog.pc
Host language Embedded SQL
Oracles ProC
Preprocessor
Preprocessor
Prog.java
Host Language function calls
Host language compiler
Host language compiler
javac jar
Proj.class
Executable
47
Java parameter-based SQL

• Like SQL/CLI in C, Java also supports
  parameterized queries (why?)
• Prepare structure of query
• Then can set values

PreparedStatement ps conn.prepareStatement(
"SELECT FROM table WHERE f1 ? and f2
?") ps.setString(1 abc") ps.setString(2,
def") ResultSet rs ps.executeQuery() ...
48
Also ODBC

• Used by Microsoft platforms/tools, others
• Access
• Start Control Panel Administrative Tools
  Data Sources (ODBC)
• Similar to JDBC
• Wont cover

49
Other combinations

• So far C/ProC, Java/JDBC
• Q Only choices?
• A No
• Call-level interface for C SQL/CLI
• ODBC
• Embedded Java SQL/J
• CLI for Perl, PHP, etc.
• Stored Procedures (next)
• langs x dyn/not x SPs/not x DBMSs

50
Step back

• Recall basic problem need SQL plus stronger
  programming lang
• ? need to connect the two langs
• In all these cases (and in the web app case),
  idea is put SQL in (traditional-lang) programs
• Another way put programs in SQL
• i.e., store programs on the DBMS
• stored procedures