# Oracle Application Server Portal Developer Kit (PDK)
Installing the DBPreferenceStore Sample (V2)

Last Updated: | August 10, 2003
Status: | Production
Version: | PDK Release 2, (9.0.2 and later)

## Overview

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 by typing the following in sqlplus:

> @jpdk_preference_store2

> For non-Oracle databases, the second column in the table above gives the type for each database column using 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. For example, 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 to use with your database.

## 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: |
Revision No | Last Update | Description
1.0 | April 26, 2001 | Created
2.0 | January 18, 2002 | Revised for v2 framework
2.1 | August 10, 2003 | Revised branding and applied PDK Stylesheet

Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA 94065, USA http://www.oracle.com/ | Worldwide Inquiries: 1-800-ORACLE1 Fax 650.506.7200 | Copyright and Corporate Info