Transcript Java Stored Procedures - Sheffield Hallam University

Issues of Data Access
• 101(-ish) ways to access data from a database!
•ODBC
• One method in more detail: OLE DB and ADO
• In favour of Stored Procedures?
• A look at Java Stored Procedures
School of Computing and Management Sciences
© Sheffield Hallam University
JDO
Java
General data aware
apps eg:
Apps and
applets
VB Delphi etc
JDBC
RDBMS specific
apps eg:
Embe
dded
SQL
Forms
Native
JDBC - ODBC
Bridge
OLE DB
ODBC
Client Side
D
r
i
v
e
r
s
Proprietary database
access protocols
Other data stores
Other ODBC
Specific RDBSM
eg ORACLE
compliant RDBMS
SQL Databases
Server Side
It works both ways
• You can access non-Oracle databases using Oracle
client tools, such as Forms:
– Heterogeneous Services (HS) include:
• Generic Connectivity (using Industry standards)
• Transparent Gateways (using proprietary calls)
• From a non-Oracle IDE you can make native calls,
provided you have Oracle client binaries installed.
• And any mix in between….!
School of Computing and Management Sciences
© Sheffield Hallam University
Why such heterogeneousness?
•
•
•
•
•
•
Departmental decision making
Cheapest at the time
Familiarity
Organisational mergers
relative “importance” of sites
Personal Computing
School of Computing and Management Sciences
© Sheffield Hallam University
ODBC
• Recognised the need to get Front Ends talking to
different Back Ends
• Resulted form work done by SAG and X/Open
• Version 1, from Microsoft, 1992
• It is be database and language neutral, and
amongst other things it:
–
–
–
–
–
Makes and manages database connections
Allocates/deallocates handles
executes ISO SQL
retrieves results
Accesses schema metadata
School of Computing and Management Sciences
© Sheffield Hallam University
ODBC
• It does merely expose a database’s functionality,
and cant enhance it
• It does not allow for cross-platform functionality
• Version 1 and 2 suffered from a poor performance
reputation.
• This could be more to do with the RAD
environment that ODBC was often used with.
– See Ken North: odbcperf.html
School of Computing and Management Sciences
© Sheffield Hallam University
Using OLE DB
•
•
•
•
•
•
Real world information systems are increasingly made up from a series of
different technologies. Organisations may have both Mainframes and clientserver systems each with different databases and applications on them.
There is often a need for the PC desktop to be the presenter of information.
OLE DB responds to this scenario by setting out a specification for a set of
low-level data access interfaces to work against many types of data store in a
standard way.
Use of an Oracle provider is preferred because it is a native OLE DB provider
and gives access to Oracle-specific database features, such as support for
LOBs, PL/SQL stored procedures, and REF CURSORs.
Whilst Client needs to be 8i or higher, you can use OLE DB to access data
from Oracle databases of version 7.3.4 or higher.
Only one version of OraOLEDB can exist on a machine at a time, and,
because it is COM-based, OLE DB is unable to cope with multiple Oracle
homes.
School of Computing and Management Sciences
© Sheffield Hallam University
Using ADO to hide OLE DB
•
•
•
Microsoft ActiveX® Data Objects (ADO) is a high-level object-oriented interface
to OLE DB data which presents the applications programmer with a set of objects,
including a database independent recordset.
It is possible to call OLE DB methods directly, but the ADO encapsulation hides
the low-level complexities
The extra overhead of the extra layers of abstraction can be worth it if:
–
–
–
•
•
•
An application which accesses different database platforms simultaneously
An application which needs has the ability to be used with any customer-defined database back end
In an environment where (expensive) C++ programmers with low-level database experience are hard
to acquire, but where Delphi or VB skills are less hard to find
Another benefit of using ADO is that you can assume that the database access
layer is already in place if your clients are running MS Windows. ADO and OLE
DB are supplied by Microsoft and installed with Windows.
No need to worry about installing extras like the Borland Database Engine (BDE)
on client machines together with your application.
An ADO-based application requires that ADO 2.1 be installed on the client
computer.
School of Computing and Management Sciences
© Sheffield Hallam University
The ADO model
NOTE: Blue indicate Delphi component names that use the ADO object
School of Computing and Management Sciences
© Sheffield Hallam University
What is PL/SQL?
• SQL is non-procedural
– designed to be relatively approachable to nonprogrammers
• PL/SQL is a procedural extension to SQL
– many Oracle products are themselves written in
pl/sql
– but it IS proprietary
School of Computing and Management Sciences
© Sheffield Hallam University
What can be done with PL/SQL?
Standard DML commands
SQL functions
SQL transaction control
creation and control of “cursors”
flow control - IF, WHEN, LOOP….
Error Handling
DDL
School of Computing and Management Sciences
© Sheffield Hallam University
Where does PL/SQL live?
•
•
•
•
•
•
Anonymous Block
Application Procedure or Function
Database Trigger
Application Trigger
Stored Procedure or Function
Packages
School of Computing and Management Sciences
© Sheffield Hallam University
Stored Procedures
 Major benefits are speed….
– no compilation is required at execution time.
– Stored procedures are loaded once into the SGA
– They remain there until they are paged out.
– Subsequent executions of the stored procedure
are much faster
 Code management…
– Stored in the data dictionary.
– Can be called by many users.
– Improved coding productivity
School of Computing and Management Sciences
© Sheffield Hallam University
Stored Procedures cont….
• And Security
– eg: you can grant users access to a procedure that
updates a table but not grant them access to the
table itself
 However, DBAs need to be aware of the
memory demands of stored procedures
School of Computing and Management Sciences
© Sheffield Hallam University
Packages
• Packages encapsulate related procedures,
functions, and associated cursors and
variables together as a unit in the database.
• Only packages can be pinned.
• Oracle provides a procedure
dbms_shared_pool.keep to pin a package.
• Packages can be unpinned with
dbms_shared_pool.unkeep.
School of Computing and Management Sciences
© Sheffield Hallam University
Using JDBC
• You can experiment using JDeveloper
• Connections are identified by a JDBC URL:
– jdbd:protocol:subname@server:port:SID
• eg:jdbc:oracle:thin@EASTLEA:1521:bsms7
• There are a variety of implementation alternatives.
Don’t assume: TEST THEM!
– www.oreilly.com/catalog/jorajdbc/chapter/ch19.html
– Top 10 tips:
• www.onjava.com/lpt/a//onjava/2001/12/19/oraclejdbc.html
School of Computing and Management Sciences
© Sheffield Hallam University
Selecting a JDBC driver

If you are writing an applet, you must use the JDBC Thin driver. JDBC OCI-based
driver classes will not work inside a Web browser, because they call native methods.

If you want maximum portability and performance under Oracle8i and earlier, then
use the JDBC Thin driver. You can connect to an Oracle server from either an
application or an applet using the JDBC Thin driver.

If you are writing a client application for an Oracle client environment and need
maximum performance, then choose the JDBC OCI driver.

For code that runs in an Oracle server acting as a middle tier, use the server-side
Thin driver.

If your code will run inside the target Oracle server, then use the JDBC server-side
internal driver to access that server.

If performance is critical to your application, or you want maximum scalability of
the Oracle server, choose the OCI driver
Adapted from Oracle manual
School of Computing and Management Sciences
© Sheffield Hallam University
Java Stored Procedures
 This demonstration starts us off on the path of using
server-side JDBC for Stored Procs
 The four steps are:
 Write the Java Procedures
 Load the Class into the database
 Publish the procedure(s) to make them available in SQL
 Test them
 This example is a Java class which will contain
procedures for producing lists from BSMS
School of Computing and Management Sciences
© Sheffield Hallam University
Java Stored Procedures
 Write the Java Procedures
 Watch out: filename MUST = class name
 see BSMSLists.java
 Load the Class into the database
 From the Command prompt, use the LoadJava utility in the
Oracle_Home bin folder
 Only get feedback if errors
Java Stored Procedures
 Publish the procedure(s) to make them available in
SQL:
Java Stored Procedures
 Test them
JSP: More reading
 Metalink Note 68806.1: Java Stored Procedures in 8i
 Oracle manual online: Java Stored Procedures
Developer's Guide
School of Computing and Management Sciences
© Sheffield Hallam University