OCL1 Oracle 10g: SQL

1
OCL1 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)
EXECT 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 myquery
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

51
Next topic SPs

• Persistent, Stored Modules / Stored Procedures
  / PL/SQL programs (in Oracle)
• Added to MySQL in 5.0
• Another way to connect application programming
  language and SQL
• Supports usual things
• Declare, set vars to vals of expressions
• Print output
• Define (optional) procedures, functions
• Cursors
• PL/SQL can compute n!

52
PL/SQL

• Procedural Language/SQL
• Oracles language for stored procedures
• Simple, interpreted, procedural language
• But Pascal-like
• BEGIN END, not
• AND OR, not
• vars defined at top of procedre
• how return works

53
PL/SQL

• Generally speaking can be used wherever SQL can
  be
• sqlplus
• embeded SQL
• Can store programs in files (.sql), run later
• _at_myprog.sql runs code in myprog.sql

54
Scripting languages

• Big problems v. small problems
• Big solutions v. small solutions
• Programming languages
• C/C, Java, etc.
• Scripting languages
• PL/SQL, Perl, PHP, Unix shell, DOS batch files,
  Python, Excel macros, VBA, JavaScript
• Usual properties of scripting languages
• Interpreted
• Dont require functions/procedures
• Weakly typed

55
PL/SQL

• Structure of procedure body
• As in Pascal, var declars precede body

DECLARE --Optional --var declarations BEGIN --exe
cutable statements --queries/updates,
etc. END / --to execute
56
PL/SQL Hello, World

• http//pages.stern.nyu.edu/mjohnson/dbms/eg/lec19
  /hello.sql

SET SERVEROUTPUT ON BEGIN -- print out
message DBMS_OUTPUT.PUT_LINE('Hello World,
from PL/SQL') END /
57
PL/SQL code examples

• One example
• Likes(drinker, beverage)
• Another example
• http//pages.stern.nyu.edu/mjohnson/dbms/eg/lec19
  /age.sql

BEGIN INSERT INTO Likes VALUES(Izzy',
milk') DELETE FROM Likes WHERE drinker
Izzy' AND beverage Beaujolais Nouveau
' COMMIT END /
58
Procedures

• Stored database objects that use a PL/SQL
  statement(s) in their body
• Create/drop similar to other SQL objects
• ALTER PROCEDURE in MySQL

CREATE PROCEDURE ltmy-procgt (ltparamsgt) ltprocedure
body as abovegt
CREATE OR REPLACE PROCEDURE ltmy-procgt(ltparamsgt) ltp
rocedure body as abovegt
DROP PROCEDURE ltmy-procgt
59
Example procedure

• Define the procedure
• Now we can call it

CREATE PROCEDURE testProcedure BEGIN INSERT INTO
Student VALUES (5, Joe) COMMIT END
EXEC testProcedure
60
More details on procedures

• Parameter list has name-mode-type triples
• Modes IN, OUT, or IN OUT
• Fulfills role similar to pass-by-value v.
  pass-by-reference
• Default is IN
• Types must match, so can get exact field type

relation.attributeTYPE
61
Procedure example

• A procedure to take a beer and price and add it
  to Joe's menu Sells(bar, beer, price)

CREATE PROCEDURE joeMenu( b IN
Sells.beerTYPE, p IN Sells.priceTYPE)
AS BEGIN INSERT INTO Sells VALUES('Joe''s Bar',
b, p) END /
62
Branching

• IFTHEN statements use THEN
• Must end with END IF
• Use ELSIF in place of ELSE IF
• Example
• http//pages.stern.nyu.edu/mjohnson/dbms/eg/lec19
  /maxval.sql

IF ltconditiongt THEN ltstatement(s)gt ELSIF ltstatem
ent(s)gt END IF
63
Loop example
DECLARE     i NUMBER 1 BEGIN     LOOP
        INSERT INTO T1 VALUES(i,i)        
i i1         EXIT WHEN igt100    
END LOOP END /
64
Cursors in PL/SQL

• As expected, PL/SQL has syntax to do the usual
  things
• Declare cursors
• Open and close
• Fetch and eventually leave
• Each can be done manually
• Also has elegant for/cursor loop
• Declare, open, close, fetch all automatic
• Example
• http//pages.stern.nyu.edu/mjohnson/dbms/eg/lec19
  /for.sql

FOR my-rec IN my-cursor LOOP END LOOP
65
Functions

• Like procedures but with return values
• Big strength can be called from SQL

CREATE FUNCTION ltfunctionNamegt (ltparamListgt)
RETURNS type AS ltlocalDeclarationsgt BEGIN ltfunctio
nBodygt END
DROP FUNCTION ltfunctionNamegt
66
Function example

• Like procedures but with return values
• drop in same way
• Big strength can be called from SQL
• http//pages.stern.nyu.edu/mjohnson/dbms/eg/lec19
  /maxval.sql

CREATE OR REPLACE FUNCTION maxval(a IN int, b IN
int) RETURN int AS BEGIN IF a gt b THEN RETURN
a ELSE RETURN b END IF END maxval /
INSERT INTO R VALUES(abc, maxval(5,10))
67
How to run scripts

• Dont want to type ftns into sqlplus by hand
• Define them in a .sql file
• In sqlplus, execute .sql file
• Runs commands in file
• Here, defines function
• Now, we can call functions
• See
• http//pages.stern.nyu.edu/mjohnson/dbms/eg/lec19
  /plsql.txt

SQLgt _at_maxval.sql
SQLgt exec DBMS_OUTPUT.PUT_LINE (maxval(5,10))
68
Triggers in Oracle

• Oracle triggers are written in PL/SQL
• Trigger body is like regular procedure body, but
  following trigger syntax

CREATE OR REPLACE TRIGGER MYTRIG1 BEFORE DELETE
ON mytable BEGIN --code END
69
Look up procedures, functions

• In Oracle, functions procedures in
  user_procedures

SELECT object_name from user_procedures
70
More on PL/SQL

• OReillys Oracle PL/SQL Programming
• http//www.unix.org.ua/orelly/oracle/prog2/
• PL/SQL Reference Tutorial
• http//www.ilook.fsnet.co.uk/ora_sql/sqlmain2.htm
• Introduction to PL/SQL
• http//www.geocities.com/cliktoprogram/plsql/intro
  duction.html
• Oracle FAQ's Script and Code Exchange
• http//www.orafaq.com/scripts/

71
Triggers

• Constraints state what must remain true
• DBMS decides when to check
• Triggers are instructions to perform at
  explicitly specified times
• Three aspects
• An event (e.g., update to an attribute)
• A condition (e.g., a query to check)
• An action (the triggers effect) (deletion,
  update, insertion)
• When the event occurs, DBMS checks the
  constraint, and if it is satisfied, performs the
  action

72
Triggers important points

• Can replace old row (result of event) with new
  row
• Action may be performed before or after event
• Can refer to old row and new row
• WHEN clauses tests whether to continue
• Action may be performed either
• For each row involved in event
• Once per event
• Oracle does triggers as PL/SQL programs

73
Elements of Triggers

• Timing of action execution before, after or
  instead of triggering event
• The action can refer to both the old and new
  state of the database
• Update events may specify a particular column or
  set of columns
• A condition is specified with an optional WHEN
  clause
• The action can be performed either for
• once for every tuple or
• once for all the tuples that are changed by the
  database operation

74
Simple trigger example

• R(id, data, last-modified)
• data is a large string
• Last-modified is a newly added date field
• Goal whenever data is modified, update
  last-modified date
• Could modify all scripts/programs that touch this
  table
• Bad idea
• Better user a trigger

• CREATE TRIGGER UpdateDateTrigger
• BEFORE UPDATE OF data ON R
• REFERENCING
• NEW ROW AS NewTuple
• FOR EACH STATEMENT
• BEGIN
• NewTuple.last-modified sysdate
• END

75
Triggers Row-level example

• MovieExec(name, address, cert, netWorth)
• If someone decreases a movie executives net
  worth, I want the database to reset itself to the
  previous net worth.

CREATE TRIGGER NetWorthTrigger AFTER UPDATE OF
netWorth ON MovieExec REFERENCING NEW ROW AS
NewTuple OLD ROW AS OldTuple FOR EACH ROW WHEN
(OldTuple.netWorthgtNewTuple.netWorth) UPDATE
MovieExec SET netWorth oldTuple.netWorth WHERE
cert newTuple.cert)
76
Triggers Table-level example

• MovieExec(name, address, cert, netWorth)
• If someone updates the net worth of one movie
  exec so that the average net worth of all movie
  execs becomes less than 50,000, I want the
  database to reset itself.

CREATE TRIGGER AvgNetWorthTrigger AFTER UPDATE OF
netWorth ON MovieExec REFERENCING OLD TABLE AS
OldStuff, NEW TABLE AS NewStuff FOR EACH
STATEMENT WHEN (50000 gt (SELECT AVG(netWorth)
FROM MovieExec)) BEGIN DELETE FROM
MovieExec WHERE (Name, address, cert, netWorth)
IN NewStuff INSERT INTO MovieExec (SELECT
FROM OldStuff) END
77
Perl and databases

• DB connectivity is done through DBI
• Database Interface
• Analogous to Javas JDBC
• Think of DBI as a Java class with static methods
• Use these to obtain a connection, prepare and
  execute queries, etc.

78
Perl DBI

• Open a connection
• Prepare and execute query

my dbh DBI-gt connect("dbimysqldatabasetestm
ysql2.stern.nyu.eduport3306", user, pass)
my sth dbh-gtprepare(query) sth-gtexecute
79
Perl DBI

• Extract next row of data from statement results,
  if available
• What this means row has two fields, whose values
  are put in a and b, in order
• Other options, but this should suffice
• In general, want to scroll through results
• Braces are required!

my (a, b) sth-gtfetchrow_array()
while (my (a, b) sth-gtfetchrow_array())
print out a and b
80
Limit Perl webpages that do something

• Semi-interesting Perl script
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/loo
  kup.cgi
• Non-trivial but not huge 40 lines
• Works with two-column (a,b) table
• Takes input from user
• Returns rows whose a field contains value
• If no/empty input, returns all rows
• Bad idea in general!

81
lookup.cgi

• Two possible situations for running script
• Page opened for the first time
• User entered parameter and pressed button
• Structure of file
• Print input box and button for next search
• On button click, parameter is sent to this pages
  url
• (Try to) read input parameter
• Open MySQL connection
• Run query
• Print results in a table
• Disconnect from MySQL

82
Higher-level structure

• As one page
• If we have params, display data based on them
• Otherwise, prompt user for params, call self
• Could be
• Page 1 prompt for params, call page 2
• Page 2 display data based on params
• In e.g. always display data for convenience

83
Tutorials on Perl

• Some material drawn from the following good
  tutorials
• http//perl.com
• CGI backend programming using perl
• http//www.scit.wlv.ac.uk/jphb/sst/perl/
• Perl Basics
• http//www.cs.wcupa.edu/rkline/csc417/perl-basics
  -1.html
• CGI Basics
• http//www.cs.wcupa.edu/rkline/csc417/cgi-basics-
  1.html
• MySQL/Perl/CGI example
• http//www.scit.wlv.ac.uk/jphb/sst/perl/ex3d.html
  

84
PHP MySQL

• Open a connection and open our DB
• Run query

db mysql_connect("mysql2.stern.nyu.edu3306",
user, pass) mysql_select_db("test", db)
result mysql_query(query,db)
85
PHP MySQL

• Extract next row of data from statement, if
  available
• What this means myrow is an array that can then
  be accessed
• Other options, but this should suffice
• In general, want to scroll through results

myrow mysql_fetch_row(result)
while (myrow mysql_fetch_row(result))
print rows data
86
Limit PHP webpages that do something

• Semi-interesting Perl script
• http//pages.stern.nyu.edu/mjohnson/dbms/php/look
  up.php
• Non-trivial but not huge 60 lines, but much
  plain html
• Works with two-column (a,b) table
• Takes input from user
• Returns rows whose a field contains value
• If no/empty input, returns all rows
• Bad idea in general!

87
lookup.php port of lookup.cgi

• Two possible situations for running script
• Page opened for the first time
• User entered parameter and pressed button
• Structure of file
• Print input box and button for next search
• On button click, parameter is sent to this pages
  url
• (Try to) read input parameter
• Open MySQL connection
• Run query
• Print results in a table
• Disconnect from MySQL

88
Insert/delete Perl/PHP example

• Similar to search example
• NB form has two buttons
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/upd
  ate.cgi
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/upd
  atecgi.txt
• http//pages.stern.nyu.edu/mjohnson/dbms/perl/upd
  ate.cgi
• http//pages.stern.nyu.edu/mjohnson/dbms/php/upda
  tephp.txt

89
Master-detail Perl/PHP example

• Idea display list of regions
• When region clicked on, display its countries
• Mechanism pass GET param in link, not with a
  FORM
• http//pages.stern.nyu.edu/mjohnson/websys/cia.pl
  
• http//pages.stern.nyu.edu/mjohnson/websys/cia.ph
  p.txt

90
Tutorials on PHP

• Some material drawn from the following good
  tutorials
• http//php.net
• PHP introduction and examples
• http//www.scit.wlv.ac.uk/jphb/sst/php/
• Interactive PHP with database access
• http//www.scit.wlv.ac.uk/jphb/sst/php/gazdb.html
  
• Longer PHP/MySQL Tutorial from webmonkey
• http//hotwired.lycos.com/webmonkey/99/21/index2a.
  html
• Nice insert/update/delete example from webmonkey
• http//hotwired.lycos.com/webmonkey/99/21/index3a.
  html
• MySQL/Perl/PHP page from U-Wash
• http//www.washington.edu/computing/web/publishing
  /mysql-script.html