PostgreSQL Default insert for autoincrements and "returning" [message #1273689] |
Thu, 20 March 2014 11:35 |
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 11: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 20:23 |
Jeremie Bresson Messages: 1252 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?
|
|
|
Powered by
FUDForum. Page generated in 0.02461 seconds