PL/SQL and JDBC

1
PL/SQL and JDBC
2
Triggers and Procedures
Triggers Procedures
Coded in pl/sql or java,.. Same (coded in pl/sql, java,..)
Implicitly fired by the server when triggering event occurs Explicitly run by a user, application or trigger
Fired by any user By a specific user/application/trigger

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

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
  hh24miss')crlf
• 'From 'sender''crlf
• 'Subject 'subjectcrlf
• 'To 'recipientcrlf

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

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
• Cant 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 ltembedded SQL statement gt 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-number
  and account.balance gt amount
• END-EXEC

18
Embedded SQL (Cont.)

• The open statement causes the query to be
  evaluated
• EXEC SQL open c END-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 close c 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 open c END-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 account
where branch-name Perryridge for
update To update tuple at the current location of
cursor update account set balance
balance 100 where 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
ODBC

• 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

23
Architecture
Application
ODBC driver
Data Source

• 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!

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/JDBC
  Tutorial/

26
JDBC Basics Connections

• A Connection is an object representing a login to
  a database
• // GET CONNECTION
• Connection con
• try
• con DriverManager.getConnection(
• "jdbcodbcbankDB",
• 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 well say stmt.executeQuery(select )

28
CreateStatement cursor behavior

• Two optional args to createStatement
• createStatement(ResultSet.ltTYPEgt,
  ResultSet.ltCONCURgt)
• Corresponds to SQL cursor features
• ltTYPEgt is one of
• TYPE_FORWARD_ONLY cant move cursor backward
• TYPE_SCROLL_INSENSITIVE can move backward, but
  doesnt show results of any updates
• TYPE_SCROLL_SENSITIVE can move backward, will
  show updates from this statement
• ltCONCURgt is one of
• CONCUR_READ_ONLY this statement doesnt 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
  statements 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 (i1 i lt numCols i)
• if (i gt 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 lt 100)
• // Loop through each column, getting the
• // column data and displaying
• for (i1 i lt numCols i)
• if (i gt 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
  odbcweblog",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 lt 100)
• for (i1 i lt numCols i)
• if (i gt 1) buf.append(",")
• buf.append(results.getString(i))
• buf.append("\n")
• results.close() stmt.close() con.close()

35
Example Updatable cursors

• conn DriverManager.getConnection("jdbcoracleth
  in_at_training1521Oracle", "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(sq
  l, 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.25i_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/o
  df8/eoug2000_paper60_1.0.doc
• http//cegt201.bradley.edu/projects/proj2003/equip
  rd/database.html
• Note all of this is changing every minute