How to call a function with void return using Firedac FDConnection Component in Delphi XE5?

I recently started using the [ExecSQLScalar]1 and [ExecSQL]2 methods of the FDConnection component in Delphi XE5. It's very handy not to need to build a Dataset object, like FDQuery just for simple queries or executions. However I had a curious problem when executing a function with void return that has internal validations where it can generate exceptions. I'm using a Postgres database.

CREATE FUNCTION can_be_exception()
  RETURNS void AS
$$
BEGIN
    RAISE EXCEPTION E'fail';
END;
$$
  LANGUAGE plpgsql STABLE;

In delphi, I call the ExecSQLScalar function ...

FDConnection1.ExecSQLScalar('select 1');
FDConnection1.ExecSQLScalar('select can_be_exception()');

On first run, I get the following error:

Project TFDConnectionDEMO.exe raised exception class EPgNativeException with message '[FireDAC][Phys][PG][libpq] ERROR: fail'.

On the second run, I get a Violation Access error:

Project TFDConnectionDEMO.exe raised exception class $C0000005 with message 'access violation at 0x00000000: read of address 0x00000000'.

Apparently the error occurs in the line below in unit FireDAC.Comp.Client

function TFDCustomConnection.ExecSQLScalar(const ASQL: String;
  const AParams: array of Variant; const ATypes: array of TFieldType): Variant;
var
  oCmd: IFDPhysCommand;
begin
  oCmd := BaseCreateSQL;
  try
    if BasePrepareSQL(oCmd, ASQL, AParams, ATypes) or (FExecSQLTab = nil) then begin
      FDFree(FExecSQLTab);

...

ignoring the previous error and trying again, another error is displayed...

Project TZConnectionDEMO.exe raised exception class EFDException with message '[FireDAC][DatS]-24. Row is not nested'.

Searching, I found no response to this error. I figured my mistake would be to call the bank raise_exception function using the ExecSQLScalar function of the FDConnection component. So I tried using FDConnection.ExecSQL and as I imagined, you can not use this if there is a SELECT clause in the parameter.

Is there a better way to call function with void return using FDConnection.ExecSQL? would a BUG be in the component? or would not it be correct to make that kind of call?

Solution

Using ExecSQLScalar is fine in this case. This is certainly a bug (which was already fixed, at least in Delphi 10.2.3). As you've correctly pointed out, the problem is in releasing a table storage object instance held by the FExecSQLTab field by using FDFree procedure.

I don't have Delphi XE5 source code but maybe you can see something like this inside (comments about what happened are added by me):

if BasePrepareSQL(oCmd, ASQL, AParams, ATypes) or (FExecSQLTab = nil) then
begin
  FDFree(FExecSQLTab); { ← directly calls destructor if object is not nil }
  FExecSQLTab := oCmd.Define; { ← no assignment if command execution raises exception }
end;

Problem was that when a SQL command execution raised exception during storage table definition stage (oCmd.Define), reference to previously destroyed storage table object instance (by FDFree) remained stored in the FExecSQLTab field (as a dangling pointer).

Then when a different command was executed that way, FDFree procedure was called just for that dangling pointer. Hence the access violation.

Way to correct this is replacing line e.g. by:

FDFree(FExecSQLTab);

by:

FDFreeAndNil(FExecSQLTab);

which was done in some later Delphi release.