Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » scout » PostgreSQL Default insert for autoincrements and "returning"
PostgreSQL Default insert for autoincrements and "returning" [message #1273689] Thu, 20 March 2014 07:35 Go to next message
davide . is currently offline davide .
Messages: 12
Registered: March 2014
Junior Member
Hi all. I'm using scout w/ PostgreSQL 9. In the database some tables have configured an autoincrement column "id" used as primary key. from other (non-scout) clients I insert table rows with the SQL "INSERT INTO (id, column, ...) VALUES (DEFAULT, col_value, ...) RETURNING id

This statement atomically creates a new row, sets the value for id, and sets the other columns values to what is provided by the query. Moreover, it returns the id just created, so the application can use it for future references to that table row. Quite handy, isn't it?

Handling this in scout looks problematic: "DEFAULT" keyword is not recognized, returning id cannot be got from the SQL.insert call, ...

Is there any way we can handle this in scout?

thanks in advance

[Updated on: Thu, 20 March 2014 07:36]

Report message to a moderator

Re: PostgreSQL Default insert for autoincrements and "returning" [message #1277298 is a reply to message #1273689] Tue, 25 March 2014 16:23 Go to previous message
Jeremie Bresson is currently offline Jeremie Bresson
Messages: 681
Registered: October 2011
Senior Member
I am sorry I am not familiar to PostgreSQL. I can give you solution with other database engines:

Derby:

The questions table contains a question_id integer and a question_text varchar.

SQL.insert(" insert into questions (question_text, multiple_choices) " +
    " values (:QuestionText, :MultipleChoices) ", formData);

SQL.selectInto(" values IDENTITY_VAL_LOCAL() " +
    " into  :QuestionNr", formData);


MySQL:

category_id has the flag AUTO_INCREMENT
last_updatehas CURRENT_TIMESTAMP as default value.

SQL.insert(
    " insert into      category(name) " +
        " values (:categoryName) ",
    formData
    );
SQL.selectInto(
    " select      category_id, " +
        "             last_update " +
        " from        category " +
        " where       category_id = LAST_INSERT_ID() " +
        " into        :id, " +
        "             :lastUpdate ",
    formData.getMetadataBox());


Oracle:

Sequence can be used: you first select the id: "SELECT seq_campus_site_id.NEXTVAL FROM DUAL" (you set it in your formData) and you write the insert after that.


Maybe there is a workarround for PostgreSQL... When you speak from other projects, are you using JDBC?
Previous Topic:Hibernate integration
Next Topic:Trouble after updating to nightly Luna build
Goto Forum:
  


Current Time: Mon Sep 01 14:41:52 EDT 2014

Powered by FUDForum. Page generated in 0.06029 seconds