Using ShellExecute to send sqlite3.exe a shell script

Question

I've got the following shell script in file c:/SQLiteData/Cmnds.txt

.open c:/SQLiteData/LGMeta.db
create table Temp {f INTEGER primary key};
insert into Temp values(-1);
.output c:/SQLiteData/Out.txt
select * from Temp;

I tried running it inside a c++ programme using

ShellExecute(NULL, L"open", L"c:/SQLiteData/sqlite3.exe",
L".read c:/SQLiteData/Cmnds.txt", NULL, 0);

ShellExecute returns 42 which suggests success but nothing happens. Neither Temp or Out.txt are created. Can anyone tell me what I'm missing?

EDIT

Thanks for the replies. I've spent the last 3 days tearing my hair out with this stuff. I patched this together from several posts on the subject

unsigned int RunCmnd(String CmndLine)
{
    STARTUPINFO StartupInfo;
    PROCESS_INFORMATION ProcessInfo;

    memset(&ProcessInfo,0,sizeof(ProcessInfo)); // setup memory blocks
    memset(&StartupInfo,0,sizeof(StartupInfo));

    StartupInfo.cb=sizeof(StartupInfo); // set structure size
    StartupInfo.wShowWindow=SW_HIDE; // hide window

    if (CreateProcess(NULL,CmndLine.c_str(),
    NULL,NULL,false,0,NULL,NULL,&StartupInfo,&ProcessInfo))
    {
        WaitForSingleObject(ProcessInfo.hThread,INFINITE);
        return 0;
    }
    else return GetLastError();
}

If I start up the command shell and enter the following line

c:/SQLiteData/sqlite3.exe < c:/SQLiteData/Cmnds.txt

everything works as expected BUT if use

RunCmnd("c:/SQLiteData/sqlite3.exe < c:/SQLiteData/Cmnds.txt")

from within my c++ builder app nothing happens. I'm missing something fundamental here. Can anyone tell me what?

cannot you use a simpler way to running your command? like `system` or `execvp` ? — Jean-François Fabre, Oct 22 '16 at 17:01
You shouldn't really be using `ShellExecute`. Use `ShellExecuteEx` (or `CreateProcess`) instead. Anyway, you are passing the wrong verb. You **don't** want to *open* the file; you are trying to *execute* it. Pass `NULL` for *lpOperation* instead. — IInspectable, Oct 22 '16 at 18:21
All the more reason to use `CreateProcess()` instead. `ShellExecute/Ex()` will just call it anyway, so best to remove the middleman. — Remy Lebeau, Oct 22 '16 at 20:37
What's wrong with SQLite3. _dll_ ? [(hint)](https://github.com/stijnsanders/TSQLite/blob/master/SQLiteBatch/SQLiteBatchMain.pas) — Stijn Sanders, Oct 22 '16 at 21:01
When you type `sqlite3 < c:/SQLiteData/Cmnds.txt` the redirection is first processed by the command processor. The sqlite utility does not get passed those as arguments. When you call CreateProcess, you need to set up STDIN for the process yourself. Or make "cmd.exe" your executable and pass "/c sqlite3 < c:/SQLiteData/Cmnds.txt" as an argument in order to have cmd.exe process your redirection. — Klitos Kyriacou, Oct 26 '16 at 09:44
Thanks Klitos. I tried **CreateProcess("c:/Windows/,System32/cmd.exe",CmndLine.c_str(), NULL,NULL,false,0,NULL,NULL,&StartupInfo,&ProcessInfo)** but still nothing happens despite no error message. — NoComprende, Oct 26 '16 at 10:40
Well, there are two errors. First, although Windows supports both the backslash and `/` as path separator characters, `cmd.exe` supports only the backslash (because, for historical reasons, it already uses `/` for options, just like Unix uses `-`). Second problem is that you forgot to pass `/c` in front of the command line which `cmd.exe` requires when it is started to execute a command. Try `CreateProcess("c:/Windows/System32/cmd.exe","/c C:\SQLiteData\sqlite3.exe < C:\SQLiteData\Cmnds.txt", NULL,NULL,false,0,NULL,NULL,&StartupInfo,&ProcessInfo)` — Klitos Kyriacou, Oct 26 '16 at 12:45
That worked (with double backslashes). Thanks for sticking with me Klitos. — NoComprende, Oct 26 '16 at 15:37
I'm still unsure as to the exact workings of this.1) Is this the same thing as starting command shell and then typing in the CmndLine parameter> — NoComprende, Oct 27 '16 at 08:11
I'm still unsure as to the exact workings of this. 1) Is this the same thing as starting command shell and then typing in the CmndLine parameter? 2) Given sqlite3 is itself an executable is there a way of cutting cmd.exe out of the equation. 3) After it processes the Cmnds.txt file the rows of any select query seem to be glued together whereas they're separated by line feeds within the sqlite3 command shell. Is this because the txt file is being processed by cmd.exe rather than sqlite3.exe and is there a way to correct this? — NoComprende, Oct 27 '16 at 08:19

score 0 · Answer 1 · edited May 23 '17 at 12:13

Finally came up with this which was adapted from an excellent post by TarmoPikaro on this thread How to execute a command and get output of command within C++ using POSIX?.

String TempFileName(bool ForwardSlash) // ForwardSlash default = true
{
    wchar_t Nm[MAX_PATH],Path[MAX_PATH];
    GetTempPath(MAX_PATH,Path);
    if (!GetTempFileName(Path,L"",0,Nm))
        throw Exception(String("TempFileName failed - ")+
        SysErrorMessage(GetLastError()));
    String Name=Nm;
    if (ForwardSlash)
        for (int Len=Name.Length(),i=1; i<=Len; i++) if (Name[i]=='\\') Name[i]='/';
    return Name;
}
//---------------------------------------------------------------------------
String SQLiteExe(String DB,String OutFile,String Cmds)
{
    // Returns first error message if process doesn't execute cleanly. Otherwise
    // if OutFile=="" it returns the output
    // if Outfile>"" the output is sent to OutFile and return is NULL.
    String Output;
    HANDLE hPipeRead,hPipeWrite;

    SECURITY_ATTRIBUTES saAttr={sizeof(SECURITY_ATTRIBUTES)};
    saAttr.bInheritHandle=TRUE; //Pipe handles are inherited by child process.
    saAttr.lpSecurityDescriptor=NULL;

    // Create a pipe to get output from child's stdout.
    if (!CreatePipe(&hPipeRead,&hPipeWrite,&saAttr,0))
        return "Error: Unable to create pipe";

    STARTUPINFO si={sizeof(STARTUPINFO)};
    si.dwFlags=STARTF_USESHOWWINDOW | STARTF_USESTDHANDLES;
    si.hStdOutput=hPipeWrite;
    si.hStdError=hPipeWrite;
    si.wShowWindow=SW_HIDE;
    // Prevents cmd window from flashing. Requires STARTF_USESHOWWINDOW in dwFlags.

    TStringList *Batch=new TStringList;
    Batch->StrictDelimiter=true;
    Batch->Delimiter=';';
    Batch->DelimitedText=Cmds;
    if (OutFile>"") Batch->Insert(0,".output "+OutFile);
    String S;
    for (int i=0; i<Batch->Count; i++)
    {
        Batch->Strings[i]=Batch->Strings[i].Trim();
        // .commands must have dot as first char on line
        if (Batch->Strings[i]=="") continue;
        S+=Batch->Strings[i]+(Batch->Strings[i][1]=='.' ? "" : ";")+"\r\n";
    }
    Batch->Text=S;
    String BatchFile=TempFileName();
    Batch->SaveToFile(BatchFile);
    delete Batch;
    String Cmd="sqlite3 "+DB+" \".read "+BatchFile+"\""; // assumes sqlite3.exe in PATH

    PROCESS_INFORMATION pi={0};

    BOOL fSuccess=CreateProcessW(NULL,Cmd.c_str(),NULL,NULL,TRUE,CREATE_NEW_CONSOLE,
    NULL,NULL, &si, &pi);
    if (! fSuccess)
    {
        CloseHandle(hPipeWrite);
        CloseHandle(hPipeRead);
        DeleteFile(BatchFile);
        return "Error: Failed to create process";
    }

    bool bProcessEnded=false;
    while (!bProcessEnded)
    {
        // Give some timeslice (50ms), so we won't waste 100% cpu.
        bProcessEnded=WaitForSingleObject(pi.hProcess,50)==WAIT_OBJECT_0;

        // Even if process exited - we continue reading, if there is some data available
        // over pipe.
        while (true)
        {
            char buf[1024];
            DWORD dwRead=0;
            DWORD dwAvail=0;

            if (!::PeekNamedPipe(hPipeRead,NULL,0,NULL,&dwAvail,NULL)) break;
            if (!dwAvail) break; // no data available, return
            if (!::ReadFile(hPipeRead,buf,std::min(sizeof(buf)-1,
            (unsigned int)(dwAvail)),&dwRead,NULL) || !dwRead)
                break; // error, the child process might ended

            buf[dwRead]=0;
            Output+=buf;
        }
        int p=Output.Pos("Error:"); // if (p) return first error  message
        if (p) {Output.Delete(1,p-1); Output.Delete(Output.Pos("\r\n"),Output.Length());}
    }
    DeleteFile(BatchFile); // NB can't be deleted until ProcessEnded
    CloseHandle(hPipeWrite);
    CloseHandle(hPipeRead);
    CloseHandle(pi.hProcess);
    CloseHandle(pi.hThread);
    return Output;
}
//---------------------------------------------------------------------------
Usage
String Err=SQLiteExe("c:/SQLiteData/MyDB.db","c:/SQLiteData/MyTblDump.txt"",
".mode csv; select * from MyTbl;");
if (Err>"") throw Exception(Err);
else ........

Multiple commands should be separated by semi-colons. SQLiteExe automatically removes the redundant semi-colons at the end of lines starting with a dot.

Using ShellExecute to send sqlite3.exe a shell script

1 Answers1