Help! Can get query to work!

[Thierry Lévèque]

Hi am pretty new to jdbi.

I already wrote code to do some insert in a Postgres db without any problem.

Now for the first time I am trying to do a select query. I first wrote it using a ResultSetMapper. But I got an error when running.

I change my code do make it as simple as possible. Even using a jdbc url instead of a datasource, and I still get the same error!

Exception in thread "Thread-1" org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: org.postgresql.util.PSQLException: ERROR: syntax error at or near "RETURNING"

  Position: 33 [statement:"select id, name from subscriber", located:"select id, name from subscriber", rewritten:"select id, name from subscriber", arguments:{ positional:{}, named:{}, finder:[]}]

at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1364)

at org.skife.jdbi.v2.Query.list(Query.java:78)

at org.skife.jdbi.v2.BasicHandle.select(BasicHandle.java:331)

at com.imetrik.saas.server.insurance.service.datacrunchercontroller.dao.SubscriberDAODbImpl.getSubscriberForDataCruncher(SubscriberDAODbImpl.java:33)

at com.imetrik.saas.server.insurance.service.datacrunchercontroller.DataRequesterTask$1.run(DataRequesterTask.java:72)

at java.lang.Thread.run(Thread.java:722)

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "RETURNING"

  Position: 33

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:381)

at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1358)

What the hell is that???

Here is my code:

  DBI dbi = new DBI( "jdbc:postgresql://localhost:5432/subdepot", "thierryleveque", "" );

  Handle h = dbi.open();

  List<Map<String, Object>> rs = h.select("select id, name from subscriber");

It cannot be simpler than that. This sql query is valid. I can use it from any client.

I am using Postgres 9.1.3

Jdbc driver is: 9.1-901-1.jdbc4

Java 7u5

Any clue?

[Brian McCallister]

This is percolating up from postgres, but it makes no sense as according to the stack trace the exact SQL that is being given to postgres is "select id, name from subscriber" which clearly does not contain the string "RETURNING"  which, IIRC, is for putting a return value on inserts in postgres.

Let me set up a local postgres instance and see if I can replicate. Which version of jdbi are you using?

-Brian

[Brian McCallister]

Here is my attempt to replicate:

import com.google.common.collect.ImmutableList;

import com.google.common.collect.ImmutableMap;

import org.junit.Test;

import org.skife.jdbi.v2.DBI;

import org.skife.jdbi.v2.Handle;

import java.util.List;

import java.util.Map;

import static org.hamcrest.CoreMatchers.equalTo;

import static org.junit.Assert.assertThat;

public class PgTest

{

    @Test

    public void testFoo() throws Exception

    {

        // created db and inserted (1, 'Brian;), (2, 'Thierry') out of band

        // Using Postgres.app on OS X

        /*

            psql (9.1.4)

            Type "help" for help.

            brianm=# create table subscriber (id int primary key, name text);

            NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "subscriber_pkey" for table "subscriber"

            CREATE TABLE

            brianm=# insert into subscriber (id, name) values (1, 'Brian');

            INSERT 0 1

            brianm=# insert into subscriber (id, name) values (2, 'Thierry');

            INSERT 0 1

            brianm=#

         */

        DBI dbi = new DBI( "jdbc:postgresql://localhost:5432/brianm");

        Handle h = dbi.open();

        List<Map<String, Object>> rs = h.select("select id, name from subscriber");

        List<Map<String, Object>> expected = ImmutableList.<Map<String, Object>>of(

            ImmutableMap.<String, Object>of("id", 1, "name", "Brian"),

            ImmutableMap.<String, Object>of("id", 2, "name", "Thierry")

            );

        assertThat(rs, equalTo(expected));

    }

}

This test passes :-(

This is running with 1.6 not 1.7 (I put the test case in jdbi itself, so need 1.6 for compilation), and against the master branch, which is identical to jdbi-2.38.1 at the moment. 

[Brian McCallister]

This makes even less sense, according to the exception, the RETURNING appears at position 33 of the statement, but the statement being executed is only 31 characters long.

Can you write a unit test that replicates this? I will be happy to set up the postgres instance to test against.

-Brian

[Thierry Lévèque]

I am using jdbi 2.17

[Thierry Lévèque]

Ok, just updated to 2.38.1 and it is now working!!

Thank you for your time. 

[ric...@richardstanford.com]

I just spent a couple of days fighting this before finding this information - just FYI, the current information on http://jdbi.org/getting_jdbi/ represents a version with the bug - possibly replacing this static information with a link to http://mvnrepository.com/artifact/org.jdbi/jdbi would prevent other Postgres users from being turned away?

[Brian McCallister]

Ah, fixed in documentation now, thank you!

-Bria

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