Getting Started¶
Use this script to verify connectivity, synchronize metadata, create your first class table, and update it from M‑Files.
Check connection to the vault
EXEC spMFVaultConnectionTest
-- or
DECLARE @MessageOut NVARCHAR(250);
EXEC dbo.spMFVaultConnectionTest @IsSilent = 1,
@MessageOut = @MessageOut OUTPUT
SELECT @MessageOut
Check general settings
SELECT * from mfsettings
Before you can use the connector, pull the metadata structure into SQL. If you cannot start the sync process then the vault applications may not be fully installed. From Release 4 this operation depends on a valid license.
Synchronise metadata
EXEC spMFSynchronizeMetadata
-- or
DECLARE @ProcessBatch_ID INT;
EXEC dbo.spMFSynchronizeMetadata @ProcessBatch_ID = @ProcessBatch_ID OUTPUT,
@Debug = 0
SELECT * FROM dbo.MFProcessBatch AS mpb WHERE mpb.ProcessBatch_ID = @ProcessBatch_ID
If an error is shown then an email will be sent. If Database Mail is not set up, check the error with the following:
SELECT * FROM mflog ORDER BY logid DESC
During development, or any time after the initial build, use the following to update the structure. Note that this procedure is illustrated again in the next example.
EXEC dbo.spMFDropAndUpdateMetadata
When you want to drop all metadata in SQL then use
EXEC dbo.spMFDropAndUpdateMetadata
@IsResetAll = 1 -- setting to 1 will delete all the current structure data in SQL and reset it to M-Files
This procedure has many more uses and switches for different scenarios—more about it later. The selections below illustrate the default settings of the procedure.
DECLARE @ProcessBatch_ID INT;
EXEC dbo.spMFDropAndUpdateMetadata @IsResetAll = 0,
@WithClassTableReset = 0,
@WithColumnReset = 0,
@IsStructureOnly = 0,
@ProcessBatch_ID = @ProcessBatch_ID OUTPUT,
@Debug = 0
SELECT * FROM dbo.MFProcessBatch AS mpb WHERE mpb.ProcessBatch_ID = @ProcessBatch_ID
SELECT * FROM dbo.MFProcessBatchDetail AS mpbd WHERE mpbd.ProcessBatch_ID = @ProcessBatch_ID
Check structure tables. Explore the results.
View the classes
SELECT *
FROM MFClass
View the properties
SELECT *
FROM MFProperty ORDER BY MFID
View the value lists
SELECT *
FROM MFValueListItems
Other structure tables include: MFObjectType, MFValueList, MFWorkflow, MFWorkflowState, MFLoginAccount, MFUserAccount. The relationships between these tables are explored in another example.
Explore metadata using a view
Review all the properties for a specific class
SELECT *
FROM MFvwMetadataStructure
WHERE class = 'Customer' ORDER BY Property_MFID
Review all the classes for a specific property
SELECT class,*
FROM MFvwMetadataStructure
WHERE Property = 'Customer' ORDER BY class_MFID
Review property and column usage and comparisons
EXEC dbo.spMFClassTableColumns @ErrorsOnly = 0, -- select to only report column errors
@IsSilent = 0,
@MFTableName = 'MFCustomer',
@Debug = 0
-- if is silent = 1 get result in
SELECT * from ##spMFClassTableColumns
To get metadata from M-Files in SQL, it is necessary to create the class tables first.
Create class table
EXEC spMFCreateTable 'Customer'
-- or
EXEC spMFCreateTable @className = 'Customer'
Check the class table. Note that it was created, but has no records
SELECT *
FROM MFCustomer
Check the change in MFClass. The ‘includeinApp’ column was automatically set to 1
SELECT includeinApp , * FROM dbo.MFClass AS mc
Update records in class table
EXEC spMFUpdateTable 'MFCustomer', 1
Check the table again. Note all the special columns created, and how the Connector handles the lookup properties (e.g. country)
SELECT * FROM MFCustomer
the update procedure is at the heart of many operations. It has a number of other filters and flags. The defaults are shown below and the signficance of the options are explained in a later example.
DECLARE @Update_IDOut INT,
@ProcessBatch_ID INT;
EXEC dbo.spMFUpdateTable @MFTableName = 'MFCustomer',
@UpdateMethod = 1,
@UserId = null,
@MFModifiedDate = null,
@ObjIDs = null,
@Update_IDOut = @Update_IDOut OUTPUT,
@ProcessBatch_ID = @ProcessBatch_ID OUTPUT,
-- @SyncErrorFlag = ,
@RetainDeletions = 0,
@IsDocumentCollection = 0,
@Debug = 0
SELECT * FROM dbo.MFProcessBatch AS mpb WHERE mpb.ProcessBatch_ID = @ProcessBatch_ID
SELECT * FROM dbo.MFProcessBatchDetail AS mpbd WHERE mpbd.ProcessBatch_ID = @ProcessBatch_ID
Let’s make an update in SQL
SELECT * FROM dbo.MFValueListItems AS mvli
INNER JOIN dbo.MFValueList AS mvl
ON mvl.ID = mvli.MFValueListID
WHERE mvl.Name = 'country'
UPDATE dbo.MFCustomer
SET process_id = 1, Country_ID = 3
WHERE id = 1
EXEC dbo.spMFUpdateTable @MFTableName = 'MFCustomer',
@UpdateMethod = 0
SELECT * FROM dbo.MFCustomer AS mc