Database Programming Transactional Information Systems

Database Programming Techniques intern SQL UDF Trigger SQL SP XQUERY

intern/extern ext. UDF SQLJ ext. SP Embedded SQL

extern JDBC ODBC OLE .NET

LINQ

Communication Overhead

Database Independence

2

Java Database Connectivity JAVA DATABASE CONNECTIVITY (JDBC)     

industry standard of Sun Microsystems current version: JDBC 4.0 (2006) allows communication between Java applications and databases independent from the employed database system (write once, run anywhere) table-based (mostly for relational DBMS)

BASIC FUNCTIONALITY    

establish database connection issue SQL commands handle results determine metadata

3

JDBC Architecture

Database Independent

JDBC Architecture

Packages: java.sql, javax.sql management of JDBC drivers, selection of correct driver, database independence translation of JDBC calls into special database protocol -> 4 types

5

Type 1 and 2 Driver TYPE 1: JDBC-ODBC BRIDGE    

Translates JDBC calls into ODBC Applicable with almost all database systems Requires database library on client side Slow

TYPE 2: NATIVE-API/PARTLY JAVA DRIVER  JDBC driver uses database-specific API  requires database library on client side  faster than type 1

Source: http://www.javaworld.com

6

Type 3 and 4 Driver TYPE 3: NET-PROTOCOL/ALL-JAVA DRIVER  JDBC calls are forwarded to middleware server  Middleware uses native database protocol  no binary code on client side, but database-specific middleware  high optimization potential (caching, load balancing)

TYPE 4: NATIVE-PROTOCOL/ALL-JAVA DRIVER  direct communication with the database  Java-based driver ( platform-independent)  fast Source: http://www.javaworld.com

7

Example: JDBC Driver DB2-JDBC DRIVER  CLI-based JDBC driver - type 2 • class: COM.ibm.db2.jdbc.app.DB2Driver • denoted as app driver - type 3 • class: COM.ibm.db2.jdbc.net.DB2Driver • denoted as net driver • outdated since DB2 V8  DB2 universal JDBC driver - also called JCC driver (Java Common Connectivity) - type 2 and type 4 - class: com.ibm.db2.jcc.DB2Driver - uses DRDA (Distributed Relational Database Architecture) - libraries: db2jcc.jar and db2jcc_license_cu.jar in sqllib/java 8

JDBC API

JDBC Overview 1) Initialize Driver

Class.forName(…)

Driver register

2) Initialize Connection

DriverManager. getConnection(…)

getConnectio n

prepare create Statement() Stmt(SQL)

DriverManager

executeQuery(SQL) set…(idx, …)

4) Parameterize and Execute Statement

3) Initialize Statement create

executeQuery()

Connection

Statement Prepared Statement

registerOutParameter(… ) executeQuery()

5) Iterate Result Rows and Get Values

next() get…(idx)

Callable Stmt(SQL)

Callable Statement

ResultSet

10

Connection Set-Up CONNECTION

Connection

 via java.sql.DriverManager (here) or javax.sql.DataSource  set-up Class.forName(); //load drivers Connection con = DriverManager.getConnection( [, , ]); //connect … con.close(); // close connection

JDBC URL    

describes database fundamental set-up: jdbc:: in general, multiple drivers possible for one URL Example - type 2: - type 3/4:

jdbc:db2: jdbc:db2://:/

 H2: - Local: jdbc:h2:[file:][] - Remote: jdbc:h2:tcp://[:]/[] - Details see http://www.h2database.com/html/features.html#connection_modes

11

Connection Object IMPORTANT METHODS OF THE CONNECTION INTERFACE

Connection

 void setAutoCommit(boolean autoCommit) - turn the automatic commit on/off  void commit() / void rollback() - commit or roll back all performed changes (if auto-commit is off)  Statement createStatement() - creation of a Statement object to execute SQL commands  PreparedStatement prepareStatement(String sql) - creation of a pre-compiled SQL command  CallableStatement prepareCall(String sql) - creation of a pre-compiled Stored Procedure call  DatabaseMetaData getMetaData() - returns metadata for the current connection

12

Example TRY

Connection

{

}

CATCH

}

CATCH

CLASS.FORNAME("COM.IBM.DB2.JCC.DB2DRIVER"); CONNECTION CON = DRIVERMANAGER.GETCONNECTION("JDBC:DB2:SAMPLE"); DATABASEMETADATA DBMD = CON.GETMETADATA(); SYSTEM.OUT.PRINTLN("DATABASE SYSTEM: “ + DBMD.GETDATABASEPRODUCTNAME()); SYSTEM.OUT.PRINTLN("VERSION: “+DBMD.GETDATABASEMAJORVERSION()+"."+DBMD.GETDATABASEMINORVERSION()); CON.CLOSE(); (CLASSNOTFOUNDEXCEPTION CNFE) { SYSTEM.ERR.PRINTLN("ERROR LOADING JDBC DRIVER"); (SQLEXCEPTION SE) { SYSTEM.ERR.PRINTLN("ERROR CONNECTING TO DATABASE");

}

OUTPUT DATABASE SYSTEM: DB2/LINUX VERSION: 9.5

13

Statement Object STATEMENT OBJECT

Statement

   

execution of a statement & querying of the result created via Connection.createStatement() explicit closing necessary (with close()) Attention: execution of an SQL command invalidates previous results  in some cases, multiple instances are necessary  result(s): modification counter or ResultSet

IMPORTANT METHODS  ResultSet executeQuery(String sql) - execution of a DQL command  int executeUpdate(String sql) - execution of a DDL/DML/DCL command  boolean execute(String sql) - execution of any arbitrary SQL command

 int getUpdateCount() - number of modified tuples  ResultSet getResultSet() - returns current result if ResultSet (else null)  boolean getMoreResults() - invalidates current result, switches to next result

14

ResultSet Object RESULTSET OBJECT  represents query results (table)  basic principle: cursor (also: iterator) - access only to current row - boolean next() • positions cursor on the next row • detects whether or not more tuples are available  explicit closing required (via close())  types - forward only: sequential processing - scrollable: free positioning of the cursor (more complex!)

IMPORTANT METHODS

ResultSet

 get*(int) / get*(String) - returns value of an attribute  update*(int, *) / update*(String, *) - changes value of an attribute  ResultSetMetaData getMetaData() - metadata over the query result

15

Example STATEMENT STMT = CON.CREATESTATEMENT(); RESULTSET RS = STMT.EXECUTEQUERY("SELECT LASTNAME FROM EMPLOYEE"); WHILE (RS.NEXT()) { SYSTEM.OUT.PRINT(RS.GETSTRING("LASTNAME") + "; "); } RS.CLOSE(); STMT.CLOSE();

Statement ResultSet

OUTPUT HAAS; THOMPSON; KWAN; GEYER; STERN; PULASKI; HENDERSON; SPENSER; LUCCHESSI; O'CONNELL; QUINTANA; NICHOLLS; ADAMSON; PIANKA; YOSHIMURA; SCOUTTEN; WALKER; BROWN; JONES; LUTZ; JEFFERSON; MARINO; SMITH; JOHNSON; PEREZ; SCHNEIDER; PARKER; SMITH; SETRIGHT; MEHTA; LEE; GOUNOT;

16

Pre-Compiled SQL Commands PRE-COMPILED SQL COMMANDS

PreparedStatement

 compilation costs time  idea: frequently used commands will be compiled only once  PreparedStatement  generate via: Connection.prepareStatement(String sql)  sub-class of Statement  can be parameterized via ? (efficiency problems in case of high skew)

IMPORTANT METHODS  executeQuery() / executeUpdate() / execute() - see Statement  set*(int i, *) - sets the i-th parameter 17

Example PreparedStatement

PREPAREDSTATEMENT PSTMT = CON.PREPARESTATEMENT("SELECT SALARY FROM EMPLOYEE WHERE LASTNAME=?"); PSTMT.SETSTRING(1, "HAAS"); RESULTSET RS = PSTMT.EXECUTEQUERY(); RS.NEXT(); SYSTEM.OUT.PRINTLN(RS.GETDOUBLE(1)); RS.CLOSE(); PSTMT.CLOSE();

OUTPUT 52750.0

18

Calling Stored Procedures CALLABLESTATEMENT OBJECT

CallableStatement

 execution of Stored Procedures  generate via: Connection.prepareCall(String sql) - parameter syntax: "{CALL (?,…)}" - automatic transcription to syntax of the DBMS  sub-class of PreparedStatement  pre-compiled  output parameters must be registered

IMPORTANT METHODS  see PreparedStatement  registerOutParameter(int i, int type); - registration of the output parameters - arguments • parameter index • data type (see java.sql.Types) 19

Example CallableStatement

GET_TABLE(IN TABLENAME VARCHAR(32), OUT SQL VARCHAR(128));  Stored Procedure: returns content of a relation as well as SELECT command CALLABLESTATEMENT CS = CON.PREPARECALL("{CALL GET_TABLE(?,?)}"); CS.SETSTRING(1, "EMPLOYEE"); CS.REGISTEROUTPARAMETER(2, TYPES.VARCHAR); CS.EXECUTE(); SYSTEM.OUT.PRINTLN("PARAMETER 2 (SQL): " + CS.GETSTRING(2)); SYSTEM.OUT.PRINT("RESULTSET (LASTNAME COLUMN): "); RESULTSET RS = CS.GETRESULTSET(); WHILE (RS.NEXT()) { SYSTEM.OUT.PRINT(RS.GETSTRING("LASTNAME") + " "); } SYSTEM.OUT.PRINTLN(); RS.CLOSE(); CS.CLOSE();

OUTPUT  PARAMETER 2 (SQL): SELECT * FROM EMPLOYEE  RESULTSET (LASTNAME COLUMN): HAAS THOMPSON KWAN GEYER STERN …

20

Advanced Programming

Database Programming Techniques intern SQL UDF Trigger SQL SP XQUERY

intern/extern ext. UDF SQLJ ext. SP Embedded SQL

extern JDBC ODBC OLE .NET

LINQ

Communication Overhead

Database Independence

22

Application logic in the database ADVANTAGES     

Small distance between data and logic Reduced network traffic Encapsulation and reuse More potential for query optimization Transactional execution …

IMPLEMENTATION  User-defined functions  Stored procedures  Trigger

Stored Procedure

User-defined Functions

Execute: … Execute: UPDATE Employee SET salary=bonus(salary) …

Trigger

23

Functions

Types FUNCTION  Maps input value(s) to output value  Build-in or user-defined  Used in SQL statement

TYPES  Scalar function - Single values  single value (scalar)  Aggregate function - Also know as column function - Set of values  single value (aggregate)  Row function - …  tuple  Table function - …  table 25

Scalar functions SCALAR FUNCTIONS  Input: fixed number of values (scalars)  Output: single value (scalar)  In SELECT-, WHERE-, GROUP BY-, HAVING-, ORDER BY clause

BUILD-IN SCALAR FUNCTIONS     

Type cast function, e.g. INTEGER(20.5) Data and time functions, e.g. DAY, MONTH, YEAR Numeric functions, e.g. ABS, SQRT, SIN String functions, e.g. LOWER, UPPER, CONCAT, SUBSTR Misc., e.g. COALESCE

EXAMPLE  VALUES(CONCAT('Hello ','World'))  Results in: Hello World 26

Aggregate functions AGGREGATE FUNCTIONS  Input: set of value (column)  Ouput: single value (scalar)  In SELECT-, HAVING-, ORDER BY clause

BUILD-IN AGGREGATE FUNCTIONS  Minimum (MIN), Maximum (MAX), Number of elements (COUNT), Average (AVG), Sum (SUM)

EXAMPLE  SELECT AVG(SALARY) FROM EMPLOYEE 1 ----------27303,59 27

Row and table functions ROW FUNCTIONS  Input: fixed number of structured values  Output: single tuple (fixed schema)

TABLE FUNCTIONS  Output: single table (fixed Schema)  In FROM clause  Usage: FROM TABLE(()) AS

28

External Functions

External UDFs EXTERNAL USER-DEFINED FUNCTIONS  Written in a higher programming language (e.g. C, Java, C#)  Why external? - almost any logics can be implemented - access to existing software components is possible

FUNDAMENTAL WORKFLOW 1. 2. 3. 4. 5.

Creation of the function Compilation (optional: generation of a library) Storing the binary code on the database server Registration Code via SQL DDL statement

30

External Scalar Functions SYNTAX CREATE FUNCTION ([] , …) RETURNS [CAST FROM ] [SPECIFIC ] EXTERNAL NAME LANGUAGE C|JAVA|CLR|OLE PARAMETER STYLE DB2GENERAL|JAVA|SQL [[NOT] FENCED] [[NOT] DETERMINISTIC] [[NO] EXTERNAL ACTION] [RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT] [NO SQL | CONTAINS SQL | READS SQL DATA] [ALLOW PARALLEL | DISALLOW PARALLEL] …

Signature Unique alias for overloaded function names Reference to external code External language Type of parameter handling Execution in own process Cacheable Null arguments Transactional behavior Parallelism

31

External Scalar Functions EXAMPLE  CREATE FUNCTION HELLOWORLD() RETURNS VARCHAR(15) EXTERNAL NAME 'UDFJavaStyleExample!helloWorld‘ LANGUAGE JAVA PARAMETER STYLE JAVA DETERMINISTIC NO SQL NO EXTERNAL ACTION;  VALUES(HELLOWORLD()) 1 -----------Hello World

32

External Scalar Functions SYNTAX    

Method name must be the full qualified class and method name Class and method must both be public, and method must be static Class must be available in the classpath of the database engine If first parameter of the Java function is a java.sql.Connection, then a connection to the database is provided

EXAMPLES  CREATE ALIAS MY_SQRT FOR "java.lang.Math.sqrt"; SELECT NAME,HEIGHT FROM MOUNTAIN WHERE MY_SQRT(HEIGHT) > 90  CREATE ALIAS REVERSE AS $$ String reverse(String s) { return new StringBuilder(s).reverse().toString(); } $$; SELECT REVERSE('Name') FROM CITY;

33

External Aggregate Functions SYNTAX  Method name must be the full qualified class name  Class must implement the interface org.h2.api.AggregateFunction.  Class must be public and must have a public non-argument constructor.  void add(Object value)

This method is called once for each row.  Object getResult()

This method returns the computed aggregate value.  int getType(int[] inputTypes)

This method must return the SQL type of the method, given the SQL type of the input data.  void init(Connection conn)

This method is called when the aggregate function is used.

34

External Table Functions EXAMPLE  CREATE FUNCTION ALL_EMPLOYEES() RETURNS TABLE(FULLNAME VARCHAR(128)) EXTERNAL NAME 'UDFDb2generalStyleExample!allEmployees‘ LANGUAGE JAVA PARAMETER STYLE DB2GENERAL DETERMINISTIC READS SQL DATA NO SCRATCHPAD NO EXTERNAL ACTION DISALLOW PARALLEL;  SELECT * FROM TABLE(ALL_EMPLOYEES()) FULLNAME --------------------CHRISTINE HAAS MICHAEL THOMPSON … 35

External Stored Procedures

External Stored Procedures EXTERNAL STORED PROCEDURES  definition and implementation are very similar to external UDFs

FUNDAMENTAL WORKFLOW     

Creation of the function Compilation (optional: generation of a library) Storing the binary code on the database server Registration Code via SQL DDL statement

37

Create Procedure SYNTAX CREATE PROCEDURE (IN|OUT|INOUT , … ) [SPECIFIC ] EXTERNAL NAME LANGUAGE C|JAVA|OLE PARAMETER STYLE JAVA|DB2GENERAL|DB2SQL|GENERAL|GENERAL WITH NULLS [[NOT] DETERMINISTIC] [DYNAMIC RESULT SETS ] [NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA] [CALLED ON NULL INPUT] [[NO] DBINFO] [[NOT] FENCED] [PROGRAM TYPE SUB | MAIN]

Output Parameter

Number of ResultSet

38

Create Procedure DDL CREATE PROCEDURE mod10 (IN x int, OUT result int) LANGUAGE Java PARAMETER STYLE JAVA EXTERNAL NAME 'SPJavaStyleExample.mod10'

CODE public static void mod10(int x, int[] result) throws Exception { result[0] = x % 10; CALL mod10(25,?) Value of output parameters } -------------------------Parameter Name : RESULT Parameter Value : 5 Return Status = 0

39

Create Procedure DDL CREATE PROCEDURE getNoEmployees (IN job CHAR(15), OUT num int) LANGUAGE Java PARAMETER STYLE JAVA EXTERNAL NAME 'SPJavaStyleExample.getNoEmployees'

CODE public static void getNoEmployees(String job, int[] num) throws Exception { Connection con = DriverManager.getConnection("jdbc:default:connection"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT count(*) FROM employee WHERE job='"+job+"'"); rs.next(); CALL getNoEmployees('DESIGNER',?) num[0] = rs.getInt(1); Value of output parameters -------------------------rs.close(); Parameter Name : NUM stmt.close(); Parameter Value : 10 } Return Status = 0

40

Create Procedure SYNTAX (SIMILAR TO FUNCTIONS)

   

Method name must be the full qualified class and method name Class and method must both be public, and method must be static Class must be available in the classpath of the database engine If first parameter of the Java function is a java.sql.Connection, then a connection to the database is provided

EXAMPLES  CREATE ALIAS MY_SQRT FOR "java.lang.Math.sqrt"; CALL MY_SQRT(1764);  CREATE ALIAS REVERSE AS $$ String reverse(String s) { return new StringBuilder(s).reverse().toString(); } $$; CALL REVERSE('Name') FROM CITY;

41

Trigger

Trigger TRIGGER  Set of actions executed in response to the event of a DML operation (UPDATE, DELETE, INSERT, and SELECT in some DBMS) on a specified table  Usage - Enforcement of business rules - Ensuring data integrity (extension of CHECK constraint) - Propagation of updates - Automatic generation of values - Triggering of arbitrary (external) actions  Multiple triggers per table/view possible  Immediately after definition active  Definition with DDL operation CREATE TRIGGER

43

ECA Concept EVENT  DML operation on table

CONDITION  Trigger is only fire if condition is fulfilled  Optional  Not always supported

ACTION  Set of actions to perform  Can be complex application logic

TRIGGER CASCADING  One trigger can fire another trigger  Attention: chain reaction possible 44

Release Point BEFORE  Before execution of DML operation  No modification permitted (except current tuple)  Usage - Enforcement of business rules - Ensuring data integrity - Automatic generation of values (in current tuple)

AFTER  After execution of DML operation  Trigger is executed after constraints have in checked  Usage - Propagation of updates - Automatic generation or update of values (in other tuple or relations) - Triggering of arbitrary (external) actions 45

Example BEFORE TRIGGER  ensure start time is before end time of a new lecture CREATE TRIGGER CLASS_END BEFORE INSERT ON CL_SCHED REFERENCING NEW AS new_row FOR EACH ROW MODE DB2SQL BEGIN ATOMIC DECLARE t TIME; IF new_row.STARTING > new_row.ENDING THEN SET t = new_row.ENDING; SET new_row.ENDING = new_row.STARTING; SET new_row.STARTING = t; END IF; END@

AFTER TRIGGER  update statistics after insert of new employee) CREATE TRIGGER NEW_HIRE AFTER INSERT ON EMPLOYEE FOR EACH ROW MODE DB2SQL UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1;

46

Create Trigger SYNTAX

 Trigger class must be public and implement org.h2.api.Trigger.  void close()

This method is called when the database is closed.  void fire(Connection conn, Object[] oldRow, Object[] newRow)

This method is called for each triggered action.  void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before, int type)

This method is called by the database engine once when initializing the trigger.  void remove()

This method is called when the trigger is dropped. 47

Example public static class MyTrigger implements org.h2.api.Trigger { // Initializes the trigger. public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before, int type) { // [...] } // This method is called for each triggered action. public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException { BigDecimal diff = null; if (newRow != null) { diff = (BigDecimal) newRow[1]; } if (oldRow != null) { BigDecimal m = (BigDecimal) oldRow[1]; diff = diff == null ? m.negate() : diff.subtract(m); } PreparedStatement prep = conn.prepareStatement("UPDATE INVOICE_SUM SET AMOUNT=AMOUNT+?"); prep.setBigDecimal(1, diff); prep.execute(); } public void close() { // [...] } public void remove() { // [...] } }

48

Summary JDBC  Interacting with a database out of Java programs

USER-DEFINED FUNCTIONS  Scalar functions  Aggregate functions  Table functions

STORED PROCEDURES TRIGGER  ECA Concept  Before/After 49