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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tilman Rassy | 2008-10-07 12:33:17 | Re: Aggregate function: Different results with jdbc and psql |
Previous Message | Tom Lane | 2008-10-07 12:21:01 | Re: Aggregate function: Different results with jdbc and psql |