Re: [QUESTIONS] the JDBC Driver

On Fri, 9 Jan 1998, Zed A. Shaw wrote:

> >Where did you download it?
>
> www.demo.co.uk told me to download it from www.postgresql.com which told me
> to down load it from ftp.postgresql.org where I got the rpm file for version
> 6.3.

http://www.demon.co.uk/finder/postgres? If so, its my old site (when the
driver wasn't part of the distribution). The page is still there as I
haven't got round to updating the entry on gamelan/developer.com (which
I'll do when 6.3 is out).

That's interesting that there's one for 6.3, as were still working on 6.3
and I didn't think that the rpm guys were packaging the latest stuff yet,
as it's not even in beta yet.

> >If it was ours, there should be a README file that contains how to install
> >and the url.
> Nope, no README file.

Argh, that makes the rpm useful as the readme contains details on how to
use it!

Anyhow, everything for the driver is included in the source in directory
src/interfaces/jdbc. Anyhow, one of the things being worked on for 6.3 is
fresh documentation, including a copy online, so this will be less of a
problem.

I've attached the current README file, most should still be relevent.

An early copy of the driver's docs (just the api stuff, no useage docs
yet) are currently available on:

http://www.demon.co.uk/finder/postgres/jdbc/packages.html

--
Peter T Mount  petermount@earthling.net or pmount@maidast.demon.co.uk
Main Homepage: http://www.demon.co.uk/finder
Work Homepage: http://www.maidstone.gov.uk Work EMail: peter@maidstone.gov.uk
This is a simple readme describing how to compile and use the jdbc driver.

This isn't a guide on how to use JDBC - for that refer to Javasoft's web site:

    http://www.javasoft.com

or the JDBC mailing list:

    jdbc@java.blackdown.org

    http://www.blackdown.org

---------------------------------------------------------------------------

COMPILING

To compile the driver, simply use make in the src/interfaces/jdbc directory.
This will compile the driver, and build a .jar file (Java ARchive).

REMEMBER: once you have compiled the driver, it will work on ALL platforms
that support the JDK 1.1 api or later.

That means you don't have to compile it on every platform. Believe me, I
still hear from people who ask me "I've compiled it ok under Solaris, but it
won't compile under Linux" - there's no difference.

PS: When you run make, don't worry if you see just one or two calls to javac.
    If, while compiling a class, javac needs another class that's not compiled,
    it will compile it automatically. This reduces the numer of calls to javac
    that make has to do.

Possible problems

You may see a message similar to:

postgresql/Driver.java:87: interface java.sql.Connection is an interface. It can't be instantiated.
    return new Connection (host(), port(), props, database(), url, this);

This is caused by not having the current directory in your CLASSPATH. Under
Linux/Solaris, unset the CLASSPATH environment variable, and rerun make.

---------------------------------------------------------------------------

INSTALLING THE DRIVER

To install the driver, the .class files have to be in the classpath. This can be
done in two ways:

1: create a directory "postgresql" (and it must be called this) in the current
   directory (or a directory in the class path), and copy all .class files
   into it.

2: copy the postgres.jar file into a directory, and add it to the classpath.

   ie: under LINUX/SOLARIS (the example here is my linux box):

    export CLASSPATH=.:/usr/local/lib/postgresql.jar:/usr/local/jdk1.1.1/lib/classes.zip

   note: in java, .zip and .jar files hold collections of classes.

---------------------------------------------------------------------------

USING THE DRIVER

To use the driver, you must introduce it to JDBC. Again, there's two ways
of doing this:

1: Hardcoded.

   This method hardcodes your driver into your application/applet. You
   introduce the driver using the following snippet of code:

    try {
      Class.forName("postgresql.Driver");
    } catch(Exception e) {
      // your error handling code goes here
    }

   Remember, this method restricts your code to just the postgresql database.

2: Parameters

   This method specifies the driver from the command line. When running the
   application, you specify the driver using the option:

    -Djdbc.drivers=postgresql.Driver

   eg: This is an example of running one of my other projects with the driver:

    java -Djdbc.drivers=postgresql.Driver finder.finder

   note: This method only works with Applications (not for Applets).
     However, the application is not tied to one driver, so if you needed
     to switch databases (why I don't know ;-) ), you don't need to
     recompile the application (as long as you havent hardcoded the url's).

---------------------------------------------------------------------------

JDBC URL syntax

The driver recognises JDBC URL's of the form:

    jdbc:postgresql:database

    jdbc:postgresql://host/database

    jdbc:postgresql://host:port/database

Also, you can supply both username and passwords as arguments, by appending
them to the URL. eg:

    jdbc:postgresql:database?user=me
    jdbc:postgresql:database?user=me&password=mypass

By default, the driver doesn't use password authentication. You can enable
this by adding the argument auth. ie:

    jdbc:postgresql:database?user=me&password=mypass&auth=password

or if passing the user & password directly via DriverManager.getConnection():

    jdbc:postgresql:database?auth=password

PS: Password authentication is enabled if the value of auth starts with 'p'.
    It is case insensitive.

As of postgresql 6.3, Ident (RFC 1413) authentication is also supported.
Simply use auth=ident in the url.

Also, as of 6.3, a system property of postgresql.auth is supported. This
defines the default authentication to use. The auth property overides this.

---------------------------------------------------------------------------

That's the basics related to this driver. You'll need to read the JDBC Docs
on how to use it.

POSTGRESQL SPECIFICS
--------------------

Date datatype:

The driver now supports US and European date styles (although it is currently
limited to postgres format).

Basically the US like to format their dates as mm-dd-yyyy, while in Europe,
we like to use dd-mm-yyyy. Postgres supports this by the DateStyle variable.
From psql, you can issue "set datestyle='european';" to set european style,
and "set datestyle='us';" to set the US format. You can see what the current
value for this with "show datestyle;".

The driver now issues the "show datestyle;" query when it first connects, so
any call to ResultSet.getDate() how returns the correct date.

One caveat though: if you change the datestyle from within JDBC, you must also
issue the "show datestyle" query. Without this, the driver will not know of
the change.

ie:
    Statement s = db.createStatement();
    ...
    s.executeUpdate("set datestyle='european'");
    s.executeUpdate("show datestyle");
    ..
    s.close();
    
            ------------------

JDBC supports database specific data types using the getObject() call. The
following types have their own Java equivalents supplied by the driver:

    box, circle, lseg, path, point, polygon

When using the getObject() method on a resultset, it returns a PG_Object,
which holds the postgres type, and its value. This object also supports
methods to retrive these types.

    Eg: column 3 contains a point, and rs is the ResultSet:

    PG_Object o = (PG_Object)rs.getObject(3);
    PGpoint p = o.getPoint();
    System.out.println("point returned x="+p.x+", y="+p.y);

Also, when using these classes, their toString() methods return the correct
syntax for writing these to the database.

---------------------------------------------------------------------------

Peter T Mount, October 28 1997
home email: pmount@maidast.demon.co.uk    http://www.demon.co.uk/finder
work email: peter@maidstone.gov.uk    http://www.maidstone.gov.uk

Adrian Hall
     email: adrian@hottub.org