As a FileMaker developer you may need to mirror records to a MongoDB cluster. You may decide, that every new or modified record is sent to the MongoDB as a backup or mirror. Your web server may run PHP scripts to show content on the web site without using Web Direct here.
To update the MongoDB database when changes are made in FileMaker, you can use script triggers to send the changes to MongoDB. Our MongoDB.InsertOne or MongoDB.UpdateOnefunctions help to move the data. In general you can simply use MongoDB.UpdateOne with the upsert option to perform an insert for new records.
To move initially a few thousand records to MongoDB, you can use MongoDB.InsertMany. We can combine that with our FM.SQL.JSONRecords function to get a set of records as JSON. And we pick these records with SQL using offset and limit to only get a block of records.
Here is a sample script where you connect to MongoDB and then loop over the records in a table to move them all to MongoDB:
# connect to MongoDB and put reference in $Mongo
#
# destination in MongoDB
Set Variable [ $MongoDatabaseName ; Value: "test" ]
Set Variable [ $MongoCollectionName ; Value: "Contacts" ]
#
Set Variable [ $r ; Value: MBS( "MongoDB.OpenDatabase"; $Mongo; $MongoDatabaseName ) ]
Set Variable [ $r ; Value: MBS( "MongoDB.OpenCollection"; $Mongo; $MongoCollectionName ) ]
#
# source in FileMaker
Set Variable [ $filename ; Value: Get(FileName) ]
Set Variable [ $FileMakerTableName ; Value: "Contacts" ]
Set Variable [ $fields ; Value: "First Name¶Last Name¶Photo¶Title¶Company¶Job Title¶Website¶Initial¶PrimaryKey¶CreatedBy¶ModifiedBy¶CreationTimestamp¶ModificationTimestamp" ]
#
# loop over all records
Set Variable [ $row ; Value: 0 ]
Set Variable [ $rowCount ; Value: MBS( "FM.ExecuteFileSQLValue"; $filename; "SELECT COUNT(*) FROM \"" & $FileMakerTableName & "\"" ) ]
Loop [ Flush: Defer ]
# The SQL to get next batch of records
Set Variable [ $sql ; Value: "SELECT \"" & Substitute($fields; ¶; "\", \"") & "\" FROM \"" & $FileMakerTableName & "\" OFFSET " & $row & " ROWS FETCH FIRST 100 ROWS ONLY" ]
Set Variable [ $records ; Value: MBS("FM.SQL.Execute"; $filename; $sql) ]
If [ MBS("FM.SQL.RowCount"; $records) > 0 ]
# convert to JSON and show
Set Variable [ $json ; Value: MBS("FM.SQL.JSONRecords"; $records; $fields) ]
Set Variable [ $r ; Value: MBS( "MongoDB.InsertMany"; $Mongo; $JSON) ]
End If
# release records from memory
Set Variable [ $r ; Value: MBS("FM.SQL.Release"; $records) ]
# next block
Set Variable [ $row ; Value: $row + 100 ]
Exit Loop If [ $row > $rowCount ]
End Loop
Set Variable [ $r ; Value: MBS( "MongoDB.Release"; $Mongo ) ]
The performance depends on the field count and content sizes. But since we move 100 records in one request to the server, we avoid the overhead of doing each record on its own.
Alternatively to using offset and limit in the SQL query, we can loop over RowID. That works better if some records get deleted while the loop runs. RowID is an extra column FileMaker has in SQL that provides the record number. They always count up and are not reused. Our query may ask for a range of 100 RowIDs, but may not get that much if some records were deleted.
# loop over all records using RowID
Set Variable [ $row ; Value: 0 ]
Set Variable [ $MaxRowID ; Value: MBS( "FM.ExecuteFileSQLValue"; $filename; "SELECT Max(ROWID) FROM \"" & $FileMakerTableName & "\"" ) ]
Loop [ Flush: Defer ]
# The SQL to get next batch of records
Set Variable [ $sql ; Value: "SELECT \"" & Substitute($fields; ¶; "\", \"") & "\" FROM \"" & $FileMakerTableName & "\" WHERE RowID >= " & $row & " AND RowID < " & ($row+100) ]
Set Variable [ $records ; Value: MBS("FM.SQL.Execute"; $filename; $sql) ]
If [ MBS("FM.SQL.RowCount"; $records) > 0 ]
# convert to JSON and show
Set Variable [ $json ; Value: MBS("FM.SQL.JSONRecords"; $records; $fields) ]
Set Variable [ $r ; Value: MBS( "MongoDB.InsertMany"; $Mongo; $JSON) ]
End If
# release records from memory
Set Variable [ $r ; Value: MBS("FM.SQL.Release"; $records) ]
# next block
Set Variable [ $row ; Value: $row + 100 ]
Exit Loop If [ $row > $MaxRowID ]
End Loop
Please try and enjoy some bulk transfers. We also have the bulk operation functions. These allow you to call insert a lot of and then send all changes to the server in one big batch with the execute function.