delphi - ADO 查询导致 mdb 数据库超过 2GB 限制

Question

我使用下面的 Delphi 代码将 Access 数据库中的表替换为 utf=8 格式的大 csv 文件。在我开始之前，DB 是 946MB。一切正常，直到我的表达到一定大小，然后我收到错误。发生这种情况时，数据库已超过 2GB。我猜测 Jet 引擎可能正在创建一个导致溢出的内部副本 - 有什么方法可以阻止这种情况，或者也许有人可以建议一种不同的方法来导入我的数据来避免它？

function TCSVDatabase.ADOFromCSV(ConStr: string): Boolean;
var
  J, K: Integer;
  S: string;
  SN, DN: string;
  DefDir: string;
  DestDir: string;
  TN: TStringList;
begin
  BeginUpdate;
  Result := False;
  DoProgress(0, 'Initializing...');
  DN := ADOSetConnectionString(ConStr);
  if FileExists(DN) then
  begin
    DestDir := ExtractFilePath(DN);
    TN := TStringList.Create;
    Result := True;
    try
      DefDir := FDefaultPath;
      FDefaultPath := DestDir;
      TN.Assign(ADOGetTableNames);
      for K := 0 to TN.Count - 1 do
      begin
        SN := DestDir + TN[K] + '.csv';
        if FileExists(SN) then
        begin
          DoProgress(0, 'Opening "' + TN[K] + '"...');
          FADOTable.Close;
          while FADOTable.Active do;
          FADOTable.Connection := FADOConnection;
          FADOTable.TableName := TN[K];
          FADOTable.Open;
          if FADOTable.Active then
          begin
            Result := True;
            ADOGetFieldNames;
            FADOQuery.Connection := FADOConnection;
            FADOQuery.Recordset := FADOTable.Recordset;
            FADOQuery.Open;
            DoProgress(0, 'Emptying "' + TN[K] + '"...');
            FADOQuery.SQL.Text := 'DELETE * FROM [' + TN[K] + ']';
            FADOQuery.ExecSQL;
            S := ExtractFilePath(SN);
            SetLength(S, Length(S) - 1);
            try
              DoProgress(0, 'Filling "' + TN[K] + '"...');
              FADOQuery.SQL.Text := 'INSERT INTO [' + TN[K] +
                '] SELECT * FROM [' + ExtractFileName(SN) + '] IN "' + S +
                '" "Text;HDR=YES;FMT=Delimited(,);CharacterSet=65001;"';
              FADOQuery.ExecSQL;
            except
              FADOQuery.Connection := nil;
              FADOQuery.Close;
              DoProgress(0, 'SQL error in "' + TN[K] + '"');
{$IFDEF VCL}
              ShowMessage('SQL error in "' + TN[K]);
{$ENDIF}
              Result := False;
              Exit;
            end;
            FADOQuery.Connection := nil;
            FADOQuery.Close;
            for J := 0 to FTemp.Count - 1 do
            begin
              if (FTemp.Codes[J] in [FT_MEMO, FT_WIDEMEMO]) then
              begin
                FADOTable.First;
                while (not FADOTable.EOF) do
                begin
                  S := FADOTable.Fields[J].AsString;
                  if (Pos('_\', S) <> 0) then
                  begin
                    S := StringReplace(S, '_\r\n_', #13#10, [rfReplaceAll]);
                    S := StringReplace(S, '_\r_', #13, [rfReplaceAll]);
                    S := StringReplace(S, '_\n_', #10, [rfReplaceAll]);
                    FADOTable.Edit;
                    FADOTable.Fields[J].AsString := S;
                    FADOTable.Post;
                  end;
                  FADOTable.Next;
                end;
              end;
            end;
          end;
        end;
        DoProgress(0, '');
      end;
    finally
      FADOTable.Close;
      if Result then
      begin
        DoProgress(0, 'Compacting...');
        CompactDatabase(DN, FADOConnection);
      end;
      FDefaultPath := DefDir;
      TN.Free;
    end;
  end;
  DoProgress(0, '');
  EndUpdate;
end;

Answer 1

对我来说，解决方案是清除表并在两个单独的过程中重新填充它们，中间有一个 CompactDatabase。感谢大家的建议，我希望我的解决方案对其他人有帮助:-)

PS 谢谢 Sam，我在测试我的修复后刚刚看到你的答案，这基本上就是你的建议!

function TCSVDatabase.ADOFromCSV(ConStr: string): Boolean;
var
  Err: Cardinal;
  J, K: Integer;
  S: string;
  SN, DN: string;
  DefDir: string;
  DestDir: string;
  TN: TStringList;
begin
  BeginUpdate;
  Result := False;
  DoProgress(0, 'Initializing...');
  DN := ADOSetConnectionString(ConStr);
  if FileExists(DN) then
  begin
    DestDir := ExtractFilePath(DN);
    TN := TStringList.Create;
    Result := True;
    try
      DefDir := FDefaultPath;
      FDefaultPath := DestDir;
      TN.Assign(ADOGetTableNames);
      for K := 0 to TN.Count - 1 do
      begin
        SN := DestDir + TN[K] + '.csv';
        if FileExists(SN) then
        begin
          DoProgress(0, 'Emptying "' + TN[K] + '"...');
          FADOTable.Close;
          while FADOTable.Active do;
          FADOTable.Connection := FADOConnection;
          FADOTable.TableName := TN[K];
          FADOTable.Open;
          if FADOTable.Active then
          begin
            Result := True;
            ADOGetFieldNames;
            FADOQuery.Connection := FADOConnection;
            FADOQuery.Recordset := FADOTable.Recordset;
            FADOQuery.Open;
            FADOQuery.SQL.Text := 'DELETE * FROM [' + TN[K] + ']';
            FADOQuery.ExecSQL;
          end;
        end;
        DoProgress(0, '');
      end;
      DoProgress(0, 'Compacting...');
      CompactDatabase(DN, FADOConnection);
      DN := ADOSetConnectionString(ConStr);
      for K := 0 to TN.Count - 1 do
      begin
        SN := DestDir + TN[K] + '.csv';
        if FileExists(SN) then
        begin
          DoProgress(0, 'Opening "' + TN[K] + '"...');
          FADOTable.Close;
          while FADOTable.Active do;
          FADOTable.Connection := FADOConnection;
          FADOTable.TableName := TN[K];
          FADOTable.Open;
          if FADOTable.Active then
          begin
            Result := True;
            ADOGetFieldNames;
            FADOQuery.Connection := FADOConnection;
            FADOQuery.Recordset := FADOTable.Recordset;
            FADOQuery.Open;
            S := ExtractFilePath(SN);
            SetLength(S, Length(S) - 1);
            try
              DoProgress(0, 'Filling "' + TN[K] + '"...');
              FADOQuery.SQL.Text := 'INSERT INTO [' + TN[K] +
                '] SELECT * FROM [' + ExtractFileName(SN) + '] IN "' + S +
                '" "Text;HDR=YES;FMT=Delimited(,);CharacterSet=65001;"';
              FADOQuery.ExecSQL;
            except
              FADOQuery.Connection := nil;
              FADOQuery.Close;
              DoProgress(0, 'Error in "' + TN[K] + '"');
              ShowMessage('Error in "' + TN[K] + '"' );
              Result := False;
              Exit;
            end;
            FADOQuery.Connection := nil;
            FADOQuery.Close;
            for J := 0 to FTemp.Count - 1 do
            begin
              if (FTemp.Codes[J] in [FT_MEMO, FT_WIDEMEMO]) then
              begin
                FADOTable.First;
                while (not FADOTable.EOF) do
                begin
                  S := FADOTable.Fields[J].AsString;
                  if (Pos('_\', S) <> 0) then
                  begin
                    S := StringReplace(S, '_\r\n_', #13#10, [rfReplaceAll]);
                    S := StringReplace(S, '_\r_', #13, [rfReplaceAll]);
                    S := StringReplace(S, '_\n_', #10, [rfReplaceAll]);
                    FADOTable.Edit;
                    FADOTable.Fields[J].AsString := S;
                    FADOTable.Post;
                  end;
                  FADOTable.Next;
                end;
              end;
            end;
          end;
        end;
        DoProgress(0, '');
      end;
    finally
      FADOTable.Close;
      if Result then
      begin
        DoProgress(0, 'Compacting...');
        CompactDatabase(DN, FADOConnection);
      end;
      FDefaultPath := DefDir;
      TN.Free;
    end;
  end;
  DoProgress(0, '');
  EndUpdate;
end;