Why is it not possible to give a shell input after reading a blank line in an Excel macro?-CodePudding

I am new to VBA and I would like to query a MongoDB database multiple times through the shell. However, when the output of a query is blank (because it doesn't exist in the database), I cannot seem to execute another command through that same shell instance. Does anyone why this is the case and how to solve it?

Dim wsh As New WshShell
Dim proc As WshExec
Dim i As Integer
Dim result As String

Set proc = wsh.Exec("mongo")
proc.StdIn.WriteLine "use mydb"

For i = 1 To 4
    If i = 3 Then
        proc.StdIn.WriteLine "db.mycollection.find({'field': 'something non-existent'})"
    Else
        proc.StdIn.WriteLine "db.mycollection.find({'field': '" & i & "'})"
    End If
    result = proc.StdOut.ReadLine
    Debug.Print i
    Debug.Print result
Next i

proc.StdIn.WriteLine "quit()"

Export:

1
{something}
2
{something}
3

4

So for iteration 3 and 4 I only get a blank line and the shell window doesn't close at the end. If you remove the if statement (so that query does find something), every iteration shows a result and the shell closes at the end.

CodePudding user response：

I found a solution to my problem. The problem was that if the result of the query is blank or null and you try to read the line of stdout, the pointer moves to the end of the stream. Which means that the "file" is read.

Basically, I needed to make sure it never returns blank/null. So, I did this:

Dim wsh As New WshShell
Dim proc As WshExec
Dim i As Integer
Dim check As String
Dim result As String

Set proc = wsh.Exec("mongo")
proc.StdIn.WriteLine "use mydb"

For i = 1 To 4
    proc.StdIn.WriteLine "db.mycollection.find({'field': '" & i & "'}).count()"

    check = proc.StdOut.ReadLine

    If CInt(check) > 0 Then ' Now you are sure it will result in something.
        proc.StdIn.WriteLine "db.mycollection.find({'field': '" & i & "'})"
        result = proc.StdOut.ReadLine
    End If
Next i

proc.StdIn.WriteLine "quit()"
proc.StdOut.ReadLine

Maybe it is not the best or most elegant way to get rid of the "StdOut.AtEndOfStream" issue, but it is a simple one. This way you won't have to call a separate script and/or use separate input/output files.