Devart Forums

Posted: **Fri 07 Jun 2019 11:08**

Hi!

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";

Defining parameters
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);

Option 2. Not sure if the return value needs to be declared, so I tried that with a little different syntax:

Code: Select all

pgProc->ParamByName("_id")->Value = logId;

Both options compile.

A little more common code:

Code: Select all

pgProc->PrepareSQL();
pgProc->ExecProc();

I'm not sure where the PrepareSQL function should be called, so I have tried shuffling it around to varying results.

Now here comes the essential part, accessing the return value:
Option A:

Code: Select all

txtOutput->Text = pgProc->ParamByName("get_log_entry")->AsAnsiString;

Option B:

Code: Select all

txtOutput->Text = pgProc->ParamByName("RETURN_VALUE")->AsAnsiString;

Option C:

Code: Select all

txtOutput->Text = pgProc->FieldByName("get_log_entry")->AsAnsiString;

All these options compile as well, but none actually retrieves my string.
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;

PS. I have run a version of this code using an output parameter instead.
This works fine, but is not what I'm looking for.

Posted: **Tue 11 Jun 2019 07:26**

To return the result you need, try using the following code:

Code: Select all

 ...
  TPgStoredProc* pgProc = new TPgStoredProc(NULL);
  pgProc->Connection = pgCon;
  pgProc->StoredProcName = "get_log_entry";
  pgProc->Prepare();
  pgProc->ParamByName("_id")->AsInteger = logId;
  pgProc->ExecSQL();
  txtOutput->Text = pgProc->ParamByName("result")->AsAnsiString;
   ...

Posted: **Mon 17 Jun 2019 06:15**

Thanks! This is exactly what I was looking for.

Posted: **Mon 24 Jun 2019 12:42**

Thank you for the interest to our product.
It is good to see that the problem has been solved.
If you have any questions during using our products, please don't hesitate to contact us - and we will try to help you solve them.

Devart Forums

C++ builder TPgStoredProc with return value

C++ builder TPgStoredProc with return value

Re: C++ builder TPgStoredProc with return value

Re: C++ builder TPgStoredProc with return value

Re: C++ builder TPgStoredProc with return value