Returning cursors from an anonymous block

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
wdnichols
Posts: 9
Joined: Thu 24 May 2018 12:49

Returning cursors from an anonymous block

Post by wdnichols » Wed 30 May 2018 20:21

We have been using DotConnect to access oracle from C# and are now converting to PostgreSQL using DotConnect for PostgreSQL.

When working with Oracle, we were able to return cursors from an anonymous block like this:

Code: Select all

                begin 
                  open :CUR1 for 
                    select * from table_1;
                  open :CUR2 for 
                    select * from table_2;
                end;
We would create an OracleParameter of type cursor and direction output, then execute through an OracleCommand object.

We are trying to do the same thing using DotConnect for PostgreSQL, but cannot identify the correct PgSqlType to use for the cursor out parameters.

Is it possible to return cursor parameters from an anonymous block using DotConnect for PostgreSQL?

Thanks, in advance, for your help.

Bill Nichols.

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

Re: Returning cursors from an anonymous block

Post by Pinturiccio » Fri 01 Jun 2018 15:10

This anonymous block is for Oracle. If you run it in pgAdmin, you will get an error. You can use the following anonymous block for PostgreSQL:

Code: Select all

DO $$
DECLARE 
CUR1 refcursor;
CUR2 refcursor;
 
BEGIN
 OPEN CUR1 FOR SELECT * FROM table_1;
 OPEN CUR2 FOR SELECT * FROM table_2;
END
$$;
However, PostgreSQL does not allow to return value from an anonymous block. You can use a PostgreSQL function instead. For more information, please refer to https://www.devart.com/dotconnect/postg ... ursor.html

Welton3
Posts: 23
Joined: Thu 04 Sep 2014 15:36

Re: Returning cursors from an anonymous block

Post by Welton3 » Fri 22 Jun 2018 14:54

Given the example from the documentation for the PlSqlCursor class, and the example from your reply for opening multiple cursors, how should the following code be modified to return two cursors? What would the function of "refcursorfunc" look like?

Code: Select all

PgSqlCommand cmd = new PgSqlCommand("refcursorfunc");
cmd.Connection = myConnection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.ParameterCheck = true;
myConnection.Open();
PgSqlTransaction t = myConnection.BeginTransaction();
try 
{
    cmd.ExecuteNonQuery();
    
    PgSqlCursor cursor = cmd.Parameters["p"].PgSqlValue as PgSqlCursor;
    
    using (PgSqlDataReader rd = cursor.GetDataReader()) 
    {
        while (rd.Read()) Console.WriteLine(rd.GetValue(0));
    }
}
finally 
{
    t.Commit();
    myConnection.Close();
}

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

Re: Returning cursors from an anonymous block

Post by Pinturiccio » Sat 23 Jun 2018 09:24

You can use the following function:

Code: Select all

CREATE OR REPLACE FUNCTION refcursorfunc2(
    OUT p1 refcursor,
    OUT p2 refcursor)
  RETURNS record AS
$BODY$ 
BEGIN 
OPEN p1 FOR SELECT * FROM table_1;
OPEN p2 FOR SELECT * FROM table_2;
END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION refcursorfunc2()
  OWNER TO postgres;
And your code will look as the following:

Code: Select all

PgSqlCommand cmd = new PgSqlCommand("refcursorfunc2");
cmd.Connection = myConnection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.ParameterCheck = true;
myConnection.Open();
PgSqlTransaction t = myConnection.BeginTransaction();
try
{
    cmd.ExecuteNonQuery();

    PgSqlCursor cursor1 = cmd.Parameters["p1"].PgSqlValue as PgSqlCursor;
    PgSqlCursor cursor2 = cmd.Parameters["p2"].PgSqlValue as PgSqlCursor;

    using (PgSqlDataReader rd = cursor1.GetDataReader())
    {
        while (rd.Read()) Console.WriteLine(rd.GetValue(0));
    }

    using (PgSqlDataReader rd = cursor2.GetDataReader())
    {
        while (rd.Read()) Console.WriteLine(rd.GetValue(0));
    }

}
finally
{
    t.Commit();
    myConnection.Close();
}

Welton3
Posts: 23
Joined: Thu 04 Sep 2014 15:36

Re: Returning cursors from an anonymous block

Post by Welton3 » Tue 26 Jun 2018 17:17

Thank you for all your help so far. Here is a little background, for context. As stated in the original post, we are migrating from Oracle to PostgreSQL. In Oracle, to limit the number of round trips to the database, we frequently make us of multi-cursor queries in anonymous blocks. Some of these queries are constructed dynamically. We are attempting to preserve as much of our current architecture as possible. To this end, we are looking for a solution where we are able to select an arbitrary number of cursors in a single round trip.

Building on your refcursorfunc example, I have created the following function:

Code: Select all

create or replace function refcursorfuncx(in aSQL text, out aCURSOR_ARRAY refcursor[]) as $$
declare
  vSQL_ARRAY text[];
  vSQL text;
  vCURSOR_ARRAY refcursor[];
  i int;
begin
  vSQL_ARRAY := regexp_split_to_array(aSQL, E';');

  for i in 1..array_upper(vSQL_ARRAY, 1)
  loop
    vSQL := vSQL_ARRAY[i];

    if (vSQL <> '') then
      declare
        vCURSOR refcursor;
      begin
        open vCURSOR for execute vSQL;

        vCURSOR_ARRAY[i] := vCURSOR;
      end;
    end if;
  end loop;

  aCURSOR_ARRAY := vCURSOR_ARRAY;

  return;
end;
$$ language plpgsql;
I have verified that it does return multiple cursors when executed in PostgreSQL using the following code:

Code: Select all

do $$
declare
  vCURSORS refcursor[];
  vCURSOR refcursor;
  vRECORD record;
begin
  vCURSORS := refcursorfuncx('select 123 as a; select 456 as b;');

  vCURSOR := vCURSORS[1];

  fetch next from vCURSOR into vRECORD;

  close vCURSOR;

  raise notice '%', vRECORD.a;

  vCURSOR := vCURSORS[2];

  fetch next from vCURSOR into vRECORD;

  close vCURSOR;

  raise notice '%', vRECORD.b;
end;
$$;
However, I have so far been unable to get the cursors back in my C# code.

Code: Select all

        [TestMethod]
        public void MultiCursorTest()
        {
            var x = GetCursors(myConnection, "select 123 as a; select 456 as b");
        }

        public static PgSqlCursor[] GetCursors(PgSqlConnection connection, string sql)
        {
            var command = new PgSqlCommand("refcursorfuncx", connection);

            command.ParameterCheck = true;
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new PgSqlParameter("aSQL", sql));

            command.ExecuteNonQuery();

            var result = command.Parameters["aCURSOR_ARRAY"].PgSqlValue as PgSqlCursor[];

            return result;
        }
command.Parameters["aCURSOR_ARRAY"].PgSqlValue actually returns an array of string: {"<unnamed portal 1>","<unnamed portal 2>"}

How can I get an array of cursors back?

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

Re: Returning cursors from an anonymous block

Post by Pinturiccio » Wed 27 Jun 2018 15:12

Welton3 wrote:command.Parameters["aCURSOR_ARRAY"].PgSqlValue actually returns an array of string: {"<unnamed portal 1>","<unnamed portal 2>"}
If you execute the following query in pgAdmin: SELECT refcursorfuncx('select 123 as a; select 456 as b;');
You will get the following result: "{"<unnamed portal 1>","<unnamed portal 2>"}".
For example, the result of a query: SELECT refcursorfunc2();
where refcursorfunc2 is described above, in pgAdmin is similar: "("<unnamed portal 1>","<unnamed portal 2>")". This is the expected result. But at the same time, with dotConnect for PostgreSQL you were able get cursors from the refcursorfunc2 function.

Your aCURSOR_ARRAY parameter is an array. For more information, please refer to https://www.devart.com/dotconnect/postg ... lType.html

And it can be read as PgSqlArray. For more information, please refer to https://www.devart.com/dotconnect/postg ... Array.html

Thus you need to replace th following code:

Code: Select all

var result = command.Parameters["aCURSOR_ARRAY"].PgSqlValue as PgSqlCursor[];
on the code:

Code: Select all

var result = (PgSqlArray)command.Parameters["aCURSOR_ARRAY"].PgSqlValue;
Your GetCursors function, respectively, must return the PgSqlArray type instead of PgSqlCursor[].

However, we reproduced the issue with reading cursors from PgSqlArray object. They are read as String and not as PgSqlCursor. We will investigate the issue and post here about the results as soon as possible. When the issue is fixed, we will provide an example of how to use PgSqlArray for reading an array of cursors.

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

Re: Returning cursors from an anonymous block

Post by Pinturiccio » Wed 18 Jul 2018 12:04

We have fixed the bug with reading an array of cursors as an array of strings. We will post here when the corresponding build of dotConnect for PostgreSQL is available for download.

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

Re: Returning cursors from an anonymous block

Post by Pinturiccio » Thu 19 Jul 2018 14:10

New build of dotConnect for PostgreSQL 7.11.1190 is available for download now!
It can be downloaded from https://www.devart.com/dotconnect/postg ... nload.html (trial version) or from Customer Portal (for users with valid subscription only).
For more information, please refer to viewtopic.php?t=37473

You need to use the following code with this version:

Code: Select all

PgSqlConnection conn = new PgSqlConnection("your connection string");
conn.Open();
PgSqlTransaction trans = conn.BeginTransaction();

PgSqlCommand comm = new PgSqlCommand("refcursorfuncx", conn);
comm.Transaction = trans;
comm.ParameterCheck = true;
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.Parameters.Add(new PgSqlParameter("aSQL", "select 123 as a; select 456 as b"));
comm.ExecuteNonQuery();


PgSqlArray array = (PgSqlArray)comm.Parameters["aCURSOR_ARRAY"].PgSqlValue;
PgSqlCursor cursor1 = array[1] as PgSqlCursor;
PgSqlCursor cursor2 = array[2] as PgSqlCursor;

using (PgSqlDataReader rd = cursor1.GetDataReader())
{
    while (rd.Read()) Console.WriteLine(rd.GetValue(0));
}

using (PgSqlDataReader rd = cursor2.GetDataReader())
{
    while (rd.Read()) Console.WriteLine(rd.GetValue(0));
}
trans.Commit();

Welton3
Posts: 23
Joined: Thu 04 Sep 2014 15:36

Re: Returning cursors from an anonymous block

Post by Welton3 » Tue 21 Aug 2018 16:51

We have updated dotConnect for PostgreSQL to 7.11.1202, and, following the example you provided, I have successfully retrieved multiple cursors from the refcursorfuncx stored procedure in a single round-trip. However, I have encountered another hurdle: some of the cursors in our code base use parameterized SQL.

E.g.

Code: Select all

begin 
    open :mail for 
        select *
            from mail
        where to_user_id = :USER_ID or (created_user_id = :USER_ID and to_user_id is null);
    open :mail_to for 
        select *
            from mail_to
        where mail_id in (select mail_id from mail where to_user_id = :USER_ID);
end;
In this case, only one parameter is used for both queries in the cursor, but, as you can imagine, more complex cases will require different parameters for different queries within the cursor.

How would one go about passing additional parameters to the stored procedure and applying the appropriate parameter values to each individual query?

My first thought is to pass a 2-dimensional array containing the name and value for any parameters required by the cursor. But, since each query is being executed as dynamic SQL in the refcursorfuncx stored procedure, that raises the question of how to assign the actual parameter values using the "using" keyword, if that's even possible.

Any insight or guidance would be greatly appreciated.

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

Re: Returning cursors from an anonymous block

Post by Pinturiccio » Thu 23 Aug 2018 14:39

Unfortunately, this question is beyond the scope of our support. This question is about creating a PostgreSQL function and parsing an array of parameters. But this is not related to dotConnect for PostgreSQL, and you will need to create such a function yourself.

Welton3
Posts: 23
Joined: Thu 04 Sep 2014 15:36

Re: Returning cursors from an anonymous block

Post by Welton3 » Thu 23 Aug 2018 16:53

I understand, and thank you for your support so far.

Post Reply