please help: PostgreSQL JDBC Pooling problem

Dear list,

I'm having a couple problems while implementing a JDBC Connection pool using
Postgresql Jdbc3PoolingDataSource.

I was able to reproduce the errors with a simple java program. I'll be
grateful if someone could analyse this problem for a while...

The class io_property is a local class using standard java Properties
classes to load data from a simple text file. This is done when calling
the io_property.startService() static method.

If I run the program exactly as show below, it works wonderfully. However,
when commenting the return line of openConnection and uncommenting the
previous 3 lines as:

this.loConnection = loPoolSource.getConnection();
System.out.println("loConnection = " + this.loConnection);
loStatement = this.loConnection.createStatement();
System.out.println("Statement = " + loStatement);
return loStatement;
// return this.loConnection.createStatement();

The JDBC Statement returned is null. That's the problem I'm facing with my
main application...

Why is this happening?? In which circumstances
does Connection.createStatement() return null???

My main application is a bit different... It uses threads and lots of SQL
code. I have to return a Statement instead of a Connection for historical
reasons...

Any help is welcome. The source file follows...

Marcus

===============================================================================

public class con_database {

static Jdbc3PoolingDataSource loPoolSource = null;
private Connection loConnection = null;

con_database() {
io_property.startService();
}

private static void setPool() {
// DataSource initialization
System.out.println("setPool()");
try {
System.out.println("setting pool");
// constructs a pool only when loPoolSource is not assigned.
// this will prevent problems if the same pool is set more than
// one time
if (loPoolSource == null) {
loPoolSource = new Jdbc3PoolingDataSource();
// DataSource configuration.
loPoolSource.setDataSourceName(io_property.DATABASE_POOL_DATA_SOURCE_NAME); //
any name is valid here
loPoolSource.setServerName(io_property.DATABASE_POOL_SERVER_NAME);
loPoolSource.setPortNumber(io_property.DATABASE_POOL_SERVER_PORT);
loPoolSource.setDatabaseName(io_property.DATABASE_POOL_DATABASE_NAME);
loPoolSource.setUser(io_property.DATABASE_POOL_USER_NAME);
loPoolSource.setPassword(io_property.DATABASE_POOL_PASSWORD);
loPoolSource.setMaxConnections(io_property.DATABASE_POOL_MAXCONNECTIONS);

} else {
System.out.println("pool is set");
return;
}
} catch (Exception loException) {
// logger.logToFile(loException);
loException.printStackTrace();
}

}

public Statement openConnection() {
Statement loStatement;
System.out.println("opening con");
try {
// gets a connection from Pool
this.loConnection = loPoolSource.getConnection();
System.out.println("loConnection = " + this.loConnection);
// loStatement = this.loConnection.createStatement();
// System.out.println("Statement = " + loStatement);
// return loStatement;
return this.loConnection.createStatement();

} catch (Exception loException) {
loException.printStackTrace();
}

return null;
}

public void closeConnection() {
System.out.println("closing con");
try {
if (this.loConnection != null) this.loConnection.close();
} catch (Exception loException) {
loException.printStackTrace();
}
}

public static void main(String args[]) {

Statement loStatement = null;
con_database loPoolConnection = new con_database();
loPoolConnection.setPool();
loStatement = loPoolConnection.openConnection();
ResultSet lors = null;
try {
lors = loStatement.executeQuery("select * from <tablename>");
while (lors.next()) {
System.out.println(lors.getString(1));
}
} catch (Exception e) {
e.printStackTrace();
}
finally {
try {
lors.close();
loStatement.close();
loPoolConnection.closeConnection();
} catch (Exception loException) {
loException.printStackTrace();
}
}

loPoolConnection = new con_database();
loPoolConnection.setPool();
loStatement = null;
loStatement = loPoolConnection.openConnection();
lors = null;
try {
lors = loStatement.executeQuery("select * from <table_name>");
while (lors.next()) {
System.out.println(lors.getString(2));
}
} catch (Exception e) {
e.printStackTrace();
}
finally {
try {
lors.close();
loStatement.close();
loPoolConnection.closeConnection();
} catch (Exception loException) {
loException.printStackTrace();
}
}
}
}

-------------------------------
http://www.vlinfo.com.br