Result set is empty but query works in Postgres

Question

I'm trying to get some results from Postgres into my Java application. As of now, I always used PreparedStatements with ResultSets to get data from my database in memory. But today my code refuses to work, although I use the same code as usual. Here is my piece of code:

PreparedStatement stmtExplicitCollections = this.originalGraph.getPreparedStatement(
    "(" +
    "SELECT ce1.collId " +
    "FROM quotient_summary_edges qse, collection_to_ec ce1, collection_to_ec ce2 " +
    "WHERE qse.summaryNodeIdSource = ce1.ecId AND qse.summaryNodeIdTarget = ce2.ecId " +
    "GROUP BY ce1.collId " +
    "HAVING COUNT(ce2.collId) = 1" +
    ") INTERSECT (" +
    "SELECT ce1.collId " +
    "FROM quotient_summary_edges qse, collection_to_ec ce1 " +
    "WHERE qse.summaryNodeIdSource = ce1.ecId AND qse.summaryNodeIdTarget IN (" + StringUtils.join(interestingEquivalenceClasses, ",") + ")" +
    ");");
log.info(stmtExplicitCollections.toString());
ResultSet rsExplicitCollections = stmtExplicitCollections.executeQuery();
ArrayList<Integer> explicitCollectionsIds = new ArrayList<>();
while (rsExplicitCollections.next()) {
    explicitCollectionsIds.add(rsExplicitCollections.getInt(1));
}
log.info(explicitCollectionsIds);

Here is the log:

2022-03-29 13:35:03 INFO  RecordsCollectionsComputation:307 - (SELECT ce1.collId FROM quotient_summary_edges qse, collection_to_ec ce1, collection_to_ec ce2 WHERE qse.summaryNodeIdSource = ce1.ecId AND qse.summaryNodeIdTarget = ce2.ecId GROUP BY ce1.collId HAVING COUNT(ce2.collId) = 1) INTERSECT (SELECT ce1.collId FROM quotient_summary_edges qse, collection_to_ec ce1 WHERE qse.summaryNodeIdSource = ce1.ecId AND qse.summaryNodeIdTarget IN (4,0,1,5))
2022-03-29 13:35:03 INFO  RecordsCollectionsComputation:313 - []

The ArrayList explicitCollectionsIds is empty/not filled and if(rsExplicitCollections.next()) returns false.

EDIT: Also, the query works in Postgres as shown below:

cl_xml=# (SELECT ce1.collId FROM quotient_summary_edges qse, collection_to_ec ce1, collection_to_ec ce2 WHERE qse.summaryNodeIdSource = ce1.ecId AND qse.summaryNodeIdTarget = ce2.ecId GROUP BY ce1.collId HAVING COUNT(ce2.collId) = 1) INTERSECT (SELECT ce1.collId FROM quotient_summary_edges qse, collection_to_ec ce1 WHERE qse.summaryNodeIdSource = ce1.ecId AND qse.summaryNodeIdTarget IN (4,0,1,5));

 collid
--------
      0
      1
(2 rows)

Can someone help me? Thanks in advance!

Looks fine. Database connection config issue. Chech that data is actually there (the same db) — Antoniossss
– Antoniossss, Commented Mar 29, 2022 at 11:23
Thanks for your reply! I have queries like this above and below this one and they work. They are all on the same database (one open connection). I will continue to look at it — Nelly Barret
– Nelly Barret, Commented Mar 29, 2022 at 11:28
@Antoniossss I updated my post with the query in Postgres: it works — Nelly Barret
– Nelly Barret, Commented Mar 29, 2022 at 11:31
But I can see different queries..... so this proof is worth of nothing. — Antoniossss
– Antoniossss, Commented Mar 29, 2022 at 11:31
I dont see nothing wrong with the code so it must be a data issue - different database maybe or something, or running query on uncommited changes or something. — Antoniossss
– Antoniossss, Commented Mar 29, 2022 at 11:48

queeg · Accepted Answer · 2022-03-29 11:26:07Z

3

It looks to me as if your application fires a query SELECT id FROM table, then receives an empty result set. No exception thrown.

That means the communication to the database is fine, query execution is fine but there is just no data found.

Stop looking at the code or changes in the network. Start looking at your database content. What is in the tables?

answered Mar 29, 2022 at 11:26

queeg

9,9502 gold badges26 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Nelly Barret Over a year ago

Hello, I updated my post with the whole query. I added the result that I got in Postgres: 2 rows. However the ResultSet seems to be empty. I'm open to any other check

queeg Over a year ago

There is no point in looking at your query without knowing the data in the tables.

Nelly Barret Over a year ago

I'm not sure that I get it. What do you want to see? The tables are filled by the application and the query works when ran in Postgres. However, the Result set is empty when I do the same query in a PreparedStatement in my application. Because my query works in Postgres, it should also work in my PreparedStatement, isn't it?

queeg Over a year ago

Then you need to figure out the differences between 'in postgres' and 'in my preparedstatement'. If you want others to take a look, give in-depth information what you are doing there. I would not even rule out user authorization issues at the moment.

Nelly Barret · Accepted Answer · 2022-03-29 12:06:42Z

0

I finally found the issue thanks to @Antoniossss! The problem was that I was working on uncommitted changes, i.e. the table collection_to_ec is not filled when I was executing the query (it is filled later)

answered Mar 29, 2022 at 12:06

Nelly Barret

1701 silver badge18 bronze badges

Collectives™ on Stack Overflow

Result set is empty but query works in Postgres

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related