Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2012
    Posts
    4

    Unanswered: use of INSERT... RETURNING with 9.1 JDBC driver

    I'm new to postgres (but not to DBs).

    I have a table with the following schema:

    CREATE TABLE contact (
    id serial NOT NULL,
    lastName varchar(50) DEFAULT NULL,
    firstName varchar(50) DEFAULT NULL,
    PRIMARY KEY (id)
    );
    I'm trying to use the following insert statement via the Postgres JDBC Driver:

    INSERT INTO CONTACT (LASTNAME, FIRSTNAME)
    VALUES (...) RETURNING id

    But I get the following exception:

    org.postgresql.util.PSQLException: ERROR: syntax error at or near "RETURNING"
    I don't have this problem when issuing the statement in psql.

    Is there a reason that the JDBC driver would not support this syntax though the underlying DB does?

    BTW, I'm trying to do this from myBatis... which is supposed to be able to handle such behavior assuming the underlying DB can do so.

    Driver: postgresql-9.1-901.jdbc4.jar driver:
    Server: 9.1.3
    Thanks for any insight

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    The JDBC driver does support this without problems.

    Without seing your Java code it's hard to tell where the problem is (but that is somewhat off-topic here).
    But it seems more of an iBatis problem...

  3. #3
    Join Date
    Apr 2012
    Posts
    4
    Quote Originally Posted by shammat View Post
    The JDBC driver does support this without problems.

    Without seing your Java code it's hard to tell where the problem is (but that is somewhat off-topic here).
    But it seems more of an iBatis problem...
    Thanks Shammat. I was afraid of that. This may not be the proper forum to address this issue (I'll also post in a myBatis forum); but in case someone here can figure it out, here is my mapper interface:

    final String INSERT = "INSERT INTO CONTACT (LASTNAME, FIRSTNAME) VALUES (#{lastName}, #{firstName}) RETURNING id";

    @Insert(INSERT)
    @Options(keyProperty = "id", useGeneratedKeys = true, flushCache = true)
    Integer insert(Contact contact);

    And I'm calling it here:
    Contact newContact = new Contact();
    newContact.setFirstName("Josh");
    newContact.setLastName("Pacer");
    int id = mapper.insert(newContact);

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Sorry, no idea. I have never used iBatis.

    Usually the value returned by "RETURNING" is retrieved through a ResultSet, so the application needs to use Statement.getResultSet() after running execute().

    Something like this:

    Code:
    stmt.execute("insert into foo (some_value) values ('foo') returning id");
    rs = stmt.getResultSet();
    if (rs.next())
    {
       int newId = rs.getInt(1);
    }
    But I assume that iBatis is using standard JDBC calls, something like this:
    Code:
    stmt.executeUpdate(theSqlString, new String[] {"id"} );
    rs = stmt.getGeneratedKeys();
    if (rs.next())
    {
       int newId = rs.getInt(1);
    }
    Where theSqlString is whatever you provide as the SQL
    If I run that piece of code I using the RETURNING clause in the INSERT statement I get the same error as you.
    But it works if I leave out the "RETURNING" in the INSERT statement (because the driver automatically handles this then).

    Based on the error message you posted, and the above code, I would assume you just have to leave out the RETURNING clause in your INSERT statement to make this work with iBatis.

  5. #5
    Join Date
    Apr 2012
    Posts
    4
    Wow, thanks for taking the time to look into this.

    I have tried leaving off the RETURNING clause, and when I do that I don't get the new ID that was assigned.

    It must be a myBatis problem, or something I'm doing wrong in declaring the insert.

    I've got a question out on the myBatis google group. If I get this resolved, then I'll try to post the answer here.

  6. #6
    Join Date
    Apr 2012
    Posts
    4
    Got a response on the myBatis forum.

    I don't need the RETURNING clause... that happens automatically.
    I just didn't realize how I was getting back the newly generated ID...

    Here's the link to the discussion with the answer:
    Exception when using INSERT... RETURNING with Postgres 9.1 - mybatis-user | Google Groups

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •