I'm trying to insert values taken from a comma-seperated file into an Interbase SQL DB using Array DML. The Problem is, I'm having a plethora of issues with

arithmetic, numeric overflow or string truncation

errors when trying to execute the DML queries, which is most likely due to a loop going too far.

While DML is faster than using conventional loops and pushing a query for each line of the file, it is hard for me to debug as I cannot see the queries being pushed at the end or during step-by-step debugging.

Is there a method for viewing the queries being pushed? FDQuery doesn't seem to offer this possibility as of now, so it's hard for me to tell what went wrong during runtime.

The csv file used can be found here, and here's the code :

if FileExists(OpenDialog1.FileName) then
begin
  Strings := TStringList.Create;
  CurrentLine := TStringList.Create;

  try
        Strings.Clear;
        Strings.LoadFromFile(OpenDialog1.FileName);

        iArraySize := Strings.Count;
        FDQuery1.SQL.Text := 'INSERT INTO tbl_post4 (lngpostid, strname, strdesc, ysnisdefault, memLabelList, memNotesText, lnggroupid, ysnisgroup, dtiCreated, dtiModified) VALUES(:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10);';
        FDQuery1.Params.ArraySize := iArraySize;

           for m := 1 to Strings.Count - 1 do   //starting at second line of file since first is used for column names
           begin
              CurrentLine.Clear;
              Split(',', Strings[m], CurrentLine);
              for n := 0 to CurrentLine.Count - 1 do
              begin //  replace empty with space && escape single quotes if found
                    if CurrentLine[n].IsEmpty then
                    begin
                      CurrentLine[n] := ' ';
                    end;
                    if CurrentLine[n].Contains('''') then
                    begin
                      CurrentLine[n] := StringReplace(CurrentLine[n], '''', '''''', [rfReplaceAll, rfIgnoreCase]);
                    end;

              end;

              FDQuery1.Params[0].AsIntegers[m] := CurrentLine[0].ToInteger;
              FDQuery1.Params[1].AsStrings[m] := CurrentLine[1];
              FDQuery1.Params[2].AsStrings[m] := CurrentLine[2];
              FDQuery1.Params[3].AsBooleans[m] := CurrentLine[3].ToBoolean;
              FDQuery1.Params[4].AsStrings[m] := CurrentLine[4];
              FDQuery1.Params[5].AsStrings[m] := CurrentLine[5];
              FDQuery1.Params[6].AsIntegers[m] := CurrentLine[6].ToInteger;
              FDQuery1.Params[7].AsBooleans[m] := CurrentLine[7].ToBoolean;
              FDQuery1.Params[8].Values[m] := CurrentLine[8];
              FDQuery1.Params[9].Values[m] := CurrentLine[9];


           end; //end for m

        FDConnection1.StartTransaction;
        FDQuery1.Execute(FDQuery1.Params.ArraySize);
        FDConnection1.CommitRetaining;
           
        ShowMessage('Data imported');
        CurrentLine.Free;
        Strings.Free;
  except
     on E : Exception do
        begin
          ShowMessage(E.Message);
          FDConnection1.RollbackRetaining;
          FDConnection1.Commit;
          CurrentLine.Free;
          Strings.Free;
        end;

  end;
  FDConnection1.Commit;

  end
  else
  begin
     raise Exception.Create('File does not exist.');
  end;



  // split function for reference
  procedure Split(Delimiter: Char; Str: string; ListOfStrings: TStrings) ;
  begin
      ListOfStrings.Clear;
      ListOfStrings.Delimiter       := Delimiter;
      ListOfStrings.StrictDelimiter := True; // Requires D2006 or newer.
      ListOfStrings.DelimitedText   := Str;
  end;

CodePudding user response：

In my case, my problem is that I was setting the range for FDQuery1.Exec(FDQuery1.Params.Size, 0) too low, which inserted null records. Simply upping the 0 to 1 fixed my issue.

I did not however find a useful way to monitor for sql errors and extract Array DML queries being sent on the TMonitor.