Re: please help: PostgreSQL JDBC Pooling problem

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(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
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>