Delphi Postgres stored procedure

Question

I am working on Delphi application which deals with stored procedures.I created stored procedure for insert statement and it works fine for my application. Now,I wanted to create the same for select statement.

CREATE TYPE list_all_firstname AS ( first_name character varying);  

CREATE FUNCTION select_sp()   
RETURNS SETOF list_all_firstname AS  
$$  
DECLARE  
rec record;  
BEGIN  
  FOR rec IN (SELECT first_name FROM person) LOOP  
  RETURN NEXT rec;  
END LOOP;  
END;  
$$ LANGUAGE plpgsql;

The call is:

SELECT * FROM select_sp();

Till this everything is fine in postgres.I wanted to access this stored procedure in my delphi application. My code is:

 with StoredProc2 do begin
     StoredProcName :='select_sp';
     ExecProc;
     Edit5.Text:=ParamByName('list_all_firstname').AsString ;
    end;

But i gets the error saying "Could not find object".How do i access return values of stored procedure in delphi??

If StoredProc2 is a TDataset descendant, you would have to be using FieldByName. With a TDataset descendant ParamByName is for the parameters that are passed TO the query/storedproc, not the results returned by it. — Marjan Venema
– Marjan Venema, Commented May 28, 2012 at 9:21

poonam · Accepted Answer · 2012-05-28 11:03:17Z

1

I got the answer..could not find object is BDE error... Next thing is accessing values,no need to use stored procedure component.We can use TQuery as below...:

  Query1.SQL.Clear;
  Query1.SQL.Add('SELECT * FROM select_sp()');
  Query1.Active := True;

  for i:=0 to Query1.RowsAffected-1 do
          begin
             sval:=Query1.FieldByName('first_name').AsString;
             ShowMessage(sval);
             Query1.Next;
          end;

answered May 28, 2012 at 11:03

poonam

8104 gold badges20 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delphi Postgres stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related