Oracle9iAS Portal Developer Kit
Installing the DBPreferenceStore Sample (V2)

The DBPreferenceStore sample provider illustrates the use of an alternative implementation of the PreferenceStore interface, DBPreferenceStore, to maintain portlet customization data in a relational database. In order for the DBPreferenceStore to be able to service multiple requests in parallel without suffering from performance overheads, it makes use of a pooled database connection provided by the J2EE server. This article describes how to configure your environment in order to run the sample, and should also be useful to anyone who plans to use DBPreferenceStore in portlets of their own.

ASSUMPTIONS

1. You have already installed the JPDK and core samples and understand the steps required to display a Web portlet on an Oracle Portal page. For more information, please review the article Installing the PDK-Java Framework and Samples.
2. You have a relational database at your disposal, and have access to the client software required to connect to the database interactively (e.g. `sqlplus` on Oracle).
3. You have a compatible JDBC driver installed in your webserver's classpath. The JDBC driver must support a mapping to the `java.sql.Types.LONGVARBINARY` type (e.g. the `LONG RAW` type in Oracle). See the documentation for your JDBC driver for more information. For best results, we recommend use of an Oracle database, version 8.1.6 or later, for which appropriate JDBC drivers can be downloaded from the 'Downloads' area of the Oracle Technology Network site. If you are using OC4J Server, then the drivers should come pre-installed (look for `classes12.zip` in `j2ee/home/lib`).

CONFIGURING THE DATABASE

This section describes how to create the table required by the DBPreferenceStore class.

1. Set the current directory to be the `doc/dbPreferenceStore` directory under the JPDK root directory, e.g.:

   `cd D:\jpdk\doc\dbPreferenceStore`
   ``

2. Connect to your database as the user who you wish to own the DBPreferenceStore table. Make sure this user has the necessary privileges to create tables and indexes. E.g.:

   `sqlplus scott/tiger@mydb`
   ``

3. Create a table `JPDK_PREFERENCE_STORE2` with the following design:

   Column Name | XOPEN Datatype | Length | Mandatory | In Primary Key?
   --- | --- | --- | --- | ---
   `REFERENCE_PATH` | `VARCHAR` | 250 | Yes | Yes
   `PATH_TYPE` | `VARCHAR` | 60 | Yes | Yes
   `SEARCH_PATH` | `VARCHAR` | 250 | Yes | No
   `DATA_CLASS` | `VARCHAR` | 250 | No | No
   `LONG_VALUE` | `LONGVARBINARY` |  | No | No

   For best performance, you should also create indexes on the `PATH_TYPE` and `SEARCH_PATH` columns.

   To quickly set up the `JPDK_PREFERENCE_STORE2` table on an Oracle database, you simply need to run the `jpdk_preference_store2.sql` script, e.g. by typing the following in `sqlplus`:

   > `@jpdk_preference_store2`

   For other databases, the second column in the table above gives for each database column the XOPEN standard name used by JDBC (e.g. in java.sql.Types) to denote its datatype. Your database vendor may use different keywords to denote these types, e.g. the Oracle JDBC drivers map `VARCHAR` to the `VARCHAR2` Oracle type, and `LONGVARBINARY` to the `LONG RAW` Oracle type. Consult the documentation for your JDBC drivers to determine the correct type keywords to use.

CONFIGURING A DATASOURCE

You need to set up a data source for the pooled connection to the database account containing the `JPDK_PREFERENCE_STORE2` table with the JNDI path `jdbc/PooledConnection`. On OC4J, this can be achieved by adding an entry such as the following to `data-sources.xml` in `j2ee/home/config`. You should edit the highlighted line so that it contains a valid JDBC URL to your database server. This should be of the form jdbc:oracle:thin:@<host name>:<port number>:<SID>. If you are not sure what the host name, port number and SID for your server are, you can use the Net8 Assistant to look up these settings.

`<data-source class="com.evermind.sql.DriverManagerDataSource" location="jdbc/UnPooledConnection" xa-location="jdbc/xa/XAConnection" ejb-location="jdbc/PooledConnection" connection-driver="oracle.jdbc.driver.OracleDriver" username="scott" password="tiger" url="jdbc:oracle:thin:@my.host.com:1521:orcl" inactivity-timeout="30" />`

You should now be able to register the DBPreferenceStore sample provider on a portal using the service id `urn:dbpref`. Consult the PDK-Java Installation guide if you are unsure how to do this.

Once registered, try adding some of the provider's portlets to a page and then customizing those portlets, e.g. by editing the portlet's title. This time, any customizations you make will be stored in the table you configured in your database.

Revision History:

- April 26, 2001

- January 18, 2002: First revision for V2