TUniStoredProc with postgreSQL

[Vincent-40]

Hello,

I use the component TUniStoredProc to access a stored procedure for a PostgreSQL database.
This procedure gets a cursor, when I execute I get an error type field myproc is of unknown type.
How to retrieve values ​​(refcursor) returned from a stored procedure via UniDAC components.

Here is the call of the procedure SELECT * FROM MYPROC (: curret)

Thank you for your response

[AlexP]

hello,

The following sample demonstrates work with stored procedures in PostgreSQL using the TUniStoredProc component.
Your error may be due to the data types that are returned in the cursor, therefore, if the error cannot be reproduced on the given sample, then please send the script for creating the procedure and tables used in it.

Code: Select all

REATE TABLE dept
(
  deptno serial NOT NULL,
  dname character varying(14),
  loc character varying(13),
  cnt integer,
  CONSTRAINT dept_pkey PRIMARY KEY (deptno )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE dept
  OWNER TO postgres;

Code: Select all

CREATE OR REPLACE FUNCTION sel_cursor_from_dept()
  RETURNS refcursor AS
$BODY$
DECLARE
  cur REFCURSOR;
begin
  OPEN cur FOR SELECT * FROM dept ORDER BY deptno;
  RETURN cur;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sel_cursor_from_emp()
  OWNER TO postgres;

Code: Select all

program Project2;

{$APPTYPE CONSOLE}

uses
  SysUtils, Uni, PostgreSQLUniProvider;
var
  UniConnection: TUniConnection;
  UniStoredProc: TUniStoredProc;
  i: integer;
begin
  UniConnection := TUniConnection.Create(nil);
  UniConnection.ProviderName := 'PostgreSQL';
  UniConnection.Server := 'db';
  UniConnection.Port := 5438;
  UniConnection.Database := 'alexp';
  UniConnection.Username := 'postgres';
  UniConnection.Password := 'postgres';
  UniConnection.Connect;


  UniStoredProc := TUniStoredProc.Create(nil);
  UniStoredProc.Connection := UniConnection;
  UniStoredProc.StoredProcName := 'sel_cursor_from_dept';
  UniConnection.StartTransaction;
  UniStoredProc.Open;
  While not UniStoredProc.Eof do
  begin
    for i:= 0 to UniStoredProc.Fields.Count - 1 do
      Write(UniStoredProc.Fields[i].asString + '|');
    UniStoredProc.Next;
    Writeln('');
  end;
  UniConnection.Rollback;
  readln;
end.

[Vincent-40]

Hello,

Yes it works, by cons I have a stored procedure like this


CREATE OR REPLACE FUNCTION myproc(curret refcursor, date1 date) RETURNS SETOF refcursor AS
$BODY$
DECLARE
requete text;
BEGIN
requete := 'SELECT id, lib
FROM mytable WHERE mydate = ''' || date1 || ''';';
OPEN curret FOR EXECUTE requete;
RETURN NEXT curret;
END;

$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;

how do I retrieve the values.
when I execute, I have a message type field "myproc" is of unknown type

thank you

[AlexP]

hello,

Please send the script for creating the mytable table and the piece of the Delphi code, in which you are working with this procedure

[Vincent-40]

here is

Code: Select all

CREATE TABLE mytable
(
  id bigint NOT NULL,
  lib character varying NOT NULL,
  mydate date,
  CONSTRAINT mytable_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE mytable OWNER TO postgres;

INSERT INTO mytable (id, lib, mydate) values (1, 'test1', '01/01/2012');
INSERT INTO mytable (id, lib, mydate) values (2, 'test2', '01/01/2012');
INSERT INTO mytable (id, lib, mydate) values (3, 'test3', '01/01/2012');
INSERT INTO mytable (id, lib, mydate) values (4, 'test4', '01/01/2012');
INSERT INTO mytable (id, lib, mydate) values (5, 'test5', '01/01/2012');
INSERT INTO mytable (id, lib, mydate) values (6, 'test6', '01/01/2012');
INSERT INTO mytable (id, lib, mydate) values (7, 'test7', '01/01/2012');
INSERT INTO mytable (id, lib, mydate) values (8, 'test8', '02/01/2012');
INSERT INTO mytable (id, lib, mydate) values (9, 'test9', '02/01/2012');
INSERT INTO mytable (id, lib, mydate) values (10, 'test10', '02/01/2012');
INSERT INTO mytable (id, lib, mydate) values (11, 'test11', '02/01/2012');

Code: Select all

 
  UniStoredProc1.StoredProcName := 'myproc';
  UniConnection1.StartTransaction;
  UniStoredProc1.SQL.Clear;
  UniStoredProc1.PrepareSQL;
  UniStoredProc1.ParamByName('date1').AsDate     := EncodeDate(2012,1,1);
  UniStoredProc1.Open;
  While not UniStoredProc1.Eof do
  begin
    for i:= 0 to UniStoredProc1.Fields.Count - 1 do
      machaine := machaine + UniStoredProc1.Fields[i].asString + '|';
    UniStoredProc1.Next;
  end;
  UniConnection1.Rollback;
  ShowMessage(machaine);

[Vincent-40]

Hi Alex,

Could you look at my problem

thank you

[AlexP]

hello,

Your function returns not one cursor (like in my sample), but a cursor set "RETURNS SETOF refcursors", therefore to retrieve data, you should use the following code

Code: Select all

UniQuery1.SQL.Text := 'SELECT * FROM sp_vincent40(''mycur'', :date1);'+#13+
                      'FETCH ALL mycur;';
UniQuery1.ParamByName('date1').AsDate     := EncodeDate(2012,1,1);
UniQuery1.SpecificOptions.Values['UnpreparedExecute']:= 'True';
UniQuery1.Open;
While Not UniQuery1.Eof do
begin
  for i:= 0 to UniQuery1.Fields.Count - 1 do
    machaine := machaine + UniQuery1.Fields.asString + '|';
  UniQuery1.Next;
end;
ShowMessage(machaine);

[Suhaimin]

hi all,
i have a store procedure in postgresql 9.1

CREATE FUNCTION totalsalesformonth(month varchar) returns decimal as $$
select sum(sales) from salesfigures where month=$1;
$$ language 'sql';



if i want to call this storedproc with unidac in delphi application, how could i do it? thanks in advance

[Suhaimin]

I used TUnistoredproc. but after tunistoredproc.open ; error occur :SQL statement doesnt return row.

[Suhaimin]

hello,


CREATE OR REPLACE FUNCTION "GETINVOICENO"()
RETURNS integer AS
$BODY$UPDATE "TBLINVOICE"
SET "NEXTINVOICENO" = "NEXTINVOICENO" +1 ;

SELECT "NEXTINVOICENO" AS RESULT FROM "TBLINVOICE" ;

$BODY$
LANGUAGE sql VOLATILE
COST 100;
ALTER FUNCTION "GETINVOICENO"()
OWNER TO postgres;


UNISTOREDPROC1.Close;
UNISTOREDPROC1.STOREDPROCNAME :='GETINVOICENO';
UNISTOREDPROC1.Open;

After unistoredproc1.open; raise error SQL statement doesn't return rows..
how could i get the result to use unistoredproc1? thanks in advance..

[AlexP]

Hello,

The Open method attempts to open the dataset, and since your stored procedure doesn't return the dataset, you get a correct error message. To execute your procedure, you should use the Execute method - and after running this method, the result will be stored in the result parameter, i.e.:

UNISTOREDPROC1.STOREDPROCNAME :='GETINVOICENO';
SHOWMESSAGE(UNISTOREDPROC1.PARAMBYNAME('result').ASSTRING);

[Suhaimin]

hello AlexP,

if i have a store procedure in postgresql
CREATE FUNCTION totalsalesformonth(month varchar) returns decimal as $$
select sum(sales) from salesfigures where month=$1;
$$ language 'sql';

unistoredproc.close;
unistoredproc.parambyname('month').value := strmonth;
unistoredproc.execute;
showmessage(unistoredproc.parambyname('result').asstring);

is it the right code to get result ?thanks in advance

[AlexP]

Hello,

Yes, its correct code.

P.S. You don't need to close unistoredproc (unistoredproc.close), if there were no call of the open method before.

[Suhaimin]

hello AlexP,

I forgot to ask ,do i need to write statement
unistoreproc.prepareSQL before fill parambyname variable ?
thanks in advance for your help..

[AlexP]

Hello,

If you set the name of a stored procedure in the run-time and use IN parameters, then you should either call the Prepare method for parameter description, or specify the parameters manually. In case only OUT parameters are used in the procedure, you don't have to call this method, as it will be executed automatically when calling the Execute method. Besides, you don't have to call the Prepare method, if you specify the procedure name in the design-time (it will be called automatically too).