C++ builder TPgStoredProc with return value

Discussion of open issues, suggestions and bugs regarding PgDAC (PostgreSQL Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Taddaiv
Posts: 2
Joined: Fri 07 Jun 2019 09:52

C++ builder TPgStoredProc with return value

Post by Taddaiv » 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.

MaximG
Devart Team
Posts: 1338
Joined: Mon 06 Jul 2015 11:34

Re: C++ builder TPgStoredProc with return value

Post by MaximG » 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;
   ...

Taddaiv
Posts: 2
Joined: Fri 07 Jun 2019 09:52

Re: C++ builder TPgStoredProc with return value

Post by Taddaiv » Mon 17 Jun 2019 06:15

Thanks! This is exactly what I was looking for.

ViktorV
Devart Team
Posts: 2661
Joined: Wed 30 Jul 2014 07:16

Re: C++ builder TPgStoredProc with return value

Post by ViktorV » 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.

Post Reply