Re: Still having trouble getting JDBC working

There are 3 fundamental steps to establishing a jdbc connection.

1. Installation of postgresql.jar
---------------------------------
If the postgresql.jar file is not accessible to the Java runtime environment you
will get a 'java.lang.ClassNotFoundException: org.postgresql.Driver' exception.
If the reported class name is not "org.postgresql.Driver" see step 2.

The easiest way to make the postgresql jdbc driver accessible to your
applications is to install it as a Java Extension. The only thing you need to do
is cp, mv or ln the postgresql.jar file into the Java extension directory. There
is no need to set CLASSPATH if you do this.
The Java extension directory is $JAVA_HOME/jre/lib/ext ($JAVA_HOME on my system
is /usr/java/jdk1.3).

This makes life REALLY easy; I don't set CLASSPATH at all in my environment!

You do need to be running Java2 (jre1.2 or jre1.3) and have write access to the
extension directory.

Check out Sun's doco at :
http://java.sun.com/docs/books/tutorial/ext/
and
http://java.sun.com/products/jdk/1.2/docs/guide/extensions/

Failing that you need to set the CLASSPATH environment variable. The syntax for
this depends on your shell; I use tcsh (great for command line, lousy for
scripting) and would use
setenv CLASSPATH ${CLASSPATH}:/usr/local/lib/postgresql.jar
(if I needed to ;-)

2. Loading the Driver
---------------------
If the postgresql.jar file is installed correctly then a call to
Class.forName("org.postgresql.Driver");
should succeed. If not you have probably misspelled the driver name.
This will result in a 'java.lang.ClassNotFoundException: ogr.potsgresql.Driver'
exception.
One common cause of this is that the driver class name was changed from
'postgresql.Driver' to 'org.postgresql.Driver' (for 7.0 I think, anyone??).
The use of outdated documentation is hazardous to your sanity!

3. Creating the Connection
--------------------------
Creating the actual Connection object requires a valid url, username and
password. The code usually looks something like this :

String url = "jdbc:postgresql://hostname:portnumber/dbname";
String username = "fred";
String password = "secret";
Connection con = DriverManager.getConnection(url, username, password);

You can leave out the ':portnumber/' part of the url if you are connecting on
the default port(5432), and you can leave out the '//hostname:portnumber/' if
you are connecting to the local host on the default port.

An incorrect url can result in quite a variety of exceptions, most of which are
reasonably self-explanatory - here's a few examples

"jdbc:BOGUS://hostname:portnumber/dbname" gives
java.sql.SQLException: No suitable driver

"jdbc:postgresql://BOGUS:portnumber/dbname" gives
The connection attempt failed because java.net.UnknownHostException: BOGUS

"jdbc:postgresql://hostname:9999/dbname" gives
Connection refused. Check that the hostname and port is correct, and that the
postmaster is running with the -i flag, which enables TCP/IP networking.

"jdbc:postgresql://hostname:portnumber/BOGUS" gives
java.sql.SQLException: FATAL 1: Database "BOGUS" does not exist in the system
catalog.

and interestingly
"BOGUS:postgresql://hostname:portnumber/dbname"
works fine on my 7.0.3 system(!!) but
":postgresql://hostname:portnumber/dbname" gives
java.sql.SQLException: No suitable driver

Using the wrong username gives
java.sql.SQLException: FATAL 1: SetUserId: user 'BOGUS' is not in 'pg_shadow'

I haven't actually set up a password on my databases but I'm sure that an
incorrect password would result in as obvious an exception message as the
examples above :-)

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

Sorry for the long post but I hope you find it useful,

Have Fun,

John L.

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

Quoting Corey Mosher <corey(at)pgsql(dot)com>:

> Hi
> Can anyone help me with this problem? I have jdk1.2.2 installed on
> a freeBSD system. My class path in .cshrc is as follows:
>
> set CLASSPATH = (/usr/local/lib/postgresql.jar)
>
> I get this error:
>
> Exception caught.
> java.lang.ClassNotFoundException: org.postgresql.Driver
> Exception caught.
> java.sql.SQLException: No suitable driver
> Ok
>
> When running the following code (I replaced all the connection stuff
> with x's for privacy reasons):
>
> import java.io.*;
> import java.sql.*;
> import java.util.*;
>
> public class test{
>
> public static void main(String args[]){
> Connection conn; // holds database connection
> Properties prop = new Properties();
> prop.put("jdbc.drivers", "org.postgresql.Driver");
> System.setProperties(prop);
> try {
> Class.forName("org.postgresql.Driver"); // load database
> interface
> } catch(Exception exc){
> System.err.println("Exception caught.\n" + exc);
> }
> try {
> conn =
> DriverManager.getConnection("jdbc:postgresql://xxxxxxxx:5432/xxxxx",
> "xxxxx", "xxxxxx");
> } catch(Exception exc){
> System.err.println("Exception caught.\n" + exc);
> }
> System.out.println("Ok");
> //conn.close();
> }
> }
>
> Does the postgresql.jar file need any special permissions? Right now I
> have:
>
> -rw-r--r-- 1 root wheel 153145 Apr 16 08:53
> /usr/local/lib/postgresql.jar
>
> Any ideas?
>
> Thanks.
>
> Corey Mosher
>
> -----------------------------
> Hub.Org Networking Services
> 251 Main St.
> Wolfville, NS
> Canada
> B0P 1X0
> Email: corey(at)hub(dot)org
> Phone: (902) 542-3657
> -----------------------------
> PostgreSQL, Inc.
> 251 Main St.
> Wolfville, NS
> Canada
> B0P 1X0
> Email: corey(at)pgsql(dot)com
> Phone: (902) 542-0713
> -----------------------------
> Fax: (902) 542-5386
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo(at)postgresql(dot)org
>

-------------------------------------------------
This mail sent through IMP: www.netspace.net.au