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

Marcus,

I can't reproduce your problem.  When I run the modifed version of your
test (changes made so that it compiles and runs standalone) I don't have
any errors.  My test table has about 1000 rows in it and everything
works fine without any errors.

I am running the latest 7.3 build (109) against a 7.3.2 database using
the sun 1.4.1_01 jdk on Redhat8.

Is there something else I need to do to reproduce the problem?

thanks,
--Barry

Marcus Andree S. Magalhaes wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
import org.postgresql.jdbc3.Jdbc3PoolingDataSource;
import java.sql.*;

public class test29 {

    static Jdbc3PoolingDataSource loPoolSource = null;
    private Connection loConnection = null;
    private static final String DATABASE_POOL_DATA_SOURCE_NAME = "test";
    private static final String DATABASE_POOL_SERVER_NAME = "localhost";
    private static final int DATABASE_POOL_SERVER_PORT = 5432;
    private static final String DATABASE_POOL_DATABASE_NAME = "test";
    private static final String DATABASE_POOL_USER_NAME = "test";
    private static final String DATABASE_POOL_PASSWORD = "test";
    private static final int DATABASE_POOL_MAXCONNECTIONS = 30;


    test29() {
    }

    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(DATABASE_POOL_DATA_SOURCE_NAME); // any name is valid here
        loPoolSource.setServerName(DATABASE_POOL_SERVER_NAME);
        loPoolSource.setPortNumber(DATABASE_POOL_SERVER_PORT);
        loPoolSource.setDatabaseName(DATABASE_POOL_DATABASE_NAME);
        loPoolSource.setUser(DATABASE_POOL_USER_NAME);
        loPoolSource.setPassword(DATABASE_POOL_PASSWORD);
        loPoolSource.setMaxConnections(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;
    test29 loPoolConnection = new test29();
    loPoolConnection.setPool();
    loStatement = loPoolConnection.openConnection();
    ResultSet lors = null;
    try {
        lors = loStatement.executeQuery("select * from test");
        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 test29();
    loPoolConnection.setPool();
    loStatement = null;
    loStatement = loPoolConnection.openConnection();
    lors = null;
    try {
        lors = loStatement.executeQuery("select * from test");
        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();
        }
    }
     }
   }