Assuming you have already installed the necessary, interrelated HTP packages, download the latest version of DB Prism. As well as the all-important Prism.jar file, all the .java source code files are downloaded too. Once you have upacked the download, you can find these under .. /src/com/prism, with the important Oracle classes under .. /src/com/prism/oracle. We downloaded DB Prism 1.1.0 and unpacked it to form the /home/oos/apache/prism base directory (following on from our Unix Apache JServ install in the last chapter).

Shut down Apache JServ if it is currently running:

$ apachectl stop

Make sure that the following lines exist in the /home/oos/apache/conf/jserv.conf file, both to pick up jserv.properties later and to set up our servlet zones, which we’ll be using to drive our PL/SQL programs:

ApJServProperties
/home/oos/apache/conf/jserv.properties
ApJServMount /servlets /root
ApJServMount /servlet /root

Similarly, ensure that the root zone itself is covered in jserv.properties, with its own properties file pointer:

zones=root
root.properties=/home/oos/apache/conf/root.properties

In jserv.properties, you also need to ensure that the new Prism.jar servlets file is properly accessed by CLASSPATH, to get hold of the main ServletWrapper.class embedded file later (that program encapsulates our PL/SQL):

wrapper.classpath=/home/oos/apache/prism/bin/Prism.jar

Next, we move on to the root.properties file, which we’ve just pointed to. Create this by copying it from a default servlets.properties file (or the example.properties file from the last chapter), and then make sure that the following line occurs somewhere within it:

servlet.plsql.code=com.prism.ServletWrapper

This will wrap up the intended database PL/SQL output within the ServletWrapper.class file (which resides in Prism.jar).

Also, point to another file, which we’re just about to create. The prism.properties entry will shortly hold the PL/SQL DADs (Database Access Descriptors). These are the sets of configured information used to direct DB Prism to the correct schema of a target database:

servlet.plsql.initArgs=properties=/home/oos/apache/conf/prism.properties

Copy a default prism.properties file from the DB Prism install directories. This creates your new prism.properties file in your own local Apache JServ setup:

$ cd /homs/oos
$ cp apache/prism/conf/prism.properties apache/conf/prism.properties

Edit this new prism.properties file, first setting three highly important global variables, as we’ll describe below.

First, any Java error reporting should go to yourself, rather than to Marcelo Ochoa (this gets you extra bonus points, especially, we suspect, from Marcelo Ochoa):

global.webmaster=my_name@my_corp.com

Second, set the global.behavior variable to zero:

global.behavior=0

Later on, if you should experience connection problems with your PL/SQL DADs, you may want to change global.behavior to either 1 or 2. This makes DB Prism parse your web addresses slightly differently and look for different DADs as a result. For example, with a web address of .. /servlet/plsql/myprocedure and a global.behavior setting of 1, DB Prism may extract a connection descriptor of “servlet”, which then returns a database connection error (because we won’t be creating a DAD for it). On the other hand, with a global.behavior value of 0, parsing the same web address can return a connection descriptor of “plsql”, which is exactly what we are after. Different systems will vary in their response to this flag, so just try the full range of the three variable values (0, 1, or 2), until one of them works out for you.

Third, set global.alias to point DB Prism at the soon-to-be-created “plsql” DAD values:

global.alias=plsql

Having created the “plsql” alias listing, we now need to fill in the actual DAD database details for it. The following paired value sets, aimed at our usual target database, worked for us:

plsql.dbusername=scott
plsql.dbpassword=tiger
plsql.connectString=jdbc:oracle:thin:@localhost:1521:ORCL
plsql.errorLevel=2
plsql.errorPage=http://localhost:8080/error.html
plsql.toolkit=4x
plsql.compat=8i
plsql.producerarg=pass
plsql.dbcharset=8859_1
plsql.clientcharset=8859_1

We note the most important of these here:

Finally, start up Apache JServ again:

$ apachectl start

First of all, shut down Apache JServ again:

$ apachectl stop

In /home/oos/apache/conf/jserv.conf, find the following line:

#ApJServAction .xml /dev/org.apache.cocoon.Cocoon

Add the following lines. These point Cocoon at the right web addresses, including its XSL content generation:

Action cocoon /servlet/org.apache.cocoon.Cocoon
AddHandler cocoon xml
Alias /xsl/ /home/oos/apache/prism/xsl/

Edit the jserv.properties file, adding the following lines to CLASSPATH (watch out for version number changes between different versions of Cocoon, especially on fop_0_15_0.jar). Also make sure the Xerces .jar file comes before cocoon.jar (this works around certain DOM presentation-level problems caused by having these the wrong way around):

wrapper.classpath=/home/oos/apache/cocoon-1.8.2/lib/xerces_1_2.jar
wrapper.classpath=/home/oos/apache/cocoon-1.8.2/bin/cocoon.jar
wrapper.classpath=/home/oos/apache/cocoon-1.8.2/lib/fop_0_15_0.jar
wrapper.classpath=/home/oos/apache/cocoon-1.8.2/lib/xalan_1_2_D02.jar
wrapper.classpath=/home/oos/apache/cocoon-1.8.2/lib/turbine-pool.jar

Next, edit /home/oos/apache/cocoon-1.8.2/conf/cocoon.properties and add the following lines to link up Cocoon to its database content access via DB Prism (for this book, we’ve had to split the longer lines across two, but each should be specified on one long line):

producer.type.db = com.prism.CocoonWrapper
producer.db.properties = /home/oos/apache/conf/prism.properties
processor.type.http = com.prism.HeaderProcessor
formatter.type.application/vnd.ms-excel =
    org.apache.cocoon.formatter.TextFormatter
formatter.application/vnd.ms-excel.MIME-type =
application/vnd.ms-excel
processor.xsp.logicsheet.connection.java =
    resource://com/prism/xsp/connection.xsl

Also change the following line:

producer.default = file

to:

producer.default = db

Now that we’ve altered cocoon.properties, we need to pick it up in the DB Prism/JServ system. Do this by editing /home/oos/apache/conf/root.properties. Look for the following line:

#servlet.org.apache.cocoon.Cocoon.initArgs=properties=
/usr/local/prism/conf/cocoon.properties

Replace this with:

servlet.org.apache.cocoon.Cocoon.initArgs=properties=
/home/oos/apache/cocoon-1.8.2/conf/cocoon.properties

Also in this root.properties file, find the line:

servlet.plsql.code=com.prism.ServletWrapper

Beneath this, to run the DB Prism XML demos later, add the following:

servlet.demo.code=com.prism.ServletWrapper
servlet.xml.code=org.apache.cocoon.Cocoon
servlet.xmld.code=org.apache.cocoon.Cocoon

Also look for the line we added earlier in the basic DB Prism install:

servlet.plsql.initArgs=properties=
    /home/oos/apache/conf/prism.properties

Beneath this, add the following lines:

servlet.demo.initArgs=properties=/home/oos/apache/conf/prism.properties
servlet.xml.initArgs=properties=
/home/oos/apache/cocoon-1.8.2/conf/cocoon.properties
servlet.xmld.initArgs=properties=
/home/oos/apache/cocoon-1.8.2/conf/cocoon.properties

We now need to move on to the prism.properties file and add some new DADs. First, update the global.alias value from:

global.alias=plsql

to:

global.alias=plsql demo xml xmld org.apache.cocoon.Cocoon

Once this is done, add in the new DADs. The following are the lines we used; they are essentially copies of the “plsql” DAD set up earlier, with one or two slight amendments (you’ll find most of these already preset, except for the JDBC URLs):

#DAD demo
demo.dbusername=scott
demo.dbpassword=tiger
demo.connectString=jdbc:oracle:thin:@localhost:1521:ORCL
demo.errorLevel=2
demo.errorPage=http://localhost:8080/error.html
demo.compat=8i
demo.toolkit=4x
demo.producerarg=pass
demo.StateLess=false

#DAD xml
xml.dbusername=scott
xml.dbpassword=tiger
xml.connectString=jdbc:oracle:thin:@localhost:1521:ORCL
xml.errorLevel=2
xml.errorPage=http://localhost:8080/error.html
xml.compat=8i
xml.toolkit=4x
xml.producerarg=ignore

#DAD xmld, use with demos to show dynamic login
xmld.connectString=jdbc:oracle:thin:@localhost:1521:ORCL
xmld.errorLevel=2
xmld.errorPage=http://localhost:8080/error.html
xmld.compat=8i
xmld.producerarg=ignore
xmld.toolkit=4x

Getting away from JServ configuration for a short while, edit the /home/oos/apache/prism/plsql/demo.sql program, making sure that the final few lines contain the URL you require. In our case, this is:

BEGIN
  -- where can I find xml stylesheets
  url_server := 'http://localhost:8080';
END demo;

You may also want to alter the modifiable authorize( ) function to be something simpler, like this:

function authorize return boolean is
begin
   return true;
end;

You can modify this to something more rigorous when you move into production.

We now need to add some new tables under the SCOTT/TIGER schema. If you can’t find the Oracle Corporation summit2.sql demo script under your own Oracle OWAS/iAS distribution, get hold of a similar SQL script from the following site:

Download this SUMMIT2.SQL file to provide the database object and insert statements required by a DB Prism demonstration later. Once you’ve checked that the SQL in this file is acceptable, install summit2.sql (or SUMMIT2.SQL) onto the SCOTT/TIGER schema.

Now we run an xtp.sql SQL file, also provided by DB Prism within its .. /prism/plsql directory. This installs the XML procedures required by DB Prism. This is followed, in turn, by demo.sql (recently altered), which makes immediate use of the XTP calls provided by xtp.sql:^[50]

SQL> @xtp
Installing DB Prism Xml Toolkit Procedures (xtp)
Package created.
Package created.
Package body created.
Package body created.
SQL> @demo
Installing DB Prism Xml Demos
Package created.
Package body created.
SQL>

The penultimate step is to fire up Apache JServ (the ultimate step is to enjoy a well-deserved drink!):

$ apachectl start