You can use MDrivenServer to read rows from an external SQL-based database and create or update objects in your model through a server-side ViewModel, without an external import program.

When to use SQLImport

Use SQLImport when the source data is available through an SQL query and you want MDrivenServer to import it on a schedule. For example, you can import reference rows from an external database into Class1 whenever an import request is marked as ready.

SQLImport reads the query result into a nesting in the importing ViewModel. The nesting defines the columns that MDrivenServer imports.

Configure the importing ViewModel

Create a ViewModel that contains the import settings, an import nesting, and the actions that start and complete the import.

1. Add a Nesting to the ViewModel for the rows returned by the external query. For example, name the nesting TheImporter.
2. In TheImporter, add a column for each value returned by the SQL query.
3. Put the target object's key attribute in the first column of the nesting. For example, if Class1.Code identifies a row, the first returned column must be Code.
4. Ensure that the nesting columns that must receive imported values are not read-only. Import columns are created read-only by default; read-only columns do not receive values.
5. Add the configuration columns to the importing ViewModel as described below.
6. Add an action named SQLImport. This exact name tells MDrivenServer to run the SQL import.
7. Add any completion action required by your process. For example, an action named Finished can set an import-request attribute from 'todo' to 'Done' after the import.

ViewModel column	Value and purpose
Nesting	The name of the connected import nesting, for example TheImporter. The nesting defines the result-set columns to import.
Connectionstring	The connection string for the external SQL Server database.
Connectionstringodbc	Use this instead of Connectionstring when the external connection must use ODBC.
Query	The SQL query to execute against the external database. You can build the query with values from the rest of the model.
Key	The name, as a string, of the target-class attribute used to identify an existing object. That attribute must be represented by the first column of the import nesting.
CommandTimeout	Optional integer timeout in seconds for the external command. The default is 30 seconds.

Example mapping

Assume that Class1 is local reference data and Class1.Code is its identifying attribute. Configure TheImporter with Code as its first column, followed by the other values to import. Set Key to 'Code' and make the external query return Code as its first result column.

When a returned Code identifies an existing Class1 object, the import can update that object. The key attribute and first result column must stay aligned; changing their order prevents this update mapping from working as intended.

Run the import from a server-side job

Use a periodic server-side job to decide when imports should run. See MDrivenServer periodic server-side actions for periodic-action concepts and setup.

For example, model an import-request class Class2 with an Attribute1 status. Configure the job to evaluate:

Class2.allinstances->select(attribute1='todo')

If the expression returns rows, the job can fetch up to two requests and execute the actions in the importing ViewModel for each request. The SQLImport action reads the external query results. A subsequent Finished action can set Class2.Attribute1 to 'Done', so the completed request no longer matches the job expression.

Use the current nesting-based design

The current design uses one importing ViewModel with a connected nesting that describes the import rows. Older models may instead have a ViewModel column that names a separate import-template ViewModel. Prefer the nesting-based design for new work and when refactoring older imports.

Design	Import-row definition	Recommendation
Current design	A connected nesting in the ViewModel that has the SQLImport action.	Use for new imports.
Older design	A ViewModel column points to a separate ViewModel that describes an import row.	Keep only where an existing model has not yet been refactored.

Import related tables

SQLImport maps the returned values into the defined import rows. When you must import multiple tables and establish associations between them, use the approach in Documentation:SQLImport multiple tables with associations rather than trying to load a large related table into a combobox. That page describes importing key attributes first and connecting objects in a later server-side step or with SQLPassthrough.

Troubleshooting

Symptom	Check
Imported attribute values are empty	Verify that the corresponding nesting columns are not read-only.
Existing objects are not updated	Verify that Key contains the target attribute name and that the same attribute is the first column in both the import nesting and the SQL result.
The external query runs too long	Set CommandTimeout to an appropriate integer number of seconds. If it is omitted, the timeout is 30 seconds.
The source requires an ODBC connection	Use Connectionstringodbc so that the import uses ODBC rather than the SQL Server connection mechanism.
SQL Server reports a duplicate-key error while changing a single link	Follow HowTos:Resolve 'Cannot insert duplicate key row' SQL Error.

See also

• Training:Import data from other SQL servers
• Documentation:SQLImport multiple tables with associations
• Documentation:Import Data
• HowTos:Import Tabular Separated Data Using OCLOperators
• Documentation:Convergence – ViewModels, import, export, multi search, synchronization and Excel