Week 9

April 2
March 26 canceled

==========================================

Topics:
	examples of PL/SQL that are hard to do in SQL
	triggers
	jdbc
	
==========================================

Summary of attempts to get oci version of jdbc running
	pld_ora in odbc control panel applet
	error 1114
	works for Admin, not for pld 8-(
	
===========================================================

Examples that do something  (started Week 6)

Find the total for a given invoice from the supplement.sql stuff

EXAMPLE: invoicetotal.sql:
Uses parameterized cursor

declare
   cursor invoice_cur (invnum invoice.invno%type)
   is 
      select ii.qty, it.ItemPrice from invitem ii, item it
      where ii.itemno = it.itemno AND ii.invno = invnum;
   inv_rec invoice_cur%rowtype;
   i integer;
   price integer(10,2) := 0.00;
   invnum integer;
begin
   i:= 1;
   invnum := &invoice_number;
   for inv_rec in invoice_cur(invnum) loop
       price := price + inv_rec.qty * inv_rec.ItemPrice;
       i := i+1;
   end loop;
   dbms_output.put_line('Total cost for invoice ' || invnum 
        || ' is ' || price);
end;


EXAMPLE: invoicetotal2.sql:
prints out line-by-line itemization
adds itemName

declare
   cursor invoice_cur (invnum invoice.invno%type)
   is 
      select ii.qty, it.ItemPrice, it.itemname from invitem ii, item it
      where ii.itemno = it.itemno AND ii.invno = invnum;
   inv_rec invoice_cur%rowtype;
   i integer;
   price integer(10,2) := 0.00;
   invnum integer;
   subprice integer(10,2);
begin
   i:= 1;
   invnum := &invoice_number;
   for inv_rec in invoice_cur(invnum) loop
       subprice := inv_rec.qty * inv_rec.ItemPrice;
       price := price + subprice;
       dbms_output.put_line('Item: ' || inv_rec.itemname
       		|| '  quanity: ' || inv_rec.qty 
       		|| '  each: '  || inv_rec.itemprice
       		|| '  total: ' || subprice);
       i := i+1;
   end loop;
   dbms_output.put_line('Total cost for invoice ' || invnum 
        || ' is ' || price);
end;

Other examples of problems you can solve using PL/SQL:

	* pricing with quantity discounts
		how do we model this?
	* calculating gpas for students
		look up student id
		convert grades to numbers
		multiply by credit hours
		special rules for re-takes, etc
		

====================================================================

====================================================================

TRIGGERS

applies to insert/update/delete

PL/SQL blocks stored in database and called automatically when a specified
event (such as insert, update, delete of row) occurs.

	create [or replace] trigger trigname
	before|after|instead of <event> ON <table|view>
	
Uses:
	validation of input data
	creating computed values, like current date
	creating sequential index values (p 330 example)
	
	BEFORE INSERT
		good for sanity checks, quantity_on_hand, reordering, 
		computed columns
		
		Fig 14-14 on p 330: compute EmployeeID, HireDate fields
		
		
	AFTER INSERT, UPDATE, DELETE
		can tell which using INSERTING, UPDATING, DELETING
		Fig 14-16: maintaining a separate transaction-history log
		after each update/delete, we record who did it
		
FOR EACH ROW:
trigger actions take place for each row being deleted/updated
versus just once for the batch

	-- trigger for the INVOICE table.
	-- when we order an item, if ITEM.QTYONHAND < INVOICE.QTY, fail!
	-- otherwise, if ITEM.QTYONHAND < 100 + QTY, plan to reorder
	
	create or replace trigger insert_invoice
	before insert on invoice
	for each row
	declare my_quantity item.qtyonhand%type;
		my_itemname    item.itemname%type;
		out_of_stock exception;
	begin
	    select item.qtyonhand, item.itemname
	    into my_quantity, my_itemname
	    from item
	    where item.itemno = :new.itemno;
	    
	    if my_quantity < :new.qty then
	    	raise out_of_stock;
	    elsif my_quantity < :new.qty + 100 then
	    	update item
	    	set qtyonhand = qtyonhand+100
	    	where itemno = :new.itemno;
	 --   	dbms_output.put_line('need to order more ' || my_itemname);
	    end if;
	    
	    exception
	        when out_of_stock then
	        raise_application_error(-20222, 'cannot add this INVOICE row; insufficent qty');
	end;
	/


cost of triggers

	:NEW
	How to make an insert fail: throw an exception
	
	Example: before inserting (invno, itemno, quan),
	check to see if we need to reorder
	fail if stock on hand is insufficient.

triggers are part of DB itself, NOT the front end.
In general, this is good (except for costs)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

JDBC

first example: jdbc/pld/studentreader.java

import java.sql.*;
import java.io.*;

    class studentreader {
	public static void main (String args[])
	    throws SQLException, IOException    {

		DriverManager.registerDriver(
			new oracle.jdbc.driver.OracleDriver()
		);

		String user = "indo";
		String pass = "indo";
		Connection conn = DriverManager.getConnection(
				"jdbc:oracle:thin:indo/indo@localhost:1521:xe");
			//	"jdbc:oracle:thin@localhost:1521:xe", user, pass);

		Statement stmt = conn.createStatement();

		ResultSet rset = stmt.executeQuery(
			"select studentid, last, first, startterm, facultyid, majorid"
			 + " from student");
		while (rset.next()) {
			System.out.println(
				rset.getString(1) + " "
				+ rset.getString(2) + " "
				+ rset.getString(3) + " "
				+ rset.getString(4) + " "
				+ rset.getString(5) + " "
				+ rset.getString(6) + " "
				+ rset.getString(6)
				
			);
		}
		stmt.close();
		conn.close();
	}
    }

Discuss:
  * drivers generally: OCI & THIN
  	url
  	SID: finding
  		select instance from v$thread

=======================================

  * try to getString(6)
  * create connection
    create statement
    execute query
    
    what if SQL is malformed?
    
    Connection
    Statement
    PreparedStatement
    	executeStatement	returns ResultSet
    	executeUpdate		returns int
    
    ResultSet
    	getters
    

    ResultSetMetaData

getString, getInt, getLong, getDouble, etc
findColumn(String colname)

Using column names too

conn.setAutoCommit(false)

metadata

CallableStatement
	IN, OUT
	

==============================================================

The demo program

To show:



Overview of Layouts (GridLayout, ??)
buttonbar & how it works
theArea: JScrollThingie

brief explanation of button ActionListeners

LabeledText

dbConnect

button1 & how it works: executeQuery

	what happens if we change it to executeUpdate?
	

button3:
	search by student id
	
	what if studentid were a numeric field?
	
	
button4: 
	search by lname
	note lowercase
	how should we handle "not found"?
	what if we find more than one??
	
button5: 
	PreparedStatement
	? parameter holders
	
button 6: conversion to prepared statement