DA and Log SQL

Data Abstract
1

Hi,

for log the SQL I use the OnAfertOpen event of IDADataSet.

The “problem” of this method is that in the ActualSQL parameter not see the values of the SQL parameters. This is an example:

SELECT
“GROUP_CODING_ID”,
“GROUP_CODING_DETAIL_ID”,
“DESCRIPTION”,
“DETAIL”,
“SORTING”,
“PROCEDURE_ID”,
“STUDY_ID”
FROM
“V_SWF_BASE_DATA_FRAME_40”
WHERE
(1=1) AND
(“PROCEDURE_ID” =:pProcedureID) AND
((“STUDY_ID” = :pStudyID) OR (STUDY_ID IS NULL))
ORDER BY
“SORTING”,
“DESCRIPTION”

Is there a way in DA to log all SQL statements with the values of the parameters?

Thank you very much!

3

Hi,

as expected. we don’t replace parameters with constant values because you can’t put some values like blob content into SQL as is. as a result we use parameters.

TDAAfterOpenDatasetEvent = procedure(const Sender: IDADataset; ActualSQL: string; ElapsedMilliseconds: Cardinal) of object;

Why you can’t get values of parameters from Sender ?

  IDADataset = interface
...
    property Params: TDAParamCollection read GetParams;
    function ParamByName(const aName: string): TDAParam;
  end;

if you like, you can replace parameters with their real values with the StringReplace method.

1 Like
4

Thank you very much EvgenyK

I solved like this:

procedure TSWFDAServiceBase.AfterOpenDataset(const Sender: IDADataset; ActualSQL: string; ElapsedMilliseconds: Cardinal);
var
  LLogSQL: string;
begin
  LLogSQL := ActualSQL;
  for var LIndex: Integer := 0 to Sender.Params.Count - 1 do
  begin
    case Sender.Params[LIndex].DataType of
      TDADataType.datString, TDADataType.datFixedChar, TDADataType.datMemo:
        begin
          LLogSQL := LLogSQL + sLineBreak + Format(':%s(string)=%s', [Sender.Params[LIndex].Name, QuotedStr(Sender.Params[LIndex].AsString)]);
        end;
      TDADataType.datDateTime:
        begin
          LLogSQL := LLogSQL + sLineBreak + Format(':%s(datetime)=%s', [Sender.Params[LIndex].Name, QuotedStr(Sender.Params[LIndex].AsString)]);
        end;
      TDADataType.datFloat, TDADataType.datDecimal, TDADataType.datSingleFloat:
        begin
          LLogSQL := LLogSQL + sLineBreak + Format(':%s(float)=%g', [Sender.Params[LIndex].Name, Sender.Params[LIndex].AsFloat]);
        end;
      TDADataType.datCurrency:
        begin
          LLogSQL := LLogSQL + sLineBreak + Format(':%s(currency)=%m', [Sender.Params[LIndex].Name, Sender.Params[LIndex].AsCurrency]);
        end;
      TDADataType.datAutoInc, TDADataType.datInteger, TDADataType.datShortInt, TDADataType.datByte, TDADataType.datWord, TDADataType.datSmallInt, TDADataType.datCardinal:
        begin
          LLogSQL := LLogSQL + sLineBreak + Format(':%s(integer)=%d', [Sender.Params[LIndex].Name, Sender.Params[LIndex].AsInteger]);
        end;
      TDADataType.datLargeInt, TDADataType.datLargeAutoInc, TDADataType.datLargeUInt:
        begin
          LLogSQL := LLogSQL + sLineBreak + Format(':%s(int64)=%d', [Sender.Params[LIndex].Name, Sender.Params[LIndex].AsLargeInt]);
        end;
      TDADataType.datBoolean:
        begin
          LLogSQL := LLogSQL + sLineBreak + Format(':%s(boolean)=%s', [Sender.Params[LIndex].Name, Sender.Params[LIndex].AsString]);
        end;
      TDADataType.datWideString, TDADataType.datWideMemo, TDADataType.datFixedWideChar:
        begin
          LLogSQL := LLogSQL + sLineBreak + Format(':%s(widestring)=%s', [Sender.Params[LIndex].Name, QuotedStr(Sender.Params[LIndex].AsWideString)]);
        end;
      TDADataType.datGuid:
        begin
          LLogSQL := LLogSQL + sLineBreak + Format(':%s(guid)=%s', [Sender.Params[LIndex].Name, QuotedStr(Sender.Params[LIndex].AsGuid.ToString())]);
        end;
      TDADataType.datUnknown:
        begin
          LLogSQL := LLogSQL + sLineBreak + Format(':%s(unknown)', [Sender.Params[LIndex].Name]);
        end;
      TDADataType.datXml:
        begin
          LLogSQL := LLogSQL + sLineBreak + Format(':%s(xml)', [Sender.Params[LIndex].Name]);
        end;
      TDADataType.datBlob:
        begin
          LLogSQL := LLogSQL + sLineBreak + Format(':%s(blob)', [Sender.Params[LIndex].Name]);
        end;
      TDADataType.datCursor:
        begin
          LLogSQL := LLogSQL + sLineBreak + Format(':%s(cursor)', [Sender.Params[LIndex].Name]);
        end;
    end;
  end;
  TSynapseWFServerLog.Log.LogSql(Format('%s - %s:[%d]', [FClientAddess, Sender.Name, ElapsedMilliseconds]), LLogSQL);
end;
2 Likes