Skip site navigation (1) Skip section navigation (2)

Re: Aggregate function: Different results with jdbc and psql

From: Tilman Rassy <rassy(at)math(dot)TU-Berlin(dot)DE>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Aggregate function: Different results with jdbc and psql
Date: 2008-10-07 12:30:40
Message-ID: 200810071430.40769.rassy@math.tu-berlin.de (view raw or flat)
Thread:
Lists: pgsql-jdbc
Hello,

On Tuesday 07 October 2008 14:01, you wrote:
> Tilman Rassy <rassy 'at' math.TU-Berlin.DE> writes:
> > I have the following problem: A certain query, i.e.,
> >
> > SELECT * FROM user_worksheet_grades WHERE user_id = 23 AND worksheet_id =
> > 105;
> >
> > gives
> >
> >  worksheet_id | user_id | num_edited | num_corrected | grade
> > --------------+---------+------------+---------------+-------
> >           105 |      23 |          1 |             1 |     4
> > (1 row)
> >
> > when I issue it in psql. When I use the same query from JDBC in Java, the
> > column "grade" is SQL NULL.
>
> Can you show the Java code (to the list)?

Yes, here it is:

  public ResultSet queryUserWorksheetGrade (int userId, int worksheetId)
    throws SQLException
  {
    final String METHOD_NAME = "queryUserWorksheetGrade";
    this.logDebug
      (METHOD_NAME + " 1/3: " + "Started" + ". " +
       " userId = " + userId +
       ", worksheetId = " + worksheetId);
    this.sqlComposer
      .clear()
      .addSELECT()
        .addAsterisk()
      .addFROM()
        .addTable(DbTable.USER_WORKSHEET_GRADES)
      .addWHERE()
        .addColumn(DbColumn.USER_ID) .addEq() .addValue(userId)
      .addAND()
        .addColumn(DbColumn.WORKSHEET_ID) .addEq() .addValue(worksheetId);
    String query = this.sqlComposer.getCode();
    this.logDebug(METHOD_NAME + " 2/3: " + "query = " + query);
    ResultSet resultSet = 
this.connection.createStatement().executeQuery(query);
    this.logDebug(METHOD_NAME + " 3/3: " + "Done");
    return resultSet;
  }

A few remarks: sqlComposer is an auxiliary object to compose SQL code. The SQL 
is written to a log message before it is executed. When I copy the SQL from 
the logs and paste it to psql, I get the result above. But in Java, "grade" 
is NULL. I tested it with "wasNull".

I also tried variants of the above. In any case, all columns except "grade" 
are correct. This is why a thought the problem is related to the "sum" 
aggregate function. The use of views seems to have no influence. The problem 
occurs even if no views are involved.

> Are you sure you connect to the database with the same
> user/password with psql and JDBC?

User is te same, password is not needed with psql.

Tilman




In response to

Responses

pgsql-jdbc by date

Next:From: Tilman RassyDate: 2008-10-07 12:33:17
Subject: Re: Aggregate function: Different results with jdbc and psql
Previous:From: Tom LaneDate: 2008-10-07 12:21:01
Subject: Re: Aggregate function: Different results with jdbc and psql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group