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();