Re: please help: PostgreSQL JDBC Pooling problem

Barry,

Try to change the openConnection method as follows:

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;
}

With my system (Solaris x86, latest jdk 1.4 from Sun and postgresql 7.3.2) this
results in a NullPointer Exception when printing loStatement on the screen.

This seems to reproduce the error happening in my main application. The classes
simply can't connect to the database because all Statements that openConnection()
returns are null.

BTW, is this the correct manner to implement a Pooled DB in PostgreSQL and
a singleton class wrapped as a static attribute (loPoolSource) ??

Thanks again.

Marcus Andree

Quoting Barry Lind <blind(at)xythos(dot)com>:

> 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
> >
>

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