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;

See also http://stackoverflow.com/q/9119999/33732 and http://stackoverflow.com/q/19019714/33732. And http://stackoverflow.com/q/19054789/33732. All three contain code to run a console program and capture the output with your program. — Rob Kennedy, Oct 06 '13 at 19:01
You raise an exception, and then call exit. That's not how exceptions work. Once you raise the exception, control moves up the chain of exception and finally handlers. Also, use `RaiseLastOSError` or `Win32Check` to raise errors that can be identified by a call to `GetLastError`. — David Heffernan, Oct 06 '13 at 19:16
And now you added an equally pointless call to `RaiseLastOSError` that can never execute because you already raised an exception. All you need is a call to `RaiseLastOSError`. That's the call that raises the exception. Personally I would replace that entire `if` statement with `Win32Check(StdOutFileHandle <> INVALID_FILE_HANDLE);` — David Heffernan, Oct 06 '13 at 19:22
OK, now the call to `Win32Check` is better, but in the wrong place. Should be before the `try`. Think what happens if that `Win32Check` fails. Look at the finally block. Yes, you just called `CloseHandle(INVALID_FILE_HANDLE)`. — David Heffernan, Oct 06 '13 at 19:39
OK, some more error handling. Why is the return value of CreateProcess called `Handle`. That function returns a boolean! The variable is typed as a boolean? Why did you put the try before the call to `CreateProcess`. If `CreateProcess` fails, it will not initialise the two handles and so they should not be closed. Only close the handles if you know they've been initialised. Put the try after you check that `CreateProcess` returns true. You can write `Win32Check(CreateProces(...))`. — David Heffernan, Oct 06 '13 at 19:46

score 5 · Accepted Answer · edited May 23 '17 at 11:53

5

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.

edited May 23 '17 at 11:53

Community

1
1

answered Oct 06 '13 at 18:34

David Heffernan

601,492
42
1,072
1,490

Thanks for replying. I've edited the question to include new code based on your answer. Now, it is showing a window with the output scrolling. That means something is still wrong. What change is needed to fix it? – Steve F Oct 06 '13 at 19:15
Read all of my answer again. I know I updated it, but all the same, you've only taken on board some of the points I make. For example, you don't set `STARTF_USESTDHANDLES`. Also, do read the documentation, and do read the example code in the link in my final paragraph. And your finally block is all wrong. You are calling `CloseHandle` on handles that may not be initialized. Surely the compiler warns you about that. Did you enable warnings? – David Heffernan Oct 06 '13 at 19:17
Code updated with suggested corrections, but still not working.. Output file is being created but the size is 0 bytes. – Steve F Oct 06 '13 at 19:33
1

You set `StartupInfo.dwFlags := STARTF_USESHOWWINDOW` and so obliterate the setting to `STARTF_USESTDHANDLES`. Use logical `or`. But I suspect that code does not execute. Next step is for you to do some debugging and add some error checking. After every API call, check the return value. Read docs to work out how to interpret the return value. Use `Win32Check`, for instance, to raise exceptions on failures. Find out how far the code is getting before you start having errors. – David Heffernan Oct 06 '13 at 19:37
I've read the code sample on the MSDN link, but there some extra bit of work being done regarding creating input and output pipes. Is that needed in my code as well? – Steve F Oct 06 '13 at 19:41
The call to CreateProcess is doing its work. This I know because there is a long delay where its dumping the database table data. Its the output redirection that has an issue. Question is where? – Steve F Oct 06 '13 at 19:48
Hmm, I cannot see the problem. Will keep digging. – David Heffernan Oct 06 '13 at 20:08
OK, I've got it. The file handle is not inheritable by default. I'll add an update. – David Heffernan Oct 06 '13 at 20:13
1

Sorry it took me so long to realise the error. I even mentioned in the answer that handles needed to be inheritable, and then failed to make it so! Anyway, some good lessons here. You did a really good job of tidying up the code and adding error checking. That helped us remove lots of failure modes. And that helped narrow it down. Thank you! – David Heffernan Oct 06 '13 at 20:19

Steve F · Answer 2 · 2013-10-06T23:08:34.563

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;

My last note to this series and for future visitors. Don't use SQLite command line with output redirection to backup SQLite database. Prefer to use [`SQLite Online Backup API`](http://www.sqlite.org/backup.html) for that. The code might have been almost 3 times shorter than this and what is most important, could be native! — TLama, Oct 06 '13 at 21:09
Yes second function needs corrections. I'll leave that to you! — David Heffernan, Oct 06 '13 at 22:42
Done. I also made corrections to the code in DoDBBackup and DoDBRestore to illustrate the correct way to pass file paths. It turns out that if the paths for the executable file and parameters ie PChar(CommandLine) passed in to CreateProcess contain spaces, then we get a Windows error. This is solved by enclosing the path in double-quotes. — Steve F, Oct 06 '13 at 23:06
@SteveF Well that's to be expected. The space is used as a delimiter between arguments. You don't need to quote the first parameter to CreateProcess IIRC since it is always a single path. — David Heffernan, Oct 07 '13 at 10:36

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

2 Answers2

Linked