Thread: 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
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();
}
}
}
}
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@xythos.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
Marcus,
OK, that reproduces. The stack traces shows that it is this line of
code that is causing the null pointer exception:
System.out.println("Statement = " + loStatement);
Specificly the toString() method on the loStatement object. It isn't
the fact that loStatement is null, but a logic error in the toString()
method. I don't yet know why the toString() method has a null pointer
exception, but in normal operations I wouldn't expect that code would be
calling toString on a Statement object.
thanks,
--Barry
Marcus Andree S. Magalhaes wrote:
> 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@xythos.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
>
Marcus,
The fix for this is pretty easy, just add the following to the beginning
of the toString() method of AbstractJdbc1Statement.java:
//if no sql yet set, return default toString()
if (m_sqlFragments == null)
return super.toString();
I will check in a fix for this soon.
thanks,
--Barry
Barry Lind wrote:
> Marcus,
>
> OK, that reproduces. The stack traces shows that it is this line of
> code that is causing the null pointer exception:
> System.out.println("Statement = " + loStatement);
>
> Specificly the toString() method on the loStatement object. It isn't
> the fact that loStatement is null, but a logic error in the toString()
> method. I don't yet know why the toString() method has a null pointer
> exception, but in normal operations I wouldn't expect that code would be
> calling toString on a Statement object.
>
> thanks,
> --Barry
>
> Marcus Andree S. Magalhaes wrote:
>
>> 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@xythos.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
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>