TPgStored Proc and Composite Types

[binfch]

Hi there

I have a stored function starting like this:

Code: Select all

CREATE OR REPLACE FUNCTION core.f_get_agent_switch_data
(
  IN  p_agent_id          bigint,
  IN  p_application_name  varchar
)
RETURNS core.t_agent_switch_data AS
$$
...

The function returns a composite type looking like:

Code: Select all

CREATE TYPE core.t_agent_switch_data AS (
  server_name      varchar(32),
  server_ip        varchar(32),
  server_port      integer,
  agent_extension  varchar(128),
  agent_password   varchar(255)
);

In delphi I have the following code:

Code: Select all

with PgStoredProcVoipData do begin
  ParamByName('p_agent_id').AsInteger := 1;
  ParamByName('p_application_name').AsString := 'GEM78';
  Execute;
...

Question -> Now how do I access the resulting composite type result?

Thanks,
P.

[AlexP]

Hello,

You can retrieve fields data from the type returned by your function as shown below:

Code: Select all

with PgStoredProcVoipData do begin 
  ParamByName('p_agent_id').AsInteger := 1; 
  ParamByName('p_application_name').AsString := 'GEM78'; 
  Execute; 
  Fields[0].asString;  //server_name
  Fields[1].asString  //server_ip
  .....

[binfch]

Thx for the feedback

However I still am having problems!

On my form I have one PgStoredProc. As DFM this looks like:

Code: Select all

  object PgStoredProc1: TPgStoredProc
    StoredProcName = 'f_get_agent_switch_data'
    Connection = PgConnection1
    SQL.Strings = (
      'SELECT f_get_agent_switch_data(:p_agent_id, :p_application_name)')
    Left = 80
    Top = 24
    ParamData = 
    CommandStoredProcName = 'f_get_agent_switch_data:0'
  end

As you can see there is a correct result (ftADT value) from the stored function when I push in the IDE the "execute" button in the params configuration dialog.

However I am unable to execute this in my code. My code looks like:

Code: Select all

  try
    PgStoredProcVoipData := TPgStoredProc.Create(nil);
    with PgStoredProcVoipData do begin
      Connection := PgConnection;
      StoredProcName := 'f_get_agent_switch_data';
      Sql.Clear;
      Sql.Add('SELECT f_get_agent_switch_data(:p_agent_id, :p_application_name)');
      Params.Clear;
      Params.AddParam(Params.CreateParam(ftADT, 'result', ptResult));
      Params.AddParam(Params.CreateParam(ftLargeint, 'p_agent_id', ptInput));
      Params.AddParam(Params.CreateParam(ftString, 'p_application_name', ptInput));
      ParamByName('p_agent_id').AsInteger := 1;
      ParamByName('p_application_name').AsString := 'GEM78';
      Execute;
      try
        ServerName := Fields[0].asString;
        ServerIp := Fields[1].asString;
        ServerPort := Fields[2].asInteger;
        AgentExtension := Fields[3].asString;
        AgentPassword := Fields[4].asString;
        Result := True;
      except
      end;
      Close;
    end;
  finally
    PgStoredProcVoipData.Free;
  end;

The exception is "List index out of bounds(0)"...

What am I doing wrong?

Thanks & cheers,
P.

[AlexP]

Hello,

You should place the following SELECT operator to the SQL.Text property of the procedure:

Code: Select all

SELECT (f_get_agent_switch_data(:p_agent_id, :p_application_name)).*

, in this case, a DataSet will be the result of execution, and you will be able to turn to its fields.

Code: Select all

 try
    PgStoredProcVoipData := TPgStoredProc.Create(nil);
    PgStoredProcVoipData.Connection := PgConnection1;
    PgStoredProcVoipData.Sql.Clear;
    PgStoredProcVoipData.Sql.Add('SELECT (f_get_agent_switch_data(:p_agent_id, :p_application_name)).*');
    PgStoredProcVoipData.Params.Clear;
    PgStoredProcVoipData.Params.CreateParam(ftLargeint, 'p_agent_id', ptInput);
    PgStoredProcVoipData.Params.CreateParam(ftString, 'p_application_name', ptInput);
    PgStoredProcVoipData.ParamByName('p_agent_id').AsInteger := 1;
    PgStoredProcVoipData.ParamByName('p_application_name').AsString := 'GEM78';
    PgStoredProcVoipData.Execute;
    try
      ServerName := PgStoredProcVoipData.Fields[0].asString;
      ServerIp := PgStoredProcVoipData.Fields[1].asString;
      ServerPort := PgStoredProcVoipData.Fields[2].asInteger;
      AgentExtension := PgStoredProcVoipData.Fields[3].asString;
      AgentPassword := PgStoredProcVoipData.Fields[4].asString;
    except
    end;
    PgStoredProcVoipData.Close;
  finally
    PgStoredProcVoipData.Free;
  end;