How to redirect large amount of output from command executed by CreateProcess?

Question

I need to run a sqlite backup command from the command line. I don't want to use "cmd /c". The command is:

sqlite3.exe MYDB.db .dump > MYDB.bak

I could not find any example on SO that shows how to do this.

Code that I have so far, collected from various SO posts is this, but is very much incomplete:

function StartProcess(const ACommandLine: string; AShowWindow: boolean = True;
  AWaitForFinish: boolean = False): Integer;
var
  CommandLine: string;
  StartupInfo: TStartupInfo;
  ProcessInformation: TProcessInformation;
  StdOutPipeRead, StdOutPipeWrite: THandle;
  Handle: boolean;
begin
   Result := 0;
   FillChar(StartupInfo, SizeOf(TStartupInfo), 0);
   FillChar(ProcessInformation, SizeOf(TProcessInformation), 0);
   StartupInfo.cb := SizeOf(TStartupInfo);

   StartupInfo.hStdInput := GetStdHandle(STD_INPUT_HANDLE);
   StartupInfo.hStdOutput := StdOutPipeWrite;
   StartupInfo.hStdError := StdOutPipeWrite;

   if not(AShowWindow) then
   begin
   StartupInfo.dwFlags := STARTF_USESHOWWINDOW;
   StartupInfo.wShowWindow := SW_SHOWNORMAL;
   end;

   CommandLine := ACommandLine;
   UniqueString(CommandLine);
   Handle := CreateProcess(nil, PChar(CommandLine), nil, nil, False,
   CREATE_NEW_PROCESS_GROUP + NORMAL_PRIORITY_CLASS, nil, nil, StartupInfo, ProcessInformation);

   CloseHandle(StdOutPipeWrite);

   if Handle then


   Result := ProcessInformation.dwProcessId;

   if AWaitForFinish then
   WaitForSingleObject(ProcessInformation.hProcess, INFINITE);

   CloseHandle(ProcessInformation.hProcess);
   CloseHandle(ProcessInformation.hThread);
end;

Since the output from the dump command is very large, I'm not sure how to capture the output from stdout and then redirect it. Redirect it to what? COPY CON? or to a TFileStream.Write?

I've seen this post, but its incomplete with regard to implementing the redirection to the output file. I guess I should ask "What is the most efficient way to implement this?"

If anyone has done this before, please post a code sample illustrating how I can do it.

TIA.

EDIT:

Based on David Heffernan's answer, here is my revised code that indeed works properly:

function StartProcessWithRedirectedOutput(const ACommandLine: string; const AOutputFile: string;
  AShowWindow: boolean = True; AWaitForFinish: boolean = False): Integer;
var
  CommandLine: string;
  StartupInfo: TStartupInfo;
  ProcessInformation: TProcessInformation;
  StdOutFileHandle: THandle;
  ProcessResult: boolean;
begin
  Result := 0;

  StdOutFileHandle := CreateFile(PChar(AOutputFile), GENERIC_WRITE, FILE_SHARE_READ, nil, CREATE_ALWAYS,
    FILE_ATTRIBUTE_NORMAL, 0);
  Win32Check(StdOutFileHandle <> INVALID_HANDLE_VALUE);

  Win32Check(SetHandleInformation(StdOutFileHandle, HANDLE_FLAG_INHERIT, 1));

  try
    FillChar(StartupInfo, SizeOf(TStartupInfo), 0);
    FillChar(ProcessInformation, SizeOf(TProcessInformation), 0);

    StartupInfo.cb := SizeOf(TStartupInfo);
    StartupInfo.dwFlags := StartupInfo.dwFlags or STARTF_USESTDHANDLES;
    StartupInfo.hStdInput := GetStdHandle(STD_INPUT_HANDLE);
    StartupInfo.hStdOutput := StdOutFileHandle;
    StartupInfo.hStdError := StdOutFileHandle;

    if not(AShowWindow) then
    begin
      StartupInfo.dwFlags := StartupInfo.dwFlags or STARTF_USESHOWWINDOW;
      StartupInfo.wShowWindow := SW_HIDE;
    end;

    CommandLine := ACommandLine;
    UniqueString(CommandLine);

    ProcessResult := Win32Check(CreateProcess(nil, PChar(CommandLine), nil, nil, True,
      CREATE_NEW_PROCESS_GROUP + NORMAL_PRIORITY_CLASS, nil, nil, StartupInfo, ProcessInformation));

    if ProcessResult then
    begin
      try
        Result := ProcessInformation.dwProcessId;

        if AWaitForFinish then
          WaitForSingleObject(ProcessInformation.hProcess, INFINITE);

      finally
        if ProcessInformation.hProcess <> INVALID_HANDLE_VALUE then
          CloseHandle(ProcessInformation.hProcess);

        if ProcessInformation.hThread <> INVALID_HANDLE_VALUE then
          CloseHandle(ProcessInformation.hThread);
      end;
    end;

  finally
    CloseHandle(StdOutFileHandle);
  end;
end;

procedure TfAdmin.DoDBBackup(ADBBackupFile: String);
var
  b, p, q: String;
begin

  b := ExtractFilePath(ParamStr(0)) + 'PPDB.bak';
  p := ExtractFilePath(ParamStr(0)) + 'sqlite3.exe';
  q := ExtractFilePath(ParamStr(0)) + 'PPDB.db .dump';

  fMain.UniConnection1.Close;
  try
    StartProcessWithRedirectedOutput(p + ' ' + q, b, True, True);
  finally
    fMain.UniConnection1.Open;
  end;

  ZipMaster1.FSpecArgs.Add(b);
  ZipMaster1.ZipFileName := ADBBackupFile;
  ZipMaster1.Add;

  DeleteFile(b);

  ShowMessage('Backup complete!');

end;

Answer 1

Create a file handle for the redirection. That's what your cmd script does. That redirects to a file named 'MYDB.bak'.

So, call CreateFile to create a file with that name, and assign the handle returned as StartupInfo.hStdOutput. When the external process has finished, call CloseHandle on the file handle to close the file. You'll need to decide what to do about the standard error handle. One common choice is to merge it with standard output. Assign the same handle to both hStdOutput and hStdError.

Your code is assigning standard handles, but not asking that the external process uses them. You need to include STARTF_USESTDHANDLES in StartupInfo.dwFlags.

The call to CreateFile will look like this:

StdOutFileHandle := CreateFile(
  'MYDB.bak',
  GENERIC_WRITE,
  FILE_SHARE_READ,
  nil,
  CREATE_ALWAYS,
  FILE_ATTRIBUTE_NORMAL,
  0
);

Check that the value returned by CreateFile is not equal to INVALID_HANDLE_VALUE.

As I mentioned in your previous question, you need the external process to inherit the file handle that you pass it. If you don't allow inheritance of handles then the external process cannot use the handle that you pass it. So pass True for the bInheritHandles parameter of CreateProcess.

The file handle created by CreateFile is not, by default inheritable. You can either pass security attributes that make it inheritable. Or you can set it explicitly after you have created. The latter looks like this:

Win32Check(SetHandleInformation(StdOutFileHandle, HANDLE_FLAG_INHERIT, 1));

Examples of the former (in the context of pipes) can be seen in my answer here: How to redirect binary gbak output to a Delphi stream?

The code that mentions StdOutPipeWrite all needs to be deleted. It cannot work at the moment because you are not initializing the handle.

You should make good use of try/finally to ensure that you don't leak any handles even in the face of exceptions.

Finally, your code contains a lot of errors, and little error checking. I suggest that you read and re-read the documentation for CreateProcess. Also have a good read of this example on MSDN: http://msdn.microsoft.com/en-us/library/windows/desktop/ms682499.aspx. Although it uses pipes, the principals are the same. Do exactly the same, but instead of pipes use the handle returned by the call to CreateProcess.

Answer 2

For a more complete answer, one that also illustrates input redirection, I'm posting my code. Thanks to David Heffernan for guidance, without which this would not be possible.

The code involves backup and restore of a SQLite database, by calling the Sqlite3.exe executable file using CreateProcess. Obviously input and output needs to be redirected to/from this command and the code below illustrates how to achieve this:

function StartProcessWithRedirectedOutput(const ACommandLine: string; const AOutputFile: string;
  AShowWindow: boolean = True; AWaitForFinish: boolean = False): Integer;
var
  CommandLine: string;
  StartupInfo: TStartupInfo;
  ProcessInformation: TProcessInformation;
  StdOutFileHandle: THandle;
begin
  Result := 0;

  StdOutFileHandle := CreateFile(PChar(AOutputFile), GENERIC_WRITE, FILE_SHARE_READ, nil, CREATE_ALWAYS,
    FILE_ATTRIBUTE_NORMAL, 0);
  Win32Check(StdOutFileHandle <> INVALID_HANDLE_VALUE);
  try
    Win32Check(SetHandleInformation(StdOutFileHandle, HANDLE_FLAG_INHERIT, 1));
    FillChar(StartupInfo, SizeOf(TStartupInfo), 0);
    FillChar(ProcessInformation, SizeOf(TProcessInformation), 0);

    StartupInfo.cb := SizeOf(TStartupInfo);
    StartupInfo.dwFlags := StartupInfo.dwFlags or STARTF_USESTDHANDLES;
    StartupInfo.hStdInput := GetStdHandle(STD_INPUT_HANDLE);
    StartupInfo.hStdOutput := StdOutFileHandle;
    StartupInfo.hStdError := StdOutFileHandle;

    if not(AShowWindow) then
    begin
      StartupInfo.dwFlags := StartupInfo.dwFlags or STARTF_USESHOWWINDOW;
      StartupInfo.wShowWindow := SW_HIDE;
    end;

    CommandLine := ACommandLine;
    UniqueString(CommandLine);

    Win32Check(CreateProcess(nil, PChar(CommandLine), nil, nil, True,
      CREATE_NEW_PROCESS_GROUP + NORMAL_PRIORITY_CLASS, nil, nil, StartupInfo, ProcessInformation));

    try
      Result := ProcessInformation.dwProcessId;

      if AWaitForFinish then
        WaitForSingleObject(ProcessInformation.hProcess, INFINITE);

    finally
      CloseHandle(ProcessInformation.hProcess);
      CloseHandle(ProcessInformation.hThread);
    end;

  finally
    CloseHandle(StdOutFileHandle);
  end;
end;

function StartProcessWithRedirectedInput(const ACommandLine: string; const AInputFile: string;
  AShowWindow: boolean = True; AWaitForFinish: boolean = False): Integer;
var
  CommandLine: string;
  StartupInfo: TStartupInfo;
  ProcessInformation: TProcessInformation;
  StdInFileHandle: THandle;
begin
  Result := 0;

  StdInFileHandle := CreateFile(PChar(AInputFile), GENERIC_READ, FILE_SHARE_READ, nil, OPEN_EXISTING,
    FILE_ATTRIBUTE_NORMAL, 0);
  Win32Check(StdInFileHandle <> INVALID_HANDLE_VALUE);

  try
    Win32Check(SetHandleInformation(StdInFileHandle, HANDLE_FLAG_INHERIT, 1));
    FillChar(StartupInfo, SizeOf(TStartupInfo), 0);
    FillChar(ProcessInformation, SizeOf(TProcessInformation), 0);

    StartupInfo.cb := SizeOf(TStartupInfo);
    StartupInfo.dwFlags := StartupInfo.dwFlags or STARTF_USESTDHANDLES;
    StartupInfo.hStdInput := StdInFileHandle;
    StartupInfo.hStdOutput := GetStdHandle(STD_OUTPUT_HANDLE);
    StartupInfo.hStdError := GetStdHandle(STD_OUTPUT_HANDLE);

    if not(AShowWindow) then
    begin
      StartupInfo.dwFlags := StartupInfo.dwFlags or STARTF_USESHOWWINDOW;
      StartupInfo.wShowWindow := SW_HIDE;
    end;

    CommandLine := ACommandLine;
    UniqueString(CommandLine);

    Win32Check(CreateProcess(nil, PChar(CommandLine), nil, nil, True,
      CREATE_NEW_PROCESS_GROUP + NORMAL_PRIORITY_CLASS, nil, nil, StartupInfo, ProcessInformation));

    try
      Result := ProcessInformation.dwProcessId;

      if AWaitForFinish then
        WaitForSingleObject(ProcessInformation.hProcess, INFINITE);

    finally
      CloseHandle(ProcessInformation.hProcess);
      CloseHandle(ProcessInformation.hThread);
    end;

  finally
    CloseHandle(StdInFileHandle);
  end;
end;

procedure TfAdmin.DoDBBackup(ADBBackupFile: String);
var
  b, p, q: String;
begin
  b := ExtractFilePath(ParamStr(0)) + 'PPDB.bak';
  p := '"' + ExtractFilePath(ParamStr(0)) + 'sqlite3.exe"';
  q := '"' + ExtractFilePath(ParamStr(0)) + 'PPDB.db" .dump';

  fMain.UniConnection1.Close;
  try
    StartProcessWithRedirectedOutput(p + ' ' + q, b, True, True);
  finally
    fMain.UniConnection1.Open;
  end;

  ZipMaster1.FSpecArgs.Add(b);
  ZipMaster1.ZipFileName := ADBBackupFile;
  ZipMaster1.Add;

  DeleteFile(b);

  ShowMessage('Backup complete!');
end;

procedure TfAdmin.DoDBRestore(ADBBackupFile: String);
var
  b, p, q, q2, r: String;
begin
  b := ExtractFilePath(ParamStr(0)) + 'PPDB.bak';
  p := '"' + ExtractFilePath(ParamStr(0)) + 'sqlite3.exe"';
  q := '"' + ExtractFilePath(ParamStr(0)) + 'PPDB.db"';

  ZipMaster1.ExtrBaseDir := ExtractFilePath(ParamStr(0));
  ZipMaster1.ExtrOptions := [ExtrOverWrite];
  ZipMaster1.ZipFileName := ADBBackupFile;
  ZipMaster1.Extract;

  fMain.UniConnection1.Close;
  try
    q2 := StringReplace(q, '"', '', [rfReplaceAll]);
    r := ChangeFileExt(q2, '.db$');
    if FileExists(r) then
      DeleteFile(r);
    if not RenameFile(q2, r) then
      RaiseLastOSError;
    StartProcessWithRedirectedInput(p + ' ' + q, b, True, True);
  finally
    fMain.UniConnection1.Open;
  end;

  DeleteFile(b);

  ShowMessage('Restore complete!');

end;