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

Question

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?

I cannot reproduce this in Tokyo with PostgreSQL 10.1. But I guess I know why. There seems to remain a dangling pointer after FDFree(FExecSQLTab). In Tokyo there is FDFreeAndNil(FExecSQLTab). — Victoria
Great! I made a copy of the file and made the change as you said ... it's working as expected. Thank you very much. Apparently it is not of interest of the embarcadero the stability of the applications made in previous versions of the IDE — AnselmoMS
You're welcome! Not taking care about fix propagation to old versions is typical for EBMT. — Victoria

Victoria Victoria · Accepted Answer · 2018-07-14T21:43:50

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.

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

1 Answers