Re: please help: PostgreSQL JDBC Pooling problem

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