Use returning value in same UpdateCommand (PgSqlDataTable)

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
chris901
Posts: 64
Joined: Wed 20 Jul 2016 04:21

Use returning value in same UpdateCommand (PgSqlDataTable)

Post by 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?

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Use returning value in same UpdateCommand (PgSqlDataTable)

Post by 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.

chris901
Posts: 64
Joined: Wed 20 Jul 2016 04:21

Re: Use returning value in same UpdateCommand (PgSqlDataTable)

Post by 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

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Use returning value in same UpdateCommand (PgSqlDataTable)

Post by 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.

chris901
Posts: 64
Joined: Wed 20 Jul 2016 04:21

Re: Use returning value in same UpdateCommand (PgSqlDataTable)

Post by 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?

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Use returning value in same UpdateCommand (PgSqlDataTable)

Post by 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.

Post Reply