JavaPostgres95 v0.6

Frequently Asked Questions

Updated August 2 1997

This FAQ contains hint's and tips on using the JavaPostgres95 JDBC driver with the PostgreSQL database engine.

Index

Examples

Performing simple queries via JDBC
Performing updates via JDBC
A really simple psql frontend in Java

Things To Do

Accessing large objects stored on the database
Accessing Unicode strings stored on the database
Using the copy function of postgresql

What version of the JDK/JRE is required for the driver?

Versions 0.3 and higher require the 1.1 API or later. The developement of the driver was done using JDK 1.1.1 under Linux.

Can the driver run on the old 1.0.2 API?

Only version 0.2 of the driver will run under the 1.0.2 VM. This driver is, however, unsupported.

What versions of PostgreSQL are supported by the driver?

Versions prior to 0.4 of the driver support versions 6.0 and earlier of Postgres.
Version 0.5 was a development release, and was only distributed to those who requested it.
Versions 0.6 and later supports 6.1.1 and later of PostgreSQL.

Although version 0.4 will work with postgresql 6.1.1, it supports only unauthenticated connections. Password authentication was introduced in version 0.6.

I cannot connect to my PostgreSQL 6.1 server

This is caused by a bug in the 6.1 code (basically it doesn't handle the endianess correctly). Upgrade to 6.1.1, which has more features to it, and is more stable.

How to install the driver

To use the driver, simply place the postgresql.jar file into your classpath. That can be in the current directory, into a directory you have setup for third party class libraries (advised), or in the lib directory of the JDK/JRE distribution (unadviseable).
Once copied, add the file name to the classpath.

Example: On linux

export CLASSPATH=postgresql.jar

Using the driver

Because the driver uses the JDBC API all you need to do is ensure that the postgres95.PGDriver class is loaded. There are several ways of doing this. They are:

The Java application asks you for the driver's class name. In this case, you enter postgres95.PGDriver JExplorer is an example of this.
You implicitly load the class in your application/
You load the driver via parameters.

Loading the driver in your application

To load the driver in your own applications, you simply need the following line:

Class.forName("postgres95.PGDriver");

This method has the disadvantage that it hardwires your application to this driver.

Loading the driver via parameters

Any Java application can have parameters passed to it on startup, either from the command line, or from a parameter file.

From the source of postgres95.PGDriver:

// This allows the driver to be loaded using properties.
//
// example:
//
// 1 In any application that wants to make use of the driver, set the
//    system properties up. Either with a
//
//            -Djdbc.drivers=postgres95.PGDriver
//
// 2 or do this
//
//    static {
//      Properties sysProps = System.getProperties();
//      sysProps.put("jdbc.drivers","gnu.postgres95.PGDriver");
//      System.setProperties(sysProps);
//    }

Take a look at the test.psql example application for an example of this.

How to setup Password Authentication

Boy, was this a pig to get working. I will say this now - If this is documented elsewhere, and is easier to do than this, please email me.

Ok, there's two steps in using password authentication, one on the server, and then on the JDBC end.
The second is real easy, you simply replace postgres95 in the jdbc url to postgresql. This is enough to tell the driver to use the different protocol.

The first step is the hard one.

The first thing to do is configure the pg_hba.conf file that's located in the PGDATA directory. Here's an example:

# TYPE DATABASE IP_ADDRESS MASK USERAUTH MAP

host all 127.0.0.1 255.255.255.255 password my.pwds

host all 192.0.0.0 255.255.255.0 password my.pwds

This is infact from my pg_hba.conf file.

As you can see, the USERAUTH field is set to password, and the field MAP contains my.pwds which is a file that contains the passwords. This is also stored in the PGDATA directory.

Now to create the password file. This is a simple file containing one line per user. Heres an example:

postgres:papAq5PwY/QQM

Here the papAq5PwY/QQM is the encrypted password (which here is password).

I couldn't find any utility that would produce the encrypted password (the /etc/passwd file uses a different method to how postgres does), so here is the source for a simply utility that generates the relevent line:

#include <stdio.h>

int main(int argc,char **argv)
{
char salt[3];

if(argc!=3) {
fprintf(stderr,"pscript user password\n");
exit(1);
}

strncpy(salt, argv[2], 2);
salt[2] = '\0';

printf("%s:%s\n",argv[1],crypt(argv[2],salt));
}

Compile this using:

gcc -o test/pscript test/pscript.c

and run using:

test/pscript user password

I know it's C and not Java, but Java doesn't have an equivalent of the crypt function.

What is the syntax of the JDBC URL?

The driver recognises several forms of URL, based around the following template:
jdbc:driver://host:port/database?option=value&user=userid&password=password

Here's an explanation of each field:

Field Purpose

driver The JDBC driver name.
Here, this can be either postgres95 or postgresql.
postgres95 indicates that you want to connect without authentication.
postgresql indicates that you want to connect using password authentication.

host The host running the server. If this is absent, then it assumes localhost

port The port that the server is listening to. If absent, it defaults to 5432

database The database name

option=value Options about the database backend. These can be left out

userid The userid. If this is leftout, then you need to pass it through the DriverManager.getConnection(url,userid,password) call.

password The password.

Field	Purpose
driver	The JDBC driver name. Here, this can be either postgres95 or postgresql. postgres95 indicates that you want to connect without authentication. postgresql indicates that you want to connect using password authentication.
host	The host running the server. If this is absent, then it assumes localhost
port	The port that the server is listening to. If absent, it defaults to 5432
database	The database name
option=value	Options about the database backend. These can be left out
userid	The userid. If this is leftout, then you need to pass it through the DriverManager.getConnection(url,userid,password) call.
password	The password.

Heres some examples:

jdbc:postgres95:pgtest?user=postgres&password=pass Database pgtest on the local machine, using the userid postgres
Because we are using no-authentication, the password pass is ignored.

jdbc:postgresql:pgtest?user=postgres&password=pass As above, but use password authentication.

jdbc:postgresql://myhost/pgtest?user=postgres&password=pass Connect to database pgtest on server myhost, using password authentication with user postgres, password pass.

jdbc:postgres95://localhost:8000/pgtest?user=postgres&password=pass Connect to the server listening on port 8000

You can access the database when the application is on the same machine, but not over the network

This is caused by postgres not allowing connections from remote hosts. This ability is defined in the pg_hba.conf file (stored in the PGDATA directory).
Add an entry for the remote host or network into that file.
For example:

# TYPE DATABASE IP_ADDRESS MASK USERAUTH MAP

host all 127.0.0.1 255.255.255.255 password my.pwds

host all 192.0.0.0 255.255.255.0 password my.pwds

host all 192.0.1.0 255.255.255.0 trust

Here, the first line allows password authenticated connections from the local machine. It also allows password authenticated logins from hosts that are on the 192.0.0.x network.

Hosts on the 192.0.1.x network don't need a password.

Page last updated August 2 1997

jdbc:postgres95:pgtest?user=postgres&password=pass	Database pgtest on the local machine, using the userid postgres Because we are using no-authentication, the password pass is ignored.
jdbc:postgresql:pgtest?user=postgres&password=pass	As above, but use password authentication.
jdbc:postgresql://myhost/pgtest?user=postgres&password=pass	Connect to database pgtest on server myhost, using password authentication with user postgres, password pass.
jdbc:postgres95://localhost:8000/pgtest?user=postgres&password=pass	Connect to the server listening on port 8000