Folks-

Firebird 3.0, WIndows 10.

The subject says it all.  I have a Delphi routine on the client side that can compute a hash value for a given record of a TDataSet.  I'd like to move this functionality to the server side, in either a SP or a UDF, but it looks like it might be too hard.

Each TDataSet record represents a unique combination of parameters used in a physics model.  Each model has a corresponding parameter table.  Each parameter column is named 'P1," 'P2,', etc.  Depending on the model, the count of columns in the record could be different, so it's impossible to hardcode the incoming parameters to the hashing function -- some sort of dynamic technique must be used.  Not only that, but the number of columns might end up over 100, so it'll be clumsy at best to use some sort of pivot table idea to dump the column values into records in a temp table.  My Delphi implementation looks like this:

function ComputeParameterHash(ds: TDataSet): string;
var
   ms: TMemoryStream;
   i: integer;
   RoundedFieldValue: Single;
   SHA256: TIDHashSHA256;
begin
  { The hash value is a 32-byte SHA256 digest, encoded as lower-case hex,
  resulting in 64 characters. The hash value is formed by rounding floating
  point parameters (doubles) to singles. This implies that nearly exact
  points in parameter space will have their LSBs truncated, resulting in the same hash. }
  Result := '';
  ms := TMemoryStream.Create;
  try
    for i := 1 to ds.FieldCount-1 do
    begin
      // Save rounded value (hence .AsSingle)
      RoundedFieldValue := ds.FieldByName('P'+IntToStr(i)).AsSingle;
      ms.Write(RoundedFieldValue, SizeOf(Single));
    end;
    ms.Seek(0, soBeginning);
    SHA256 := TIDHashSHA256.Create;
    try
      Result := LowerCase(SHA256.HashStreamAsHex(ms));
    finally
      SHA256.Free;
    end;
  finally
    ms.Free;
  end;
end;

Note that I'm filling a memory buffer with a concatenation of binary representations of the incoming parameter values downcast to singles, and then using the Indy library to perform the hash.  I could directly build this as a UDF, if I knew how to get the parameter values over to it.  I'm not sure if a UDF should do SELECTS from temp tables or access cursors and whatnot, or even if it can.  Alternatively, I could translate this into an SP/trigger and of course access the column values directly, perhaps from the 'NEW' context variable, but then I'm not sure how to directly access the binary representations so I can concatenate them together into something (a BLOB variable?) to send to a UDF that could do the hash.

Any suggestions are appreciated.

Elias Sabbagh
Victor Technologies, LLC
ehs@...