Postgres 11 Procedure call by TZStoredProc

[stinkard]

Hi everyone!
First of all thanks to Zeos developers for a cool free product!

Faced a problem of a call of the Postgres 11 procedure.

Code: Select all

select VERSION();
PostgreSQL 11.1, compiled by Visual C++ build 1914, 64-bit

Example:

Code: Select all

CREATE OR REPLACE PROCEDURE "testuser"."testproc"(IN "x" int4, IN "y" int4, INOUT "z" int4)
 AS $BODY$
begin
	z := x+y;
end$BODY$
  LANGUAGE plpgsql

In attempt to perform this procedure we receive an exception

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  ZStoredProc1.StoredProcName := 'testuser.testproc';
  ZStoredProc1.ParamByName('x').Value := 2;
  ZStoredProc1.ParamByName('y').Value := 3;
  //ZStoredProc1.UseCall := true;
  try
    ZStoredProc1.ExecProc;
  EXCEPT
    ON e: exception do
      Memo1.Text := e.Message;
  end;
  Edit1.Text := ZStoredProc1.ParamByName('z').Value;
end;

Code: Select all

SQL Error: ERROR:  bin.proc1(integer, integer, unknown) is a procedure
LINE 1: SELECT * FROM bin.proc1(2,3,NULL)
HINT:  To call a procedure, use CALL.

In the version of ZEOS 7.2.4 we have the following code which does not allow to find solutions of this problem:

Code: Select all

unit ZDbcPostgreSqlStatement;
...
function TZPostgreSQLCallableStatement.GetProcedureSql: string;
...
var
  InParams: string;
begin
  if Length(CachedQueryRaw) = 1 then  //only name in there?
  begin
    Unprepare; //reset cached query
    InParams := GenerateParamsStr(InParamCount);
    Result := Format('SELECT * FROM %s(%s)', [SQL, InParams]);
    {$IFDEF UNICODE}WSQL{$ELSE}ASQL{$ENDIF} := Result; //sets the cached queries again
  end;
end;

It was necessary to build hastily a crutch.
Yes, I propose the solution nonindustrial and obviously curve, but it seems works.
Very much I hope that in the following version the good decision will be added.
Thanks in advance! And all of good luck!

My changes:

Code: Select all

unit ZDbcIntfs;
...
  IZPreparedStatement = interface(IZStatement)
...
    function GetUseCall: boolean;               /// added by stinkard
    procedure SetUseCall(const Value: boolean); /// added by stinkard
  end;

Code: Select all

unit ZAbstractRODataset;
...
TZAbstractRODataset = class(TDataSet)
...
protected
  property UseCall: boolean read FUseCall write FUseCall; /// added by stinkard
...
end;
...
procedure TZAbstractRODataset.InternalPrepare;
begin
...
  try
    if (FSQL.StatementCount > 0) and((Statement = nil) or (Statement.GetConnection.IsClosed)) then
      Statement := CreateStatement(FSQL.Statements[0].SQL, Properties)
    else
      if (Assigned(Statement)) then
         Statement.ClearParameters;
    Statement.SetUseCall(FUseCall) /// added by stinkard
  finally
    Connection.HideSQLHourGlass;
  end;
end;
...

Code: Select all

unit ZDbcPostgreSqlStatement;
function TZPostgreSQLCallableStatement.GetProcedureSql: string;
...
var
  InParams: string;
begin
  if Length(CachedQueryRaw) = 1 then  //only name in there?
  begin
    Unprepare; //reset cached query
    InParams := GenerateParamsStr(InParamCount);
    if UseCall then                                    /// added by stinkard
      Result := Format('CALL %s(%s)', [SQL, InParams]) /// added by stinkard
    else                                               /// added by stinkard
      Result := Format('SELECT * FROM %s(%s)', [SQL, InParams]);
    {$IFDEF UNICODE}WSQL{$ELSE}ASQL{$ENDIF} := Result; //sets the cached queries again
  end;
end;

[marsupilami]

Hello,

hmm - wouldn't it be more simple to redefine your procedures to be functions? On a first look Postgresql functions seem to be a superset of postgresql procedures?

Usually I would like for Zeos to determine the type of call (procedure/function) and the way to set up the call (call/select) in an automated fashion. But given that Postgresql has function overloading, I am not sure how that could be done.

Best regards,

Jan

[Fr0sT]

It's completely a TZStoredProc business - so maybe a new param will do the job done

[stinkard]

Hellow!
marsupilami,
The introduction of procedures in PG11 is to enable the use of internal transactions that have not been addressed in previous versions of PG.
https://www.postgresql.org/docs/11/plpg ... tions.html

Therefore, with the work of the procedures must be ensured. This need will grow all the time.
What to do with the overload must come up.

We also need to read the PG metadata each time the StoredProc property is set for automated estimation of call type.
Now the metadata is requested only by the PropertyEditor.
And it is also necessary to remember that in different versions of PG there is a different format of metadata about functions.
So the task is a bit more complicated than it seems at first watch. But as usual

Good luck to all,
Max.

[marsupilami]

Hello Max,

you are right. It's a new feature that totally escaped my attention. I think the job it's doable:

1. Modify UncachedGetProcedures and maybe UncachedGetProcedureColumns to use the new metadata field, if PG11+ is detected. pg_proc.proisagg and pg_proc.proiswindow were replaced by pg_proc. prokind. See "E.2.2. Migration to Version 11" in the PG manual. GetServerVersion might help to determine the current server version.
2. Modify PGs implementation of IZCallableStatement to use the correct calling convention. It either needs to be determined from GetProcedures or it needs to make its own call into the database to determine this. In the latter case again GetServerVersion might be helpful.
3. Worry about overloaded functions later on - I assume Zeos PostgreSQL driver doesn't worry about this currently at all.

I will set up a ticket on the issue tracker to not forget this. Unfortunately I can't take the time to implement it right away. If you want to have a go at this I would be happy to give you pointers, if you need them.

Best regards,

Jan

[marsupilami]

Ticket is created: https://sourceforge.net/p/zeoslib/tickets/316/