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