Sunday, December 21, 2008

Adventures with Apex, part three: Setting up Apex with Tomcat and the DBPrism plugin

It is common knowledge that you can run Apex using either the Apache-based Oracle HTTP Server (with mod_plsql) or the Embedded PL/SQL Gateway (on 10g XE and 11g), but did you also know that there is a third alternative?

The DBPrism project is a free, open-source plugin for the Tomcat web server that, among other things, can function as a replacement for mod_plsql.

Side Note 1: Interestingly, Oracle itself is also developing the "Apex Listener", what I assume is based on similar [Java] technology. So by Apex 4.0 we will have even more deployment choices.

Side Note 2: My wish is that someone familiar with Microsoft's IIS should write a plugin to mimic mod_plsql on that platform as well, to make it easier to start using Apex in companies that have standardized on Microsoft technology (sneaking it in the back door, so to speak :-). UPDATE (AUGUST 2009): I decided to implement a free, open-source PL/SQL gateway for IIS myself. The Thoth Gateway runs all mod_plsql applications (including Apex applications) on Microsoft Internet Information Server (IIS) 6.0 or later.

Anyway, here are instructions for using Apex with Tomcat and DBPrism:

ON THE DATABASE SERVER

1. Download the latest version of Application Express from oracle.com

- http://www.oracle.com/technology/products/database/application_express/download.html

2. Install Apex into the database according to the instructions

- do not worry about configuring either the Embedded PL/SQL Gateway (DBMS_EPG) or Oracle HTTP Server,
- but make sure to unlock the apex_public_user schema and make a note of the password given to apex_public_user (you will need it to configure the JDBC connection on the web server)


ON THE WEB SERVER

Note: This could be the same physical machine as the database, if desired.

1. Download Java Runtime Engine (JRE) from sun.com

- install by running setup program
- create an environment variable called JRE_HOME and set it to the location where JRE was installed

2. Download Tomcat from tomcat.apache.org

- unzip to a folder, for example c:\program files\apache-tomcat-xxx (where xxx is version number)
- follow instructions in RUNNING.txt to start Tomcat and verify installation (http://localhost:8080)

3. Download the Oracle JDBC Thin driver from Oracle.com

- http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_111060.html
- get the "ojdbc5.jar" file

4. Download DBPrism from http://sourceforge.net/projects/dbprism/

- choose the "dbprism" package (not the "cms" package)
- unzip and get the "dpls.war" file
- put the dpls.war file into the "webapps" folder in Tomcat
- shutdown and restart Tomcat
- this will create a "dpls" folder under "webapps"
- put the "ojdbc5.jar" file from step 3 in the dpls\WEB-INF\lib folder
- edit the prism.xconf file
- under "variables", change the value of the "demo.db" to a valid jdbc connectionstring to the database
- under under category named "DAD_apex", change the "dbpassword" to the correct password of the "apex_public_user" (note that this was set during/after the installation of Apex)

- shutdown and restart Tomcat again

5. Configure Apex images/javascript for Tomcat

- Create a folder called "i" under "webapps" in Tomcat
- shutdown and restart Tomcat
- Extract the files in the "images" folder in the Apex installation zip file and copy it to the "i" folder

6. Verify that everything works

- Go to http://localhost:8080/dpls/apex (this should display the login page of the Apex environment)


Pretty cool, isn't it? :-)

7 comments:

Algis said...

Hello,
Works SUPER!
Thanks :)

Peter de Boer said...

Nice post!

I had a little problem with the security-constraints (defined in the web-xml), but after fixing/removing it, the DBPrism servlet works just fine.

This might be a good alternative for the embedded pl/sql gateway.

Regards,
Peter

Patrick Wolf said...

Really cool!

degzhaus said...

This is great, OraDude! We have been looking for an alternative to using the OracleHTTP server for quite some time.

Do you think this is ready for production? Would there be added security risk with the DBPrism webserver in production (as opposed to OracleHTTP)?

Thanks for the info, regardless!! This is very cool.

Marcelo Ochoa said...

@Jon:
DBPrism is aware of any security issue related to buffer overflows or any other C problems.
Also there are checking for SQL Injection, package exclusion list, and so on.
From the Java world, there is an strong checking related to not share any state between concurrent threads for example.
Also as an open source application you can check the code and have a lot of logging to see DBPrism execution.
And finally you can use Oracle Proxy user support integrated with JAAS authentication.
Best regards, Marcelo.

The Hendersons said...

How do you use proxy authentication with Apex? I tried setting useProxyUser to true but then I can no longer log into APEX. It prompts me with a login and never lets me in.

Jko said...

I have FORM Authentication demo when I browse