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