I'm attempting to retrieve the return value of a PL/pgSQL function using the TPgStoredProc class.
My main question is: How do I access the return value?
This is what I have tried so far:
First set the obvious properties.
Code: Select all
int logId = StrToInt(txtInput->Text);
TPgStoredProc* pgProc = new TPgStoredProc(NULL);
pgProc->Connection = pgCon;
pgProc->StoredProcName = "get_log_entry";
Option 1:. As seen here (Delphi): viewtopic.php?f=28&t=35563&p=123683&hil ... rn#p123697
Code: Select all
pgProc->Params->CreateParam(ftString, "get_log_entry", ptResult);
pgProc->Params->CreateParam(ftInteger, "_id", ptInput);
Code: Select all
pgProc->ParamByName("_id")->Value = logId;
A little more common code:
Code: Select all
pgProc->PrepareSQL();
pgProc->ExecProc();
Now here comes the essential part, accessing the return value:
Option A:
Code: Select all
txtOutput->Text = pgProc->ParamByName("get_log_entry")->AsAnsiString;
Code: Select all
txtOutput->Text = pgProc->ParamByName("RETURN_VALUE")->AsAnsiString;
Code: Select all
txtOutput->Text = pgProc->FieldByName("get_log_entry")->AsAnsiString;
It should be noted that Option 1 in combination with Option A doesn't give any error message,
while any other combination throws an exception with the message "parameter 'param_name' not found" or similar.
This is the SQL function I call:
Code: Select all
create function get_log_entry(IN _id integer)
returns varchar(50)
as $$
begin
return (select log_entry from log where log_id = _id);
end; $$
language plpgsql;
This works fine, but is not what I'm looking for.