PL/SQL and JDBC

1. PL/SQL and JDBC

2. Triggers and Procedures

3. Triggers • Trigger has • Trigger declaration (name,…) • Triggering event/statement • Trigger restriction if any • Trigger type if any • Trigger timing if needed • Trigger action/body

4. Trigger events/statements • DML statements • (INSERT, DELETE, UPDATE) on a table/view • DDL statements • CREATE or ALTER • User events • Logon, logoff, • Database events • Startup, shutdown, etc…

5. Trigger types • Row triggers • Execute once for each row affected by the trigger statement • If trigger statement is update emp set mgr = 10, then it updates all rows • Trigger body is executed for all rows • Statement triggers • Execute once for the entire trigger statement

6. Trigger timing • BEFORE trigger • Specified that the trigger ‘body’ is executed BEFORE the trigger statement • AFTER trigger • Specifies that the trigger ‘body’ is executed AFTER the trigger statement

7. Triggers • Trigger has • Trigger declaration (name,…) • Triggering event/statement • Insert on emp • Trigger restriction if any • Simple predicate (e.g. :new.mgr <> :old.mgr) • Trigger type if any • “for each row” (row-type trigger) • Trigger timing if needed • Before insert • Trigger action/body • PL/SQL block

8. Statement-type Trigger Example CREATE OR REPLACE TRIGGER emp_alert_trig BEFORE INSERT ON emp BEGIN DBMS_OUTPUT.PUT_LINE('New employees are about to be added'); END; / set serverout on INSERT INTO emp (empno, ename, deptno) SELECT empno + 1000, ename, 40 FROM emp WHERE empno =7900;

9. Row-type Trigger example Old row referred by :OLD; New row referred by :NEW CREATE OR REPLACE TRIGGER chkmgr BEFORE INSERT ON emp FOR EACH ROW DECLARE mgr_exists number; newmgr number := :NEW.mgr; BEGIN SELECT COUNT(*) INTO mgr_exists FROM EMP WHERE empno = newmgr; IF (mgr_exists = 0) THEN raise_application_error (-20000, 'Manager does not exist'); END IF; END; / show errors; INSERT INTO emp (empno, mgr) values (100, -1);

10. Triggers on VIEWs • Views are defined as “queries” on one or more tables • Triggers on views are translated as triggers on the underlying views by “INSTEAD OF” keywords. • Oracle fires the trigger instead of executing the trigger statement on the view • Restrictions • Need the “view” to be inherently modifiable. • Primary keys of the underlying tables should be in the view • View should not have group by, distinct, aggregates, etc.

11. Other useful functionality in PL/SQL • UTL_SMTP • Mailing from pl/sql inside the database • UTL_HTTP • … • HTML_DB (we will have an special class on this later)

12. Example using UTL_SMTP Send mail program using utl_smtp CREATE OR REPLACE PROCEDURE mail ( sender VARCHAR2, recipient VARCHAR2, message IN VARCHAR2 ) IS crlf VARCHAR2(2):= UTL_TCP.CRLF; connection utl_smtp.connection; mailhost VARCHAR2(30) := ‘cs-pop.bu.edu'; header VARCHAR2(1000); subject varchar2(32) := 'Hello'; BEGIN -- Start the connection. connection := utl_smtp.open_connection(mailhost,25); header:= 'Date: '||TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss')||crlf|| 'From: '||sender||''||crlf|| 'Subject: '||subject||crlf|| 'To: '||recipient||crlf; -- Handshake with the SMTP server

13. SMTP example contd… utl_smtp.helo(connection, mailhost); utl_smtp.mail(connection, sender); utl_smtp.rcpt(connection, recipient); utl_smtp.open_data(connection); -- Write the header utl_smtp.write_data(connection, header); utl_smtp.write_data(connection, crlf ||message); utl_smtp.close_data(connection); utl_smtp.quit(connection); EXCEPTION WHEN UTL_SMTP.INVALID_OPERATION THEN dbms_output.put_line(' Invalid Operation in SMTP transaction.'); WHEN UTL_SMTP.TRANSIENT_ERROR THEN dbms_output.put_line(' Temporary problems with sending email - try again later.'); WHEN UTL_SMTP.PERMANENT_ERROR THEN dbms_output.put_line(' Errors in code for SMTP transaction.'); END; / exec mail ('rkothuri@cs.bu.edu', 'rkothuri@cs.bu.edu', ‘Test message with body');

14. Interfacing with the Database • PL/SQL • Embedded SQL • JDBC

15. Embedded SQL SQL is not a general purpose programming language. + Tailored for data retrieval and manipulation + Relatively easy to optimize and parallelize • Can’t write entire apps in SQL alone Options: Make the query language “turing complete” Avoids the “impedance mismatch” but, loses advantages of relational lang simplicity Allow SQL to be embedded in regular programming languages. • The SQL standard defines embeddings of SQL in a variety of programming languages such as Pascal, PL/I, Fortran, C, and Cobol.

16. Embedded SQL • A language to which SQL queries are embedded is referred to as a host language, and the SQL structures permitted in the host language comprise embedded SQL. • EXEC SQL statement is used to identify embedded SQL request to the preprocessor EXEC SQL <embedded SQL statement > END-EXEC Note: this varies by language. E.g. the Java embedding uses # SQL { …. } ;

17. Example Query From within a host language, find the names and cities of customers with more than the variable amount dollars in some account. • Specify the query in SQL and declare a cursor for it EXEC SQL declare c cursor for select customer-name, customer-cityfrom depositor, customer, accountwhere depositor.customer-name = customer.customer-name and depositor account-number = account.account-numberand account.balance > :amount END-EXEC

18. Embedded SQL (Cont.) • The open statement causes the query to be evaluated EXEC SQL opencEND-EXEC • The fetch statement causes the values of one tuple in the query result to be placed on host language variables. EXEC SQL fetch c into :cn, :cc END-EXECRepeated calls to fetch get successive tuples in the query result • A variable called SQLSTATE in the SQL communication area (SQLCA) gets set to ‘02000’ to indicate no more data is available • The close statement causes the database system to delete the temporary relation that holds the result of the query. EXEC SQL closec END-EXEC Note: above details vary with language. E.g. the Java embedding defines Java iterators to step through result tuples.

19. Cursor EXEC SQL opencEND-EXEC c Every fetch call, will get the values of the current tuple and will advance the pointer A while loop to get all the tuples Also, you can move up/down, go to the start, go to end, etc.. Finally, you can update/modify a tuple through a cursor

20. Updates Through Cursors Can update tuples fetched by cursor by declaring that the cursor is for update declare c cursor for select *from accountwherebranch-name = ‘Perryridge’for update To update tuple at the current location of cursor update accountsetbalance = balance + 100where current of c

21. Comparisons of PL/SQL and Embedded SQL • PL/SQL is a better choice • Reduce overhead when executing multiple statements, improve performance, and increase productivity • Tight integration with the Oracle server. PL/SQL types are native to Oracle. • Can define Packages to bundle logically related types, objects and procedures/functions

22. Open DataBase Connectivity(ODBC) standard standard for application program to communicate with a database server. application program interface (API) to open a connection with a database, send queries and updates, get back results. Applications such as GUI, spreadsheets, etc. can use ODBC ODBC

23. Architecture Application ODBC driver • A lookup service maps “data source names” (“DSNs”) to drivers • Typically handled by OS • Based on the DSN used, a “driver” is linked into the app at runtime • The driver traps calls, translates them into DBMS-specific code • Database can be across a network • ODBC is standard, so the same program can be used (in theory) to access multiple database systems • Data source may not even be an SQL database! Data Source

24. ODBC/JDBC • Various vendors provide drivers • MS bundles a bunch into Windows • Vendors like DataDirect and OpenLink sell drivers for multiple OSes • Drivers for various data sources • Relational DBMSs (Oracle, DB2, SQL Server, Informix, etc.) • “Desktop” DBMSs (Access, Dbase, Paradox, FoxPro, etc.) • Spreadsheets (MS Excel, Lotus 1-2-3, etc.) • Delimited text files (.CSV, .TXT, etc.) • You can use JDBC/ODBC clients over many data sources • E.g. MS Query comes with many versions of MS Office (msqry32.exe) • Can write your own Java or C++ programs against xDBC

25. JDBC • Part of Java, very easy to use • Java comes with a JDBC-to-ODBC bridge • So JDBC code can talk to any ODBC data source • E.g. look in your Windows Control Panel for ODBC drivers! • JDBC tutorial online • http://developer.java.sun.com/developer/Books/JDBCTutorial/

26. JDBC Basics: Connections • A Connection is an object representing a login to a database // GET CONNECTION Connection con; try { con = DriverManager.getConnection( "jdbc:odbc:bankDB", userName,password); } catch(Exception e){ System.out.println(e); } • Eventually you close the connection // CLOSE CONNECTION try { con.close(); } catch (Exception e) { System.out.println(e); }

27. JDBC Basics: Statements • You need a Statement object for each SQL statement // CREATE STATEMENT Statement stmt; try { stmt = con.createStatement(); } catch (Exception e){ System.out.println(e); } Soon we’ll say stmt.executeQuery(“select …”);

28. CreateStatement cursor behavior • Two optional args to createStatement: • createStatement(ResultSet.<TYPE>, ResultSet.<CONCUR>) • Corresponds to SQL cursor features • <TYPE> is one of • TYPE_FORWARD_ONLY: can’t move cursor backward • TYPE_SCROLL_INSENSITIVE: can move backward, but doesn’t show results of any updates • TYPE_SCROLL_SENSITIVE: can move backward, will show updates from this statement • <CONCUR> is one of • CONCUR_READ_ONLY: this statement doesn’t allow updates • CONCUR_UPDATABLE: this statement allows updates • Defaults: • TYPE_FORWARD_ONLY and CONCUR_READ_ONLY

29. JDBC Basics: ResultSet • A ResultSet object serves as a cursor for the statement’s results (stmt.executeQuery()) // EXECUTE QUERY ResultSet results; try { results = stmt.executeQuery( "select * from branch") } catch (Exception e){ System.out.println(e); } • Obvious handy methods: • results.next() advances cursor to next tuple • Returns “false” when the cursor slides off the table (beginning or end) • “scrollable” cursors: • results.previous(), results.relative(int), results.absolute(int), results.first(), results.last(), results.beforeFirst(), results.afterLast()

30. ResultSet Metadata • Can find out stuff about the ResultSet schema via ResultSetMetaData ResultSetMetaData rsmd = results.getMetaData(); int numCols = rsmd.getColumnCount(); int i, rowcount = 0; // get column header info for (i=1; i <= numCols; i++){ if (i > 1) buf.append(","); buf.append(rsmd.getColumnLabel(i)); } buf.append("\n"); • Other ResultSetMetaData methods: • getColumnType(i), isNullable(i), etc.

31. Getting Values in Current of Cursor • getString // break it off at 100 rows max while (results.next() && rowcount < 100){ // Loop through each column, getting the // column data and displaying for (i=1; i <= numCols; i++) { if (i > 1) buf.append(","); buf.append(results.getString(i)); } buf.append("\n"); System.out.println(buf); rowcount++; } • Similarly, getFloat, getInt, etc.

32. Updating Current of Cursor • Update fields in current of cursor: result.next(); result.updateInt(“assets", 10M); • Also updateString, updateFloat, etc. • Or can always submit a full SQL UPDATE statement • Via executeQuery() • The original statement must have been CONCUR_UPDATABLE in either case! • To write the change back to the table, you specify • updateRow();

33. Cleaning up Neatly try { // CLOSE RESULT SET results.close(); // CLOSE STATEMENT stmt.close(); // CLOSE CONNECTION con.close(); } catch (Exception e) { System.out.println(e); }

34. Putting it Together (w/o try/catch) Connection con = DriverManager.getConnection("jdbc:odbc:weblog",userName,password); Statement stmt = con.createStatement(); ResultSet results = stmt.executeQuery("select * from Sailors") ResultSetMetaData rsmd = results.getMetaData(); int numCols = rsmd.getColumnCount(), i; StringBuffer buf = new StringBuffer(); while (results.next() && rowcount < 100){ for (i=1; i <= numCols; i++) { if (i > 1) buf.append(","); buf.append(results.getString(i)); } buf.append("\n"); } results.close(); stmt.close(); con.close();

35. Example: Updatable cursors conn = DriverManager.getConnection("jdbc:oracle:thin:@training:1521:Oracle", "oratest", "oratest") int i_deptno = 10; String sql = "SELECT empno, sal, comm FROM emp_with_type” + “ WHERE deptno = ?" ; // Specify the resultset as Scroll Sensitive and Updateable PreparedStatement pstmt = conn.prepareStatement(sql, ResultSet.TYPE_ SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); pstmt.setInt(1, i_deptno); ResultSet rset = pstmt.executeQuery(); while (rset.next()) { float i_sal = rset.getFloat(2); float i_comm = rset.getFloat(3); // Populate the resultset column using // the updateFloat() method on the ResultSet object rset.updateFloat(2, (float)(i_sal+(1.25*i_comm))); // Update the corresponding resultset row using the above value. rset.updateRow(); // write the change back to the table } rset.close(); pstmt.close() …..

36. Interesting links • http://edms-service.web.cern.ch/edms-service/ODF/odf8/eoug2000_paper60_1.0.doc • http://cegt201.bradley.edu/projects/proj2003/equiprd/database.html Note: all of this is changing every minute