Use returning value in same UpdateCommand (PgSqlDataTable)

[chris901]

Hello,

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



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:


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

What am I missing?

[Pinturiccio]

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.

[chris901]

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!

[Pinturiccio]

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.

[chris901]

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?

[Pinturiccio]

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.