Using/accessing cursors

richardkirk · Post by **richardkirk** » Fri 26 Aug 2011 20:05

I have the following function:
CREATE OR REPLACE FUNCTION company_select(IN v_companynr integer, INOUT swv_refcur refcursor, INOUT swv_refcur2 refcursor)
RETURNS record AS
$BODY$
DECLARE
v_Count SMALLINT;
BEGIN
select Count(*) INTO v_Count From Company Where Company.CompanyNr = v_CompanyNr;
If v_Count = 0 then
open SWV_RefCur for Select 1 AS Err, 'Company not found. (Company_Select)' AS Msg;
Return;
end if;

select Count(*) INTO v_Count From CompanyAccount Where CompanyNr = v_CompanyNr;
If v_Count = 0 then
Insert Into CompanyAccount(CompanyNr) Values(v_CompanyNr);
end if;

open SWV_RefCur2 for Select *,
(select ObjText from ObjDescrCoy
where CompanyNr = 0 and ObjName = 'BANK' and ObjCode = BankNr) AS BankName
From Company, CompanyAccount
Where Company.CompanyNr = v_CompanyNr
And CompanyAccount.CompanyNr = v_CompanyNr;

Insert Into ObjDescrCoy(CompanyNr, ObjName, ObjCode, ObjText)
Select v_CompanyNr, 'COMPUTER', Coalesce(Max(ObjCode),0)+1, SYS_CONTEXT('userenv','host')
From ObjDescrCoy
Where CompanyNr = v_CompanyNr
And ObjName = 'COMPUTER'
And Not Exists(Select * From ObjDescrCoy x
Where x.CompanyNr = v_CompanyNr
And x.ObjName = 'COMPUTER'
And x.ObjText = SYS_CONTEXT('userenv','host'));

Insert Into ObjDescrCoy(CompanyNr, ObjName, ObjCode, ObjText)
Select v_CompanyNr, 'USER', Coalesce(Max(ObjCode),0)+1, CURRENT_USER
From ObjDescrCoy
Where CompanyNr = v_CompanyNr
And ObjName = 'USER'
And Not Exists(Select * From ObjDescrCoy x
Where x.CompanyNr = v_CompanyNr
And x.ObjName = 'USER'
And x.ObjText = CURRENT_USER);
END; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
...................
Now, how in the world can I access swv_refcur, swv_refcur2 in Delphi?

AlexP · Post by **AlexP** » Mon 29 Aug 2011 13:49

Hello,

The code specified below shows how to work with refcursor through PgDAC:

Code: Select all

var
  PgConnection: TPgConnection;
  PgStoredProc: TPgStoredProc;
  PgQuery1, PgQuery2: TpgQuery;
begin
  PgConnection := TPgConnection.Create(nil);
  PgConnection.Server := '...';
  PgConnection.Port := 5432;
  PgConnection.Database := '...';
  PgConnection.LoginPrompt := false;
  PgConnection.Username := '...';
  PgConnection.Password := '...';
  PgConnection.Connect;

  PgStoredProc := TPgStoredProc.Create(nil);
  PgStoredProc.Connection := PgConnection;
  PgStoredProc.StoredProcName := 'company_select';
  PgStoredProc.Prepare;
  PgStoredProc.ParamByName('swv_refcur').DataType := ftCursor;
  PgStoredProc.ParamByName('swv_refcur').ParamType := ptOutput;
  PgStoredProc.ParamByName('swv_refcur2').DataType := ftCursor;
  PgStoredProc.ParamByName('swv_refcur2').ParamType := ptOutput;

  PgQuery1 := TpgQuery.Create(nil);
  PgQuery1.Connection := PgConnection;
  PgQuery2 := TpgQuery.Create(nil);
  PgQuery2.Connection := PgConnection;


  PgConnection.StartTransaction;
  PgStoredProc.ParamByName('v_companynr').AsInteger := 1;  
  PgStoredProc.Execute;
  PgQuery1.Cursor := PgStoredProc.ParamByName('swv_refcur').AsCursor;
  PgQuery1.Open;
  PgDataSource1.DataSet :=  PgQuery1;
  PgQuery2.Cursor := PgStoredProc.ParamByName('swv_refcur2').AsCursor;
  PgQuery2.Open;
  PgDataSource2.DataSet :=  PgQuery2;

richardkirk · Post by **richardkirk** » Tue 30 Aug 2011 13:28

Thank you!
PgQuery1.Cursor := PgStoredProc.ParamByName('swv_refcur').AsCursor;
raises an error:
function sys_context(unknown, unknown) does not exist.

And I thought T-SQL was difficult!

AlexP · Post by **AlexP** » Wed 31 Aug 2011 13:14

Hello,

There is no sys_context function in PostgreSQL (this is an Oracle function). You should use functions available in PostgreSQL.

richardkirk · Post by **richardkirk** » Wed 31 Aug 2011 16:09

That's my problem!
I have no idea where postgesql is getting this error message. As far as I know, I call the function, (using your method), and the answer I get back is the error "function sys_context...etc., etc.)"
Maybe better just to re-write the function and not use cursors at all would be the better idea?
By the way PostgreSQL version is version 9.
I do not have Oracle on my machine.

richardkirk · Post by **richardkirk** » Wed 31 Aug 2011 20:05

MY APOLOGIES!
I now see the sys_context line! I'm blind!
The system I'm working on was "translated" from MSSQL to PostgreSQL, and the translation has inserted this function.
I will carry on and build further work-arounds!
I have already migrated one system to posgres + pgDAC very successfully - AND IT'S VERY FAST! Only 6 million records, but going up each month........hope it still performs like it does now!

AlexP · Post by **AlexP** » Thu 01 Sep 2011 12:03

Hello,

PgDAC shows high performance even when working with very large amounts of data, so it is not likely that you encounter any problems even when the number of records increases. If you have any further questions, please contact us and we will try to help you.

Devart Forums