How to insert a record and return new id as an output parameter in PostgreSql

Question

I'm trying to insert a new record while returning the new id as a out-parameter value using the following sql:

Insert into ERRORS  ( ErrorId, Date, ErrorInfo )  values  ( :ItemDate, :Text )  returning ErrorId into :ID

I get the following error:

Npgsql.NpgsqlException occurred
  BaseMessage=syntax error at or near "into"
  Code=42601
  ColumnName=""
  ConstraintName=""
  DataTypeName=""
  Detail=""
  ErrorCode=-2147467259
  ErrorSql=Insert into ERRORS  ( ErrorId, Date, ErrorInfo )  values  ( (('2014-04-02 08:16:36.045969')::timestamp), (('Test 333 4/2/2014 8:16:36 AM')::text) )  returning ErrorId into 
  File=src\backend\parser\scan.l
  Hint=""
  HResult=-2147467259
  Line=1053
  Message=ERROR: 42601: syntax error at or near "into"
  Position=168
  Routine=scanner_yyerror
  SchemaName=""
  Severity=ERROR
  Source=Npgsql
  TableName=""
  Where=""
  StackTrace:
       at Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__9.MoveNext()
       at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup)
       at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()
       at Npgsql.ForwardsOnlyDataReader.NextResultInternal()
       at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean preparedStatement, NpgsqlRowDescription rowDescription)
       at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
       at Npgsql.NpgsqlCommand.ExecuteNonQuery()
       at FrozenElephant.Symbiotic.ObjectWriter.Create(IDatabaseTypesFactory factory, Object value, IDbTransaction transaction, Boolean performCommitClose) in E:\Dev\FrozenElephant\SymbioticORM\Symbiotic\Symbiotic\ObjectWriter.vb:line 423
  InnerException:

I also tried removing the last "into" but does not work either.

What was the error message when you removed the last into?

Trygve Laugstøl
– Trygve Laugstøl

2014-04-02 13:30:05 +00:00
Commented Apr 2, 2014 at 13:30 — Trygve Laugstøl
– Trygve Laugstøl, Commented Apr 2, 2014 at 13:30

Fritz · Accepted Answer · 2015-03-31 06:38:59Z

2

Instead of using an out parameter, you have to use ExecuteReader with npgsql:

NpgsqlCommand cmd = new NpgsqlCommand(@"Insert into ERRORS(Date, ErrorInfo) values(:ItemDate, :Text) returning ErrorId", conn);

...Add your inpout parameters...

NpgsqlDataReader reader = cmd.ExecuteReader();

int errorId;

while (reader.Read())
{
    errorId = reader.GetInt32(0));
}

answered Mar 31, 2015 at 6:38

Fritz

4924 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Trygve Laugstøl · Accepted Answer · 2014-04-02 13:33:10Z

0

This example SQL will yield a result set with a single row containing the new ErrorId:

CREATE TABLE errors(ErrorId SERIAL, date TIMESTAMP, ErrorInfo VARCHAR(100));

INSERT INTO errors(Date, ErrorInfo)
VALUES (now(), 'abc') 
RETURNING ErrorId

One problem with your expression is that you have ErrorId in the insert into list, but not a value for it.

answered Apr 2, 2014 at 13:33

Trygve Laugstøl

7,7963 gold badges38 silver badges41 bronze badges

7 Comments

user117499 Over a year ago

Yes, typically you exclude the auto increment value or "Serial" in this case. But I need a return parameter.

Trygve Laugstøl Over a year ago

The ErrorId would be returned in my example, but it would become a normal result set. In Java I would do executeQuery() instead of executeUpdate().

Trygve Laugstøl Over a year ago

The RETURNING part determines the columns in the returned result set.

user117499 Over a year ago

yes I understand all that, I want a parameter, this works in oracle. Sql server it's your next result.

Trygve Laugstøl Over a year ago

PostgreSQL doesn't do out parameters from queries (which is what this ends up as from the server side), but perhaps your .NET driver has some magic that can do that for you.

|

Collectives™ on Stack Overflow

How to insert a record and return new id as an output parameter in PostgreSql

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related