Use returning value in same UpdateCommand (PgSqlDataTable)

Use returning value in same UpdateCommand (PgSqlDataTable)

Postby chris901 » Sun 09 Oct 2016 18:32

Hello,

how can I use a returned value (actor_id = serial) in the same Update Command?

Image

For testing purpose I want to insert the generated serial value in a log table.

I've tried adding a new parameter as the ReturnValue direction like this:
Image

However when inserting a new value i get "missing parameter actor_id".

What am I missing?
chris901
 
Posts: 64
Joined: Wed 20 Jul 2016 04:21

Re: Use returning value in same UpdateCommand (PgSqlDataTable)

Postby Pinturiccio » Thu 13 Oct 2016 16:18

The reason is that when you create a parameter with Direction equal to ReturnValue, this parameter means the return value of the whole command. You have the following command:
Code: Select all
insert into actors
   first_name,
   last_name)
values
   (:new_first_name,
   :new_last_name)
returning actor_id;

insert into logs
   (msg)
values
   (:actor_id);

Your command consists of the two statements. The INSERT with RETURNING is executed first. Since there is another statement after it, this returning is ignored, and the second statement is executed. Its result is the resultset of the whole command. Out and ResultValue parameters in PostgreSQL are simply passed back to the client, and you cannot just use them in the command text.

To implement such logics, you can use the following anonymous code block:
Code: Select all
DO $$DECLARE temp integer;
BEGIN
   insert into actors
      first_name,
      last_name)
   values
      (:new_first_name,
      :new_last_name)
   returning actor_id into temp;

   insert into logs
      (msg)
   values
      (temp);
END$$;


This anonymous block will insert data to the actors table and adds the corresponding record to a log. However, when testing an anonymous code block, we found a bug in dotConnect for PostgreSQL with parsing $$ characters in an anonymous code block. We will fix it and post here about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: Use returning value in same UpdateCommand (PgSqlDataTable)

Postby chris901 » Tue 01 Nov 2016 04:51

Hello,

I am patiently waiting for the fix but am wondering what's the progress on this?

Can't wait to test out this great stuff in my many-to-many relations! :D
chris901
 
Posts: 64
Joined: Wed 20 Jul 2016 04:21

Re: Use returning value in same UpdateCommand (PgSqlDataTable)

Postby Pinturiccio » Thu 03 Nov 2016 13:10

After we had studied the issue, we found that there is no bug in dotConnect for PostgreSQL. PostgreSQL does not supports parameters in an anonymous code block. However, you can create a function with parameters that will perform the necessary actions. For example, it can be the following:

Code: Select all
CREATE OR REPLACE FUNCTION actor_insert(
    new_first_name text,
    new_last_name text)
  RETURNS void AS
$BODY$
    DECLARE
      temp int;
    BEGIN
      insert into actors
      (first_name,
      last_name)
   values
      (new_first_name,
      new_last_name)
   returning actor_id into temp;

   insert into logs
      (msg)
   values
      (temp);
    END;
    $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


You need to either specify actor_insert in the insert command and select StoredProcedure as the command type, or execute the following query:
Code: Select all
select actor_insert(:new_first_name, :new_last_name).

After this add the corresponding parameters to the command.

However, this workaround has the following non-critical bug. When you execute Insert in the Preview window, an empty column “return_value” is added to the table. This behavior occurs only in the Preview window, and it won’t occur at runtime with the DataGridView. We will notify you when the bug in the Preview window is fixed.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: Use returning value in same UpdateCommand (PgSqlDataTable)

Postby chris901 » Thu 03 Nov 2016 13:19

I tried out the example you posted earlier with the anonymous function and it works fine on my end. The value gets generated and saved in the temp variable. What bug were you refering to in that regard?
chris901
 
Posts: 64
Joined: Wed 20 Jul 2016 04:21

Re: Use returning value in same UpdateCommand (PgSqlDataTable)

Postby Pinturiccio » Tue 08 Nov 2016 14:15

It is a minor bug that we will fix. If you haven’t encountered it, you may ignore it, because it will be fixed soon.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL