Transcript JDBC - CS-People by full name

PL/SQL and JDBC
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
Fired by any user
Explicitly run by a user,
application or trigger
By a specific
user/application/trigger
Triggers
• Trigger has
–
–
–
–
–
–
Trigger declaration (name,…)
Triggering event/statement
Trigger restriction if any
Trigger type if any
Trigger timing if needed
Trigger action/body
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…
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
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
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
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;
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);
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.
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)
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
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 ('[email protected]', '[email protected]', ‘Test message with body');
Interfacing with the Database
• PL/SQL
• Embedded SQL
• JDBC
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.
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 { …. } ;
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-city
from depositor, customer, account
where depositor.customer-name = customer.customer-name
and depositor account-number = account.accountnumber
and account.balance > :amount
END-EXEC
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-EXEC
Repeated 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.
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
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
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
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
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!
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
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/JD
BCTutorial/
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); }
}
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 …”);
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
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()
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.
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.
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();
Cleaning up Neatly
try {
// CLOSE RESULT SET
results.close();
// CLOSE STATEMENT
stmt.close();
// CLOSE CONNECTION
con.close();
} catch (Exception e) {
System.out.println(e);
}
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();
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()
…..
Interesting links
• http://edms-service.web.cern.ch/edmsservice/ODF/odf8/eoug2000_paper60_1.0.d
oc
• http://cegt201.bradley.edu/projects/proj2003
/equiprd/database.html
Note: all of this is changing every minute