please help: PostgreSQL JDBC Pooling problem - Mailing list pgsql-jdbc

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