Map a function that returns refcursor and OUT parameter

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
ve.lin
Posts: 3
Joined: Wed 28 Feb 2018 15:46

Map a function that returns refcursor and OUT parameter

Post by ve.lin » Tue 06 Mar 2018 09:22

Hello,
I am using Devart.Data.PostgreSql, DataContexts and function mapping style to call and execute functions against pgsql database.
Currently, this is similar working scenario:

Code: Select all

        [Function(Name = "get_users")]
        [ResultType(typeof(User), 1)]
        [ResultType(typeof(UserRole), 2)]
        public IMultipleResults GetUsers([Parameter(Name = "p_id", DbType = "int4")] Nullable<int> p_id)
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), p_id);

            return (IMultipleResults)result.ReturnValue;
        }
    // then, just reading the cursors like:
    var data = ctx.GetUsers(42);
    var users = data.GetResult<User>().ToList();
    var userRoles = data.GetResult<UserRole>().ToList();
which is fine, but I'd like to add OUT parameter, which currently I can only achieve when function "get_users" returns "records", but then the mapping breaks?
Any ideas?

ve.lin
Posts: 3
Joined: Wed 28 Feb 2018 15:46

Re: Map a function that returns refcursor and OUT parameter

Post by ve.lin » Wed 07 Mar 2018 11:25

So, my current solution is to create another refcursor , and push into it the value of the out parameter, which I wanted to use.
And now I end up with no "out" parameter and one more cursor. This way I don't mix the matter with cursors and out params ...

Any better ideas?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Map a function that returns refcursor and OUT parameter

Post by Shalex » Wed 07 Mar 2018 15:58

We will investigate the question and notify you about the result.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Map a function that returns refcursor and OUT parameter

Post by Shalex » Fri 30 Mar 2018 18:07

The bug with mapping a function that returns REFCURSOR is fixed: viewtopic.php?f=3&t=36968. Please follow this walkthrough with the new (7.10.1115) build.

Sample DDL/DML:

Code: Select all

CREATE TABLE DEPT (
  DEPTNO INT PRIMARY KEY,
  DNAME VARCHAR(14),
  LOC VARCHAR(13)
);

CREATE TABLE EMP (
  EMPNO INT PRIMARY KEY,
  ENAME VARCHAR(10),
  JOB VARCHAR(9),
  MGR INT,
  HIREDATE DATE,
  SAL REAL,
  COMM REAL,
  DEPTNO INT REFERENCES DEPT
);

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20);
  
  CREATE OR REPLACE FUNCTION test_refcursor(INOUT tcount integer, OUT o_dept refcursor, OUT o_emp refcursor)
   RETURNS record AS
$BODY$
BEGIN
     tcount := tcount + 1;
     OPEN o_dept FOR SELECT * FROM dept;
     OPEN o_emp FOR SELECT * FROM emp;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
Add tables and function to your *.lqml model, set return types of your method to Dept, Emp via Method Editor of Entity Developer, check the generated code, if the generated code is different, create a correct method (e.g.: TestRefcursor2) in a partial class so that it will not be overwritten during next code generation:

Code: Select all

        [Function(Name=@"public.test_refcursor")]
        [Devart.Data.Linq.Mapping.ResultType(typeof(Dept), 1, ResultTypeOrigin.OutCursorParameter)]
        [Devart.Data.Linq.Mapping.ResultType(typeof(Emp), 2, ResultTypeOrigin.OutCursorParameter)]
        public Devart.Data.Linq.IMultipleResults TestRefcursor([Parameter(Name="tcount", DbType="INTEGER")] ref System.Nullable<int> tcount)
        {
            IExecuteResult _TestRefcursorResult = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), tcount);
            tcount = ((System.Nullable<int>)(_TestRefcursorResult.GetParameterValue(0)));
            return ((Devart.Data.Linq.IMultipleResults)(_TestRefcursorResult.ReturnValue));
        }
Run the code:

Code: Select all

            int? count = 1;
            var data = context.TestRefcursor2(ref count);
            var depts = data.GetResult<Dept>().ToList();
            var emps = data.GetResult<Emp>().ToList();

Post Reply