從MSAccess連接到外部mongodb

有一個類似的執行緒，我喜歡那里的一個答案，即使用 shell 的那個。但它似乎連接到一個正在運行的 mongo 實體。

就我而言，沒有正在運行的實體，Mongo db 在其他地方，我不知道如何使用這個腳本連接到它。我想我需要一種方法來使用類似于下面的方法將連接字串添加到外部 MongoDB。

如何從 Excel 連接 Mongodb

這是答案

Shell 方法 幾乎所有與命令列介面的東西都可以通過 Shell 訪問。

這是一個連接到正在運行的 MongoDB 實體并將查詢列印到即時視窗的簡單示例。您需要添加對 Windows 腳本宿主物件模型的參考。

Private Sub Test()

    Dim wsh As New WshShell
    Dim proc As WshExec
    Dim line As String
    
    Set proc = wsh.Exec("mongo")
    
    With proc
        .StdIn.WriteLine "use test"
        .StdIn.WriteLine "db.restaurants.find({""address.zipcode"":""10075""})"
        .StdIn.WriteLine "quit()"
        
        Do While .Status = WshRunning
            line = .StdOut.ReadLine
            If line = "Type ""it"" for more" Then
                .StdIn.WriteLine "it"
            ElseIf line Like "{*" Then
                Debug.Print line
            End If
            DoEvents
        Loop
    End With
End Sub

Just printing the raw JSON strings isn't very exciting or useful, however. You could write your own JSON parser but, for this example, we will use VBA-JSON by Tim Hall (you can find it on GitHub).

At the time of writing, there is one issue with VBA-JSON that has to be tackled when using it to parse strings returned from MongoDB. Any values that contain parentheses, e.g. "_id": ObjectId("..."), will throw an error. A quick and dirty fix for this is to use RegEx to clean the string for the parser. You will need to reference the Microsoft VBScript Regular Expressions 5.5 library for the following function to work.

Private Function CleanString(str As String) As String

    Dim temp As String
    Dim rx As New RegExp
    
    With rx
        .IgnoreCase = True
        .Global = True
        
        .Pattern = "[a-z]*\(" ' Left
        temp = .Replace(str, "")
        .Pattern = "\)" ' Right
        temp = .Replace(temp, "")
    End With
    
    CleanString = temp
End Function

We can then parse the JSON returned from MongoDB and add each object to a Collection. Accessing the values becomes quite simple.

Private Sub Mongo()

    Dim wsh As New WshShell
    Dim proc As WshExec
    Dim line As String
    Dim response As New Collection
    Dim json As Object
    
    Set proc = wsh.Exec("mongo")
    
    With proc
        .StdIn.WriteLine "use test"
        .StdIn.WriteLine "db.restaurants.find({""address.zipcode"":""10075""})"
        .StdIn.WriteLine "quit()"
        
        Do While .Status = WshRunning
            line = .StdOut.ReadLine
            If line = "Type ""it"" for more" Then
                .StdIn.WriteLine "it"
            ElseIf line Like "{*" Then
                response.Add ParseJson(CleanString(line))
            End If
            DoEvents
        Loop
    End With
    
    For Each json In response
        Debug.Print json("name"), json("address")("street")
    Next
End Sub

... Which will produce the following output from the MongoDB Example Dataset.

Nectar Coffee Shop          Madison Avenue
Viand Cafe                  Madison Avenue
Don Filippo Restaurant      Lexington Avenue
Lusardi'S Restaurant        Second Avenue
Due                         Third Avenue
Lenox Hill Grill/Pizza      Lexington Avenue
Quatorze Bistro             East   79 Street
Luke'S Bar & Grill          Third Avenue
Starbucks Coffee            Lexington Avenue
New York Jr. League         East   80 Street
Doc Watsons                 2 Avenue
Serafina Fabulous Pizza     Madison Avenue
Canyon Road Grill           1 Avenue
Sushi Of Gari East          78 Street

Gotchas

ReadLine and WriteLine are blocking functions. The window opened by Exec can't be hidden. A workaround for both of the above would be to use a two-layer approach, where VBA calls a hidden script using wsh.Run, which then runs the Exec (as well as any other code that interacts with the proc). The downside to this approach is that StdIn (and to an extent StdOut) has to be written to a file.

uj5u.com熱心網友回復：

要連接到外部 MongoDB，只需調整 Windows Shell 呼叫以指向外部地址。根據MongoDB 檔案，mongo它本身默認為 localhost 的 27017 埠。對于遠程主機，請調整這些默認值。

使用連接字串：

Set proc = wsh.Exec("mongo ""mongodb://username:password@host:port/database""")

使用args：

Set proc = wsh.Exec("mongo --host <server_or_ip_address>" _
                  & "      --port <port_number>" _
                  & "      --username <username>" _
                  & "      --password <password>")

mongo即使沒有在本地設定資料庫，上面也需要在客戶端機器上安裝 shell。此外，托管 MongoDB 的服務器必須允許外部連接。閱讀檔案以獲取設定和說明。

標籤：vba mongodb ms-access

上一篇：Mongo按開始和結束日期重疊的日期過濾

下一篇：SyntaxError:Unexpectedtoken'{'in__dirname\views\admin\users.ejswhilecompileejs