JavaServer Pages: running the examples on a postgres database

The JSP Book examples with Tomcat-Postgresql.

This worked for me with

OpenBSD 2.8
Postgresql 7.0.2
Tomcat 3.2.1 (binary version)
A pre-compiled jdbc driver from http://www.retep.org.uk/postgres/.

I haven't included any detail on installing Tomcat, the binary makes it very easy.
Installing Postgresql needs more explanation than I have space for so check the documentation that came with your postgresql version.

Install postgresql according the the documentation.
Do all the post install setup and run the postmaster.
Compile the jdbc driver included in the postgresql distribution OR download the pre-compiled version if you are really stuck.
If you downloaded the compiled jdbc driver, rename it to postgresql.jar. Move it as is into $TOMCAT_HOME/lib .
Create a user for the examples.
You must do this as a postgresql user already recognised by the database and with permissions to create other users.
```
[arkwright:~] createuser
Enter name of user to add: jspuser
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) y
CREATE USER
    
```
The permissions given to this user are irrelevant for our purposes.

As jspuser, create a database called example.

[arkwright:~] createdb -U jspuser example
CREATE DATABASE

Create the tables as the user jspuser.
The file jspbook.sql can be downloaded here and is shown at the bottom of the page.

[arkwright:~] psql -U jspuser -f jspbook.sql example
psql:jspbook.sql:9: NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'user_pk' for table 'employee'
CREATE
CREATE
CREATE

Check the tables in the database:-

[arkwright:~] psql example
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
example=# \dt
List of relations
Name       | Type  | Owner 
------------------+-------+-------
employee         | table | jspuser
employeeprojects | table | jspuser
inputtest        | table | jspuser
(3 rows)

Now the database is set up, we must edit the jsp pages to reflect the correct DB connection..
The only action you wish to perform on the JSP's in to replace each occurance of
```
<ora:useDataSource id="example"
className="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:example" />
```
with
```
<ora:useDataSource id="example" 
  className="org.postgresql.Driver" 
  url="jdbc:postgresql:example?user=jspuser&password=8231" />
```
So, move the the directory ora.The subdirectories from here (ch9, ch10 etc) contain the JSP pages with the default driver details.
```
[arkwright:~]cd $TOMCAT_HOME/webapps/ora/
    
```
I did a rather ugly perl-on-the-command-line trick although there are many ways to do a search and replace.. whichever you prefer. =)
```
perl  -pi.bak  -e 's/sun.jdbc.odbc.JdbcOdbcDriver/org.postgresql.Driver/g;
s/jdbc:odbc:example/jdbc:postgresql:example?user=jspuser&password=8231/g;' 
`grep -rl sun.jdbc.odbc.JdbcOdbcDriver *`
```
I've truncated this for readability but it must be one whole line.
This will find each occurence of the default database driver and replace the text with our new postgres one.

Altered files will be backed-up with the suffix .bak
Now the jdbc should be in the correct place and the database handle is properly set.
Be sure to shutdown.sh and startup.sh the Tomcat server.
Use the examples from Chapter 9 to test.

That's it! The following is the sql for the "example" database.

create table Employee
( UserName varchar(50) constraint user_pk primary key,
Password varchar(50),
FirstName varchar(50),
LastName varchar(50),
Dept varchar(50),
EmpDate date,
EmailAddr varchar(50),
ModDate date );

create table EmployeeProjects
( UserName varchar(50),
ProjectName varchar(50));

create table InputTest
( MyDate date,
MyNumber int);

The JSP Book examples with Tomcat-Postgresql.

Other notable problems