to compile an applica<on without the need for a DBMS-‐specific pre-‐compiler ? • Use a library of database calls – Standardized (non-‐DBMS-‐specific) API – Pass SQL-‐strings from host language and presents result sets in a language friendly way – Eg. ODBC for C/C++ and JDBC for Java – DBMS-‐neutral • A driver traps the calls and translates them into DBMS-‐specific code Lipyeow Lim -‐-‐ University of Hawaii at Manoa 2 Applica<on DBMS-‐specific Driver ODBC/JDBC API DBMS network
– Submits SQL statements – Terminates connec<ons • Driver Manager – Loads the right JDBC driver • Driver – Connects to the data source, – Transmit requests, – Returns results and error codes • Data Source – DBMS Lipyeow Lim -‐-‐ University of Hawaii at Manoa 3 Applica<on Driver Manager Driver Data Source
– Translate SQL commands to non-‐na<ve API – eg. JDBC-‐ODBC bridge. JDBC is translated to ODBC to access an ODBC compliant data source. • Type II: Direct Transla<on to na<ve API via non-‐Java driver – Translates SQL to na<ve API of data source. – Needs DBMS-‐specific library on each client. • Type III: Network bridge – SQL stmts sent to a middleware server that talks to the data source. Hence small JDBC driver at each client • Type IV: Direct Transla<on to na<ve API via Java driver – Converts JDBC calls to network protocol used by DBMS. – Needs DBMS-‐specific Java driver at each client. Lipyeow Lim -‐-‐ University of Hawaii at Manoa 4
2. Connect to the data source 3. [op<onal] Prepare the SQL statements 4. Execute the SQL statements 5. Iterate over the resultset 6. Close the connec<on Lipyeow Lim -‐-‐ University of Hawaii at Manoa 5
shared variables • Variables in host language is bound to columns of a SQL cursor • ODBC – SQLBindCol – gets data from SQL environment to host variables. – SQLBindParameter – gets data from host variables to SQL environment • JDBC – ResultSet class – PreparedStatement class Lipyeow Lim -‐-‐ University of Hawaii at Manoa 6
statement – Afer DBMS receives SQL statement, • The SQL is compiled, • An execu<on plan is chosen by the op<mizer, • The execu<on plan is evaluated by the DBMS engine • The results are returned • conn.prepareStatement – Compiles and picks an execu<on plan • pstmt.executeUpdate – Evaluates the execu<on plan with the parameters and gets the results Lipyeow Lim -‐-‐ University of Hawaii at Manoa 7 String sql=“SELECT * FROM books WHERE price < ?”; PreparedStatement pstmt = conn.prepareStatement(sql); Pstmt.setFloat(1, usermaxprice); Pstmt.executeUpdate(); cf. Sta<c vs Dynamic SQL
SQL statement -‐-‐ cf. cursor • Note that types of column values do not need to be known at compile <me Lipyeow Lim -‐-‐ University of Hawaii at Manoa 8 ResultSet rs = stmt.executeQuery(sqlstr); while( rs.next() ){ col1val = rs.getString(1); … } SQL Type Java Class accessor BIT Boolean getBoolean CHAR, VARCHAR String getString DOUBLE, FLOAT Double getDouble INTEGER Integer getInt REAL Double getFloat DATE Java.sql.Date getDate TIME Java.sql.Time getTime TIMESTAMP Java.sql.TimeStamp getTimestamp
execute statement for each row can be inefficient – A message is sent for each execute • Many APIs provide a rowset implementa<on – A set of rows is maintained in-‐memory on the client – A single execute will then insert the set of rows in a single message • Pros: high performance • Cons: data can be lost if client crashes. • Analogous rowset for reads (ie. ResultSet) also available Lipyeow Lim -‐-‐ University of Hawaii at Manoa 9
that is called and executed via a single SQL statement – Executed in the same process space of the DBMS server – Can be programmed in SQL, C, java etc – The procedure is stored within the DBMS • Advantages: – Encapsulate applica<on logic while staying close to the data – Re-‐use of applica<on logic by different users – Avoid tuple-‐at-‐a-‐<me return of records through cursors Lipyeow Lim -‐-‐ University of Hawaii at Manoa 10
S.sname, COUNT(*) FROM Sailors S, Reserves R WHERE S.sid = R.sid GROUP BY S.sid, S.sname • Parameters modes: IN, OUT, INOUT CREATE PROCEDURE IncreaseRa<ng ( IN sailor_sid INTEGER, IN increase INTEGER ) UPDATE Sailors SET ra<ng = ra<ng + increase WHERE sid = sailor_sid Lipyeow Lim -‐-‐ University of Hawaii at Manoa 11
the support provided by SQL built-‐in func<ons • Three types of UDFs – Scalar: returns a single-‐valued answer. Eg. Built-‐ing SUBSTR() – Column: returns a single-‐valued answer from a column of values. Eg. AVG() – Table: returns a table. Invoked in the FROM clause. • Programable in SQL, C, JAVA. Lipyeow Lim -‐-‐ University of Hawaii at Manoa 14
CREATE FUNCTION TAN (X DOUBLE) RETURNS DOUBLE LANGUAGE SQL CONTAINS SQL RETURN SIN(X)/COS(X) • Reverses a string CREATE FUNCTION REVERSE(INSTR VARCHAR(4000)) RETURNS VARCHAR(4000) CONTAINS SQL BEGIN ATOMIC DECLARE REVSTR, RESTSTR VARCHAR(4000) DEFAULT ''; DECLARE LEN INT; IF INSTR IS NULL THEN RETURN NULL; END IF; SET (RESTSTR, LEN) = (INSTR, LENGTH(INSTR)); WHILE LEN > 0 DO SET (REVSTR, RESTSTR, LEN) = (SUBSTR(RESTSTR, 1, 1) CONCAT REVSTR, SUBSTR(RESTSTR, 2, LEN -‐ 1), LEN -‐ 1); END WHILE; RETURN REVSTR; END Lipyeow Lim -‐-‐ University of Hawaii at Manoa 15
department number. CREATE FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3)) RETURNS TABLE ( EMPNO CHAR(6), LASTNAME VARCHAR(15), FIRSTNAME VARCHAR(12)) LANGUAGE SQL READS SQL DATA RETURN SELECT EMPNO, LASTNAME, FIRSTNME FROM EMPLOYEE WHERE EMPLOYEE.WORKDEPT = DEPTEMPLOYEES.DEPTNO Lipyeow Lim -‐-‐ University of Hawaii at Manoa 16
RETURNS TABLE ( name VARCHAR(20), job VARCHAR(20), salary DOUBLE ) EXTERNAL NAME 'MYJAR1:UDFsrv! tableUDF‘ LANGUAGE JAVA PARAMETER STYLE DB2GENERAL NOT DETERMINISTIC FENCED NO SQL NO EXTERNAL ACTION SCRATCHPAD 10 FINAL CALL DISALLOW PARALLEL NO DBINFO@ import COM.ibm.db2.app.UDF; public void tableUDF( double inSalaryFactor, String outName, String outJob, double outNewSalary) throws Excep<on { int intRow = 0; … } // tableUDF } // UDFsrv class Lipyeow Lim -‐-‐ University of Hawaii at Manoa 17
lipyeow
bernhardsvt
cm_manaus
wingnus
debug_mode
kfujita
aki_moon
signer
matleenalaakso
irocho
nbkouhou
maggiecrowley
rmw
trallard
brad_frost
geoffreycrofte
speakerdeck
destraynor
sergeychernyshev
tammielis
deanohume
marktimemedia
tammyeverts
