Wednesday, November 29, 2006

Get Bolder with LOBs Manipulation in Java/JDBC

The Oracle database supports very large CLOB/BLOB columns/data however, the average Java/JDBC applications manipulates mostly medium size LOBs of tens/hundreds Kbytes in size.

WTF with LOB Programming in Java/JDBC

If you have ever dealt with LOBs in Java/JDBC applications, you know that it is not always straightforward, and sometime difficult to program cleanly. Let's take a closer look at the currently available approaches with Orace JDBC.

3 Ways to Slice and Dice LOBs

As of 10g Release 2, Oracle JDBC furnishes 3 ways of accessing and manipulating LOBs including:Oracle's LOB methods, optimized standard LOB methods, and LONG Streaming. Which one to use and when?In this article, i want to shed some light so as to make the reader more comfortable, bolder, when using CLOBs, BLOBs, and BFILE in Java/JDBC applications.

Oracle LOB Approach
LOB manipulation is done in two steps

Step#1: Manipulate the LOB Locator

Create or Retrieve, Insert, and Update the LOB locator using createTemporary, getEmptyCLOB, getEmptyBLOB methods. setBlob, setBLOB, setClob, setCLOB, getBlob, getBLOB, getClob, updateBlob, updateClob, setBLOB, setBLOBATName, setBFILE, updateBLOB, …, and updateCLOB methods.

ResultSet rs = stmt.executeQuery(“SELECT blobcol from XobTab”;
...
BLOB BlobLoc = ((OracleResultSet)rs).getBLOB(1);

Step#2: Manipulate the LOB Content

Write LOB content using getString, getSubString, and getBytes, setStrings, and putBytes methods.

tempLob.putBytes (1, bytarr);
PreparedStatement pstmt = conn.prepareStatement ( "insert into XobTab values (?)”;
((OraclePreparedStatement)ps).setBLOB (1, tempLob);

Read the LOB contents and Stream the content out, using getBinaryStream, get-String, getCharacterStream, and getAsciiStream.

byte[] bytarr = new byte[20]; // 20 bytes chunk
BLOB LOBLoc = ps.getBLOB (1);
InputStream is = LOBLoc.getBinaryStream();
while ((length = stream.read(bytarr)) != -1)
{
// process the chunk
bytarr = new byte[10];
}
is.close();

In pre-10g releases, JDBC-Thin used the PL/SQL DBMS_LOB package for LOB operations; in 10g, it uses a more nativead faster protocol however, certain operations such as hasPattern and isSubLob are still done via the dbms_lob package.

Standard and Optimized LOB Approach

For LOBs of size less than 2 gigabytes, LOB manipulation has been simplified especially in 10g Release 2) by removing the need to explicitly manage the locator. The standard methods getBytes, getBinaryStream, getString, getCharacterStream, getAsciiStream setBytes, setBinaryStream, setString, setCharacterStream, and setAsciiStream have been extended to take BLOB, CLOB and BFILE columns as parameters. This approach simplifies LOB programming; under the covers, the driver does the right thing (i.e., taking care of the locator, etc).

Example: Strings or byte arrays can be stored/read into/from the database, by simply using setString or setBytes, getString or getBytes.

LONG Streaming Approach

LONG streaming is furnished to help faster retrieval of data from a LOB without all the LOB overhead (i.e., retrieving the lob locators and going through the lob protocol), which can be very costly for small data. All you need to do is define the column as a LONG, then the driver takes care of LOB to LONG conversion and vice versa. This technique is faster than the locator approach (up to an order ofmagnitude) however, it is not as flexible as the traditional or optimized LOB manipulation and has the following restrictions: LONG streaming always starts at the beginning of the LOB (i.e., you cannot position or start anywhere); you may read as much data as you wish but only a single pass is allowed (i.e., you cannot read the stream twice).
Using this approach, CLOBs can be declared as LONGVARCHAR using defineColumnType. and streamed using getAsciiStream by redefining it a as
(OracleStatement)stmt.defineColumnType(,Types.LONGVARCHAR);

Similarly BLOBs columns can be declared as LONGVARBINARY using defineColumnType then streamed using getBinaryStream
(OracleStatement)stmt.defineColumnType(, Types.LONGVARBINARY);

Example:
// Pretend that these columns are of LONG and LONG RAW types
(OracleStatement) stmt.defineColumnType(1,Types.LONGVARBINARY);
(OracleStatement)stmt.defineColumnType(2,Types.LONGVARCHAR);
...
ResultSet rs = stmt.executeQuery(“select LOBCOL from XOBTab”);
// Retrieving a Clob with getString");
ocrs.setCommand ("SELECT lobcol FROM XOBTab");
ocrs.execute ();
while (ocrs.next ())
{
bytarr = stmt.getString (1));
}
ocrs.close ();

// Retrieving a Blob with getBytes");
ocrs.execute ();
while (ocrs.next ())
{
ocrs.getBytes (2).length);
}

Get More

This article, excerpted from my book, summarizes the various approaches to LOB manipulation using Oracle JDBC drivers. You can find more details and code snippets in either the Oracle JDBC 10g R2 doc or in chapter 8 of my book.

Sunday, November 05, 2006

WTF with SQLJ

What is SQLJ

SQLJ allows embedding SQL statements in Java, similar to Pro*C (and all precompilable languages). A SQLJ program is pre-compiled into JDBC (by a translator) and therefore represents a simpler and more productive alternatve to straight JDBC programming in client-tier, middle-tier and database-tier. A runtime mechanism is usually needed at execution time.

#sql iter = {SELECT ENAME FROM EMP where EMPNO ='7934'};
if (iter.next()) { System.out.println("Employee# 7934 is " + iter.ENAME()); }

Oracle SQLJ Status

SQLJ has been briefly de-supported in early releases of the Oracle Database 10g (namely 10.1.0.1, 10.1.0.2, and 10.1.0.3) but is fully re-supported in the Oracle Database 10.1.0.4 and up.

SQLJ Benefits
Many benefits including simplicity features, translation-time type checking, interoperability with JDBC, Oracle extensions to SQLJ, SQLJ Stored Procedures, and Runtime-free code generation.
Simplicity Features (Compared with JDBC)
  • Embedded host variables (JDBC uses parameter markers).
  • No need to cast data types.
  • No need to programmatically register output parameters.
  • No need to explicitly handle the NULL value (See JDBC support for Null in Part II of my book).
  • Support for SELECT INTO statements.
  • Support for PL/SQL anonymous blocks makes it easy to embed PL/SQL in Java.
  • No need for String concatenation for long SQL statements.

Translation-Time Type Checking

  • Syntax checking of SQLJ constructs: a SQLJ parser checks the grammar, according to the SQLJ language specification.
  • Syntax checking of Java instructions: a Java parser (invoked under the covers) checks the syntax of Java host variables and expressions within SQLJ executable statements.
  • Semantics checking: Depending on the option settings, this includes: Syntax of SQL statements by a SQL semantics checker; SQL offline parser; Validation of schema objects (i.e., tables name, columns name); Validation of the mapping between Java types and SQL types.

Interoperability with JDBC

  • JDBC connections can be used by SQLJ as a connection context and vice versa.
  • JDBC ResultSet can be passed to a SQLJ application as a SQLJ Iterator, and vice versa.

Oracle Extensions

Support for Dynamic SQL: Oracle extends it's implementation beyond the specification which only allows static SQL statements, to allow constructing SQL statements at runtime, similarly to JDBC.

String table = "new_Dept"; #sql { INSERT INTO :{table :: dept} VALUES (:x, :y, :z) };

Compile-Time Performane Optimizations

  • Row prefetching
  • SQLJ statement caching
  • Update batching
  • Column type(s) definition
  • Parameter size definition

SQLJ Stored Procedures

The database-resident Java VM (covered in depth in Part I of my book) embeds a SQLJ translator and runtime which allow you to load, pre-compile, compile and execute SQLJ source programs direcly in the database.
...

dbprint("Hello from SQLJ in the Database!");

Date today;

#sql {select sysdate into :today from dual};

dbprint("Today is " + today);

...

static void dbprint(String s) {

try {

#sql { call dbms_output.put_line(:s)

};

} catch (SQLException e) {} }

Runtime-free code generation

SQLJ traditionally requires a runtime (implemenrtor specific). Oracle'simplementation allows you to generate runtime-free code (i.e., oure JDBC).

Advanced/Powerful SQLJ Concepts

SQLJ is more than a mere high-level JDBC code; the language/specification defines concepts such as connection contexts, execution context, iteratorsand expressions that are more advanced than their JDBC counterpart (when they exis).
A SQLJ program is a Java/JDBC code with SQLJ declaration statements,and SQLJ executable statements.

SQLJ declaration statements
This category contains import statements, connection contexts declaration,execution contexts declarations, and iterators declarations.

Excutable Statements

Executable statements can be grouped into Statementclauses and Assignment clauses.
Statement Clause:

#sql [optional SQLJ space] { Statement clause };

Assignment clauses

Query clause

#sql [conctx, exectx] iter = { SQLJ clause };

Function clause

#sql result = { VALUES ( ())};

Iterator conversion clause

#sql iter = { CAST :rset };

Expressions

Expresions are another powerful SQLJ mechanism that allows you to exploit Java expressions in SQLJ statements including: host expressions,context expressions and result expressions.

#sql [connctx_exp, execctx_exp] result_exp = { SQL with host expression };

Accessing Oracle SQL and PL/SQL Data Types
SQL and PL/SQL data are manipulated in SQLJ through SQLJ host variables/expressions, result expressions, and iterator accessors.

Assume a table with an XMLType column, created as follows:

create xmltype_tbl (xmltype_col SYS.XMLType);

insert into xmltype_tbl values(SYS.XMLType('tom'));

insert into xmltype_tbl values(SYS.XMLType('jon'));

It can be manipulated using the following code fragments:

import oracle.xdb.XMLType;

...

//

#sql iter={select xmltype_col from xmltype_tbl;}

while(iter.next()) {

System.out.println(iter.xmltype_col().getStringVal());

}

//

while (iter.next())

{

System.out.println(iter.xmltype_col.getClobVal());

}

Learn more about SQLJ and Oracle implementation and code samples

Sunday, September 03, 2006

Manipulating Tabular Data using JDBC RowSet API

If you Google "Tabular Data", you will get more than 2 millions hits, which is an illustration of the pervasive and broad scope of the concept. So what are tabular data? The following proposal might not be the most well thought definition but tabular data can be defined as data organized or presented as items/columns and records/rows that can be stored in (or retrieved from) various persistence repositores including file system, spreadsheet, relational tables, XML documents and so on.

This blog will introduce the goals the JSR-114 RowSet, briefly describe the various models and furnish pointers to get further details and code samples.

Why JDBC RowSet?

When dealing with tabular data, a number of Java SE/EE and Web Services requirements (such as JavaBean properties, XML documents properties) that are not addressed by the existing JDBC interfaces.

The RowSet specification (http://www.jcp.org/en/jsr/detail?id=114) addresses the following requirements and shortcomings of JDBC specifications:

  • Make query results scrollable and updatable without relying on the underlying JDBC driver
  • Manipulate data (e.g., filtering and joining) and make changes while disconnected from data sources
  • Associate properties and event listeners to query results to facilitate manipulation by tools
  • Consume data results in chunks and ship result set to thin clients, handheld devices, and PDAs which are most of the time not connected and do not have a full-fledged JDBC driver
  • Convert data results to or from XML documents.

The RowSets Models

The RowSet Specification (JSR-114) formalizes and completes the concept of RowSet introduced in JDBC 2.0. It defines five standard JDBC RowSet interfaces including: JdbcRowSet, CachedRowSet, WebRowSet, JoinRowSet, and FilteredRowSet (see Figure 1 below).

The RowSet Hierarchy

The base JDBC RowSet interface (javax.sql.Rowset) encapsulate/extend ResultSets (java.sql.ResultSet) through a set of properties including: datasource, datasourcename, url, username, password, typeMap, maxFiedSize, maxRows, queryTimeout, fetchsize, transactionisolation, escapeProcessing, command, concurrency, readOnly, fetchDirection, as well as events and an event listener mechanism.
In general, each rowset model is populated from a repository using the repositorey-specific APi (i.e., JDBC for RDBMS), then manipulated using the towset-model-specific methods.
In the rest of this blog, I'll give you a brief description of each model excerpted from my book (a full coverage of the RowSet API is beyond the scope of a blog).

JDBCRowSet

The JDBCRowSet object is a JavaBean equivalent of ResultSet; as such, it maintains a connection to its datasource, has a set of properties,and a listener notification mechanism. The contents are identical to those of a ResultSet object, and it inherits all of the result set methods for rows manipulation, cursor movement, and data manipulation.
The OracleJDBCRowSet class implements the javax.sql.rowset.JdbcRowSet interface.

CahedRowSet

The CachedRowSet object represents disconnected RowSet such that the row data and metadata can persist beyond the connection. When the connection to the database is restored, any modifications to the RowSet are propagated back to the database. It caches its rows in memory and allows operating on these without being connected to the datasource, resulting in increased scalability. In addition, it turns any result set object (even the read-only ones) into scrollable and updatable rowsets.
The OracleCachedRowSet class implements the javax.sql.rowset.CachedRowSet interface.

WebRowSet

A WebRowSet object is a CachedRowSet object, augmented with a default WebRowSetXmlReader, which reads an XML document into the rowset, and a default WebRowSetXmlWriter, which writes the rowset as an XML document.

You can:
  • Create a WebRowSet object and dump its contents to an XML document.
  • Read an XML document into a WebRowSet object.
  • Make changes to a WebRowSet object (Insert, Update, Delete rows) and synchronize it back to the datasource.

The W3C XML schema for WebRowSet is defined by Sun, and available at http://java.sun.com/xml/ns/jdbc/webrowset.xsd.

The oracle.jdbc.rowset.OracleWebRowSet class implements the javax.sql.rowset.WebRowSet interface. The oracle.jdbc.rowset.OracleWebRowSetXmlWriter class extends the javax.sql.rowset.spi.XmlWriter.

FilteredRowSet

A FilteredRowSet object simulates a SQL WHERE clause by applying user-defined filtering criteria on rows in a disconnected rowset; in other words, it returns/retrieves only rows that meet the defined criteria, and inserts/updates only rows that meet the constraints defined in the filter(s). The constraints are defined within a predicate class then assign this predicate to the FilteredRowSet object; as a result, the constraints defined in the predicate class will be evaluated against each row in the rowset object.In order to use filtered rowsets the JDBC applications must:

  • Define and implement the Predicate interface.
  • Create an instance of FilteredRowSet object and set properties.
  • Populate the FilteredRowSet.
  • Set/enable filters.
  • Retrieve the filtered rows.

The oracle.jdbc.rowset.OracleFileteredRowSet class implements the javax.sql.rowset.FilteredRowSet; in addition the oracle.jdbc.rowset.OraclePredicate interface inherits from the javax.sql.rowset.Predicate.


JoinRowSet

The JoinRowSet object represents a SQL JOIN operation between disconnected RowSet objects.A JoinRowSet object combines data from multiple rowsets. In order to use Join rowsets, the JDBC applications must:
  • Create an empty instance of JoinRowSet object and set properties.
  • Create and add RowSets objects to the JoinRowSet.
  • Define the match column (similar to SQL matching columns).
  • Navigate and consume the JoinRowSet Object.

The oracle.jdbc.rowset.OracleJonRowSet class implements the javax.sql.rowset.JoinRowSet interface.


Further Details

The JSR-114 furnishes a reference implementation of each rowset type as part of Java/JDK 5.0, or as stand-alone Jars (i.e., rowsetjsr114.jar) that can be used on top of any standard JDBC driver. Oracle implements JSR-114 specification as part of the JDBC drivers (i.e., ojdbc14.jar).

For more details, see:

Tuesday, August 01, 2006

Oracle Database Programming using Java and Web Services

This book is about Oracle database programming using Java and Web services. Foreword by Tom Kyte (html) (pdf)


This book is for database developers (DBA, PL/SQL developers, data architects), Java/JDBC/SQLJ developers, and Web services /SOA assemblers.

Through its 1085 pages, it furnishes a complete coverage of the following topics:
  • Java in the database: stored procedures as database programming model (rationale, obstacles to their adoption, languages for stores procedures, PL/SQL or Java?). Under the hood of the Java runtime in the database (design goals, architecture, memory management, security, threading, Java execution in the database, native compilation, etc). Examples of typical and atypical applications (SAP callout, JDBC callout, EJB callout, HTTP Callout), Excel-like expressions parser) using Java in the database. Mapping SQL and PL/SQL types to/from Java. Declaring Java stored procedures (Call Specs). Experimental examples of running JACL, JYTHON, SCHEME and GROOVY in the database using the Java runtime.
  • Oracle JDBC 10g: In depth coverage of the JDBC API specifications and Oracle implementation. Connection and connection services (Implicit Connection Caching). High-Availability in RAC environments (ONS, Fast Connection Failover, TAF) and scalability (Runtime Connection Load balancing in RAC environments). Statements and Oracle extensions (statement caching, DML batching). Manipulating Oracle data types with JDBC. Practical coverage of Result Sets and the various RowSet models (JSR-114) . Proxy Authentication, SSL encryption, end-to-end tracing, Logging, local/global Transactions, Security and best practices.
  • Oracle SQLJ 10g: Concise coverage of the SQLJ Language/API and Oracle implementation. Manipulating SQL and PL/SQL data types with SQLJ and contrasting with JDBC (interoperability, differences). Comprehensive coverage of Oracle datatypes mapping, and best practices.
  • JPublisher in Action: a practical coverage of a versatile tool that complements JDBC (and SQLJ) for accessing the Oracle database from Java. Automatic wrapping of PL/SQL packages as well as mapping complex/user-defined types to Java.
  • Database Web services: Introduction to Web services and SOA for DBAs. How to turn your Oracle database into a Web service consumer (call-out external Web services from within the database using SQL, PL/SQL, Java). How to turn your database into a Web services provider (exposing PL/SQL package into Web service as wellas SQL Query, AQ quques and operations, SQL Queries and DML, and Java in the database --using few mouse clik or a command line utility. How to add SOA services to database web services.

Here is the first chapter, the Table Of Content, the code samples at OTN and at the publisher's web site, and a reader's review.

Enjoy and pass on the buzz.

Tuesday, July 11, 2006

Get the Best Performance out of Java in the Database

When you run Java in the database, what are the various execution modes? Are you taking the most efficient approach? In this post I'll touch on the various execution modes for running Java in the Oracle database.

As we all know, the best execution performance for Java is achieved via binary executables obtained through compilation (JIT compilers and/or static compilers). The Oracle Database currently furnishes a static native Java compiler known as NCOMP, which must be explictly invoked ahead of the deployment.

There are currently three execution modes: (i) fully interpret, (ii) NCOMPed System classes, and (iii) fully NCOMPed (or NCOMPed User Classes).

Fully Interpreted (System and User Classes)
In this mode, both system classes and the user classes, run interpreted. During the database installation, the non-compiled Java VM system classes are installed. By default, user classes also run interpreted and most customers stick to the default mode and are happy with the level of performance obtained, mostly because, as explained in previous posts and in my book, the combination of Java and SQL run faster in the database (even fully interpreted). However, this is not the most efficient execution mode.

NCOMPed System Classes and Interpreted User Classes
In this mode, the system classes run natively compiled while user classes run interpreted. The natively compiled system classes (NCOMP libraries) are installed explictly following the default database install, from the companion CD. Without further explcit action, the user classes run interpreted on top of the natively compiled system classes. The overall peformance is greater than the previous mode (i.e., fully interpreted).

Fully NCOMPed (System and User Classes)
In this mode, the system and user classes run natively compiled. User NCOMP requires an explicit action from the developers. It comprises three commands/steps: 'NCOMP', 'STATUSNC', and 'DEPLOYNC'.

NCOMP

This command takes Java bytecodes that have been resolved and verified during the loading phase (loadjava) and can therefore be trusted and expected to execute correctly, then does three things:
  1. gathers class info, generates a script that will drive the NCOMPing process, and produces the list of NEED_NCOMPNG orALREADY_NCOMPED methods
  2. Pauses for the Java-to-C translation: C compilation and linkage of each package involved into platform-specific DLLs
  3. Deployment/installation of the resulting DLL (unless -noDeploy) is specified


ncomp [ options ] class_designation_file
-user -u username/password [@database_url]
[-load]
[-projectDir -d project_directory]
[-force]
[-lightweightDeployment]
[-noDeploy]
[-outputJarFile -o jar_filename]
[-thin]
[-oci -oci8]
[-update]
[-verbose]

STATUSNC

This command checks whether the JAR files, ZIP files, or CLASSES files are NCOMPed or not.
statusnc [ options ] .jar .zip
.classes.
-user /[@database]
[-output -o ]
[-projectDir -d ]
[-thin]
[-oci -oci8]

DEPLOYNC

This command deployd the natively compiled deployment JAR file, to the database.
deploync [options] deployment.jar
-user -u username/password [@database_url]
[-projectDir -d project_directory]
[-thin]
[-oci -oci8]

Conclusions

NCOMP may give an order of magnitude speed up (i.e., 10 times) compared to interpreted execution. But as usual, there is no absolute figure; it all depends on what exactly you are NCOMPing as NCOMP speeds up only Java code, not the embedded SQL.

See more details in the Java Developer's Guide of the Oracle Database documentation nd in my book.

Wednesday, June 21, 2006

Invoking External Functionality/System from within the Database

Looking at the picture at the following URL,
http://photos1.blogger.com/blogger/4310/2356/1600/360_DB_Prog.2.jpg, i have touched through my previous posts on the first three points, including:
(1) the user-defined database functinality itself,
(2)calling database functionality from within the database,
and (3) calling user-defined database functionality from outside.

In this post, I'll touch on the fourth point (4): invoking external functionality from within the database.
The requirements range from alerting or notifying external systems based on data value/state, validating or obtaining credit card authorization, querying/updating remote database, invoking a rate/tax engine, obtaining dynamic data, invalidating middle-tier data cache, messaging across tiers, executing OS commands, and so on.

The Java runtime in the database allows the use of various Java-based mechanisms and protocols, depending on the design/implementation of the target external systems as highlighted in the following examples.

- Calling out external RMI Servers allow invoking SAP System from within the database
through SAP JCO.

- Implementing an HTTP-callout trigger using the Java-based HTTPClient for requesting a
Web component (i.e., JavaServer Page) to invalidate a middle-tier data cache. Similarly a
credit card authorization and validation can be implemented using HTTPS Call-out.

- Alerting/Notifying external systems via email, using Java-based mailer in the database.

- Implementing JDBC-Callout to issue queries and DML against remote non-Oracle database(s)
using a 3rd party pure Java JDBC driver.

- Messaging across tiers using JMS over Streams AQ by invoking Message-Driven Beans in the
middle-tier.

- Web Services Callout allows retrieving dynamic data from external Web services or invoking
remote operations through Web services mechanisms.

- Invoking OS commands using Runtime.exec('').

- And so on ...

The beauty of embedding a full fledged Java VM in the database is that even when a protocol or invocation mechanism is not available by default, you can reuse existing Java library that implements the mechanism or protocol in question. The complete details of the examples and case studies with the code samples are available in my soon-to-be-released book http://www.amazon.com/gp/product/1555583296.

Monday, May 29, 2006

Invoking User-Defined Database Functionality from within and outside the database

In my previous post i touched on the fact when database access is significant, it is often more efficient to implement and run user-defined functionality directly in the database.
In his dated but still good report on distributed computing economics (i.e., Grid Computing) Jim Gray's recommends to "put computing as close to the data as possible in order to avoid expensive network traffic". See ftp://ftp.research.microsoft.com/pub/tr/TR-2003-24.pdf.
This is true for most RDBMS; as far as the Oracle database is concerned, this is true for PL/SQL, Java, and Web services (i.e., Database Web Services).

Now, assume you have built user-defined database functionality, how do you invoke it from within and from outside the database? With the Oracle database, there are three cases:

1/Invoking PL/SQL based user-defined database funtionality

Assume Func_nn(),Proc_nn(),Pkg_xx.Func_nn(), and Pkg_xx.Proc_nn()

  • Invocation from SQL, PL/SQL, and SQL*Plus
    SQL>CALL Func_nn();
    SQL>CALL Pkg_xx.Func_nn();
    SQL>CALL Pkg_xx_Proc_nn();
    SQL>Begin Proc_nn() End/
  • Invocation from Java (J2EE, JDBC, Java-in-the-database): same as Java-based functionality see below.

2/ Invoking Java-based user-defined database functionality

  • Invocation from Java-in-the-database: A user-defined Java functionality is transparently invoked by another Java class running in the database, provided the invoker has the proper privileges and authorization.
  • Call Specifications: Java-based functionality is made available/known to SQL (therefore to PL/SQL, and JDBC) through a PL/SQL Wrapper called Call Spec. There exist top-level Call Spec,PL/SQL-packaged Call Spec, and object type Call Spec. Call Spec must map parameters and retur values to/from SQL or PL/SQL types to Java (and JDBC supported) types; such mapping can be challenging unless you use a tool (i.e., JPublisher).
  • Invocation from SQL, PL/SQL and SQL*Plus: similar to PL/SQL-based functionality (see above).
  • Invocation from Java/JDBC/J2EE using the SQL 92 Syntax CallableStatement cstmt = conn.prepareCall(“{? = call func(?, ?)}”);
  • Invocation from Java/JDBC/J2EE using the PL/SQL Anonymous Block syntax CallableStatement cstmt = conn.prepareCall(“begin ? := func(?, ?); end;”); Note: with JDBC programming, Output parameter(s) must register the data types and the value(s) of Input parameter(s) must be set. However,not all SQL database types are supported.
  • Invocation from Java/JDBC/J2EE using Client-Stub: a JPublisher-generated client-side stub allows callingmethods on Java classes in the database, without the provision of a user-definedCall Spec (see above), and without their limitations (of Call Specs).
  • Invocation from OJVMJAVA: the Oracle database furnishes an interactive command-lineutility called OJVMJAVA, which lets you run Java classes in the database (i.e., classes with a public static main() method) from the client machine.

3/ Invoking User-defined Database Functionality as Web Services Simply put, Web services consist of a service client and service provider exchanging SOAP-based XML messages using Web protocols. User-defined database functionality can be published as Web Services and invoked by by standard-compliant Web services clients. However, beyond SOAP, WSDL, and UDDI, there are more to Web services. The Oracle implementation of Database Web services benefit from the Quality of Services furnished by the Oracle AS SOA stack. The steps for turning database functionality as Web Services is described in Database Web Services portal (linked from this blog).

All these approaches are fully decribed in my book http://www.amazon.com/gp/product/1555583296/

In my next post I'll touch on invoking external functionality from within the database.

Wednesday, May 03, 2006

User-Defined Database Functionality

Beyond the out-of-the-box built-in RDBMS functionality, you often need to extend the capabilities of your database by developing your own database-resident functionality, or data logic.

The requirements for user-defined database functionality range from performance (see free-sample chapter in previous posts), integration with existing functionality, security, ability to share data logic (across all clients applications), cost reduction, skill reuse, deployment/topology choices (see dilemma below), and many other reasons.

The following applications are candidates for being implemented as user-defined database functionality:
- Traditional data logic: SQL statements grouping
- Content/Repository management
- In-database Message Queuing and propagation
- Reporting functionality: generate pdf, excel, and XML documents from relational data
- Data-based Alert, and Notification
- Expressions Parsers (see code fragment below)
- Key generation, MD5 CRC checksum
- Multi-media data processing: store, retrieve, transform/convert images (GIF, PNG, JPEG), audio, video, or heterogeneous media data. Alternatively you can just use Oracle interMedia, a packaged multi-media processing framework.
- Text data management: index, search, and analyze texts stored in RDBMS. Alternatively you can just use Oracle Text, a packaged text data processing framework.

However, an RDBMS is not an application server, you must make the determination that the user-defined functionality pertains indeed to the RDBMS.
Examples:
- tax and rate engines, which take input values and return tax amounts or rates, are better implemented as EJBs and do not belong to the RDBMS.
- a JavaServer Pages that generate user-interface rendered in a browser does not belong to the RDBMS.
As discussed in many forums, making the determination is not always a clear cut.
Examples:
- a frequent dilemma is the choice between caching data in the middle-tier (i.e., CMP Beans, POJO) or pushing the processing closer to the data (i.e., stored procedures)? See http://www.theserverside.com/discussions/thread.tss?thread_id=2613
- a server-side UI can be produced using either a JavaServer Pages (that is deployed in the middle-tier) or the database-resident Oracle Application Express (formerly known as HTML DB), or use the HTP package, directly in the database.

The mechanisms and programming models for building user-defined database functionality
range from proprietary procedural languages (e.g., PL/SQL, T-SQL), standard languages (e.g., Java), de facto standard languages (C#, VB), messaging systems for database messaging, job/task scheduler, and so on.

The Oracle database furnishes PL/SQL, Java, Streams AQ, DBMS_SCHEDULER and so on for building user-defined functionality.
There are tons of PL/SQL examples, see http://asktom.oracle.com.
Java in the database is an open alternative to PL/SQL for implementing advanced functionality such as a domain-specific language parser. Imagine an Excel expression evaluator/parser that can be used to generate formulas representing complex interrow calculations in the database and dynamically generating the corresponding SQL.

Mini Parser code fragment
public class Mini parser {
package example;
import example.parser.parser;
import example.formula.Expr;
import java.io.StringReader;

public static String eval(String spreadsheetExpr) throws Exception {
parser p =
new parser(new StringReader(spreadsheetExpr));
Expr expr = (Expr)p.parse().value;
return expr.eval();
}

public static void main(String[] args) throws Exception {
System.out.println(eval(args[0]));
}
}

Then the following query returns 15 which is not the generated SQL, but gives you an idea of how the expression evaluator works:

select eval('=sum(1,2,3,4,5)') from dual;

The complete mini-parser source code as well as the corresponding CUP and LEX descriptions are available in chapter four of my book (see link on this blog).

In the next posting, I'll elaborate on how to make the user-defined database functionality available for invocation by other database components as well as external components.

Monday, April 24, 2006

Database Functionality: Beyond Data Storage

Ain't no enterprise applications without a database (RDBMS), still some of us have the tendency to think of databases as dumb data repositories or mere storage engines for Relational/Structured, Binary/MultiMedia, Documents/Unstructured and Semi-Structured/Messages data. If so, as Henri Bergson wrote "The eye can only see what the mind is prepared to accept" -- iow, you cannot see and utilize all their possibilities.

An exhaustive coverage of database functionality, that you get out-of-the-box, and those that you can build yourself is beyond the scope of a blog -- but range from OLTP to OLAP/Business Intelligence, from Messaging to Content Management, and much more.
In the next posting I'll touch on programming models that you can use to build database functionality, also known as data logic.

Sunday, April 16, 2006

What 360 degrees Database Programming Means


As some of you have asked me privately -- don't be shy, post your comments :), what the h. does 360 degree database programming means? Well, 4 things that i will elaborate on in following posts: (1) the database functionality itself, (2) calling database functionality from within the database, (3) calling database functionality from outside (client, middle-tier, etc), and (4) calling external system/functionality from within the database.

Friday, March 31, 2006

Stored Procedure as Database Programming Model

I thought the best way to trigger this blog is to discuss the rationales for stored procedures, the obstacles to their adoption, languages used for writing stored procedures, and the $100000 question of proprietary procedural languages such as PL/SQL versus open standards languages such as Java.
http://www.oracle.com/technology/books/pdfs/mensah_ch1.pdf
Enjoy and share your thoughts, Kuassi