No results were returned by the query, select query.

[c.sp...@gmail.com]

Hi folks, I am not convinced this is a purely Sql2o issue, but I'm kind of at a loss and I'm hoping you can help me.  I'm using Java 8, Sql2o 1.5.4, and postgresql 9.5.3.

My UserService looks like:

public class UserService {
    private final PGService pgService;

    public UserService(PGService _pgs) {
        this.pgService = _pgs;
    }

    public User getUserById(int id) {
        String sql = "SELECT id, firstname, lastname, email, team_id teamId FROM users WHERE id = :id;--";
        User user;
        try (Connection c = pgService.getConnection()) {
            user = c.createQuery(sql)
                    .addParameter("id", id)
                    .executeAndFetchFirst(User.class);
        }
        return user;
    }
}

My user looks like:

public class User {
    private int id;
    private String firstname;
    private String lastname;
    private String email;
    private String passhash;
    private int teamId;
    /*getters and setters*/
}

My test looks like:

public class UserServiceTest {
    private static UserService service;

    @Before
    public void setUp() throws ConfigurationException, IOException {
        this.service = new UserService(new PGService());
    }

    @Test
    public void returnsBiffUser() {
        User biff = service.getUserById(3);
        assertTrue(biff != null && biff.getLastname() == "Biff");
    }
}

When I execute the SQL directly against the database I get the expected record.

When I run the UserServiceTest file, I get the following exception:

org.sql2o.Sql2oException: Database error: No results were returned by the query.

    at org.sql2o.Query$ResultSetIterableBase.<init>(Query.java:332)
    at org.sql2o.Query$10.<init>(Query.java:412)
    at org.sql2o.Query.executeAndFetchLazy(Query.java:412)
    at org.sql2o.Query.executeAndFetchFirst(Query.java:480)
    at org.sql2o.Query.executeAndFetchFirst(Query.java:469)
    at services.UserService.getUserById(UserService.java:24)
    at services.UserServiceTest.returnsBiffUser(UserServiceTest.java:25)
Caused by: org.postgresql.util.PSQLException: No results were returned by the query.
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:115)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
    at org.sql2o.Query$ResultSetIterableBase.<init>(Query.java:328)

Does anyone have any idea why that is happening and how to fix it?

As a side note, if I omit the -- at the end of the sql string I get the follow error:

org.sql2o.Sql2oException: Database error: ERROR: syntax error at or near "RETURNING"

I don't care so much about that last one, I just want to know why my code isn't working.

Thanks folks,

~Cliff

[Lars Aaberg]

Hi Cliff,

The "syntax error at or near RETURNING" is caused by an incompatibility between the way sql2o is handling automatically generated keys in the database and the postgres jdbc driver. When using postgres, that exception is thrown when sql2o checks if there was generated any keys in the db. The solution is to never check for keys, except when you explicitly expect there to be generated a key.

This is handled in sql2o by the PostgresQuirks class. So, when creating you Sql2o instance, use one of the constructor overloads that takes a Quriks instance as parameter:

Sql2o sql2o = new Sql2o(myDataSource, new PostgresQuirks());

That should fix it! 

And make sure to remove the '--' after your query.

Regards

Lars Aaberg 

--
You received this message because you are subscribed to the Google Groups "sql2o" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sql2o+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Cliff Spital]

Brilliant!  Thank you!

--
You received this message because you are subscribed to a topic in the Google Groups "sql2o" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sql2o/aXtyyN5gnso/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sql2o+un...@googlegroups.com.