Connection to External Database

On the Connection to External Database tab you can define the object type to use a connection to an external database.

Note: Normally, external object types can only be added or modified with login accounts having the "System administrator" server role. In addition, vault users having the rights "Full control of vault" or "Change metadata structure" can be configured to have access to modifying and adding external object types. Please ask our customer support for additional information.

Note: When using M-Files API, it is advisable to use internal IDs instead of display IDs for referring to objects that reside in an external database. This is because the display ID and internal ID are usually different for an object that has been imported from an external database. The internal ID of an object is always unique.

The "Connection to External Database" tab of the "New Object Type" dialog.

Video: Importing from Databases

Use a connection to an external database to import and modify objects that reside in the external database

To import and edit objects in an external database, establish a connection to it. M-Files can be configured to connect to, for instance, the company customer database.

OLE DB connection string (from server)

M-Files Server uses the connection definition to establish an OLE DB connection to an external database. The syntax of the connection definition depends on the OLE DB supplier used to establish the connection to the external database. If Open Database Connectivity (ODBC) is required to establish a connection, the data store has to be accessed over OLE DB and ODBC. The specific connection definition depends on the database.

Note that for technical reasons, M-Files Admin displays those OLE DB providers that are available on the computer running M-Files Admin. However, the M-Files server can reside on a different host. Ensure that the selected OLE DB connection definition works from the M-Files server.

Provider recommendations

Database	Provider
MS SQL Server	Microsoft OLE DB Provider for SQL Server.
Access	Microsoft JET OLE DB Provider.
Excel	Microsoft JET OLE DB Provider. Also, set Extended Properties to "Excel 8.0;". Use the Define name functionality to define the data area in Excel. This named Excel data area corresponds to a database table. The values in the first row of the data area become the column titles of a table.
MySQL	Microsoft OLE DB Provider for ODBC Drivers (MySQL Connector/ODBC). Use the Data sources (ODBC) administrative tool to configure a new system data source. Select MySQL Connector/ODBC as the ODBC driver. Define the data source. Under driver properties, select the Disable Transactions check box. In connection settings, select Microsoft OLE DB Provider for ODBC Drivers as the provider and the system data source you defined as the data source. The default collection in the connection settings remains empty. Thus you only define the database in the driver settings. You can also use MySql.OLEDB Provider with MySQL.

Note: The Microsoft OLE DB Provider for Jet and the Jet ODBC driver are available in 32-bit versions only. If you need to use either of these in a 64-bit environment for connecting to an external Excel or Access resource, we recommend installing the 64-bit version of Microsoft Access Database Engine 2010 Redistributable or running M-Files Server in the 32-bit mode.

SELECT statement

M-Files Server uses the SELECT statement to retrieve records from an external database.

Examples of SELECT statements:

SELECT CustomerNumber, CustomerName FROM Customer

SELECT ID, Name + ' ' + Department FROM Company

SELECT ID, Name, CustomerID FROM Contacts

SELECT * FROM Customer

Update columns

Fetches the data defined in the SELECT statement into the Columns field.

Columns

The Columns field displays correspondences between columns fetched from an external database and document vault property definitions. You can edit the correspondences between source columns and target properties. Object types must always correspond to a unique ID and name. Subtypes must also correspond to the columns of the owner object. If the external database field can be updated using the UPDATE database statement, you can define the Update field accordingly. The Add field is used to specify the fields that can be added to the external database via the INSERT INTO statement.

UPDATE statement

When you edit an object in M-Files, M-Files Server edits the corresponding record in the external database using an UPDATE statement. Use a question mark (?) to signal columns to be updated.

Examples of UPDATE statements:

UPDATE Customers SET CustomerName = ? WHERE CustomerID = ?

UPDATE Contact SET Name = ?, CustomerID = ? WHERE ContactID = ?

INSERT INTO statement

When you create a new object in M-Files, M-Files Server adds a corresponding record in the external database using an INSERT INTO statement. Use a question mark (?) to indicate the value of each column.

Examples of INSERT INTO statements:

INSERT INTO Customers( CustomerName ) VALUES( ? )

INSERT INTO ContactPersons( Name, CustomerID ) VALUES( ?, ? )

Note: The INSERT INTO statement input to M-Files does not define a value for the ID column. The database should be set up to automatically provide an ID for new records. For example: in SQL Server databases, set the type of the ID column as identity; in Access databases, use an AutoNumber type column for IDs. Because Excel cannot produce new ID values, the INSERT INTO statement cannot be used with Excel.

DELETE statement

When you delete an object from M-Files, M-Files Server deletes the corresponding record in the external database using a DELETE statement. Use a question mark (?) for the ID of the record to be deleted.

Examples of DELETE statements:

DELETE FROM Customers WHERE CustomerID = ?

DELETE FROM Contacts WHERE ContactID = ?

Disabled

You can temporarily disable the external connection by selecting the Disabled checkbox.