C20'0046: Database Management Systems Lecture

1
C20.0046 Database Management SystemsLecture 18

• Matthew P. Johnson
• Stern School of Business, NYU
• Spring, 2004

2
Agenda

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

3
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
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?

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?

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
7
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
8
Sketch of sqlplus/mysql
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 /
9
Dynamic SQL example 2

• Example script sample10.pc
• See ProC 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
10
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

11
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
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

13
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 202 (!) JDBC drivers

14
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)
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))
16
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()
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)
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/.ja
va sales javac SQLRunner.java sales java
SQLRunner "select table_name from user_tables"
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/.ja
r sales javac MySQLRunner.java sales java -cp
.mysql.jar MySQLRunner "select from mytab"