Training:SQLExport from MDriven Server

SQLExport from MDriven Server

This page was created by Hans.karlsen on 2019-02-28. Last edited by Wikiadmin on 2026-07-29.

You can use SQLExport in an MDrivenServer periodic server-side action to insert or update model data in an external SQL database without running a separate export program.

SQLExport is a server-side action recognized by MDrivenServer. It evaluates a ViewModel and writes each collected row in a designated data nesting to another SQL database. Use it when, for example, an Order in your MDriven model must be replicated to an integration database every few minutes.

This page describes the action requirements. For the canonical topic page, see Documentation:SQLExport from MDriven Server.

How SQLExport works

For each row in the data nesting, SQLExport first runs the select-count query:

If queryforselectcount returns 0, MDrivenServer runs queryforinsert.
If the count is not 0, MDrivenServer runs queryforupdate.

This provides an insert-or-update pattern. The queries and the external database connection are supplied by ViewModel columns, so the export runs as part of MDrivenServer rather than requiring an external scheduled component.

Required ViewModel structure

Create a ViewModel for the server-side job with the connection information on its root and a collected nesting that supplies the export rows.

Location	Required name	Purpose
Root ViewModel	`connectionstring`	Connection string for the external SQL database.
Root ViewModel	`connectionstringodbc`	Optional alternative to `connectionstring`. When this column is used, SQLExport uses an ODBC connection instead of a SQL Server connection.
Root ViewModel	`CommandTimeout`	Optional command timeout in whole seconds. The default is `30` seconds.
Root ViewModel	A nesting whose name starts with `data`	The rows to export. The name match is case-insensitive; for example, `data` or `DataRows`.
Data nesting	`queryforselectcount`	SQL query that determines whether the target row already exists.
Data nesting	`queryforinsert`	SQL query to run when the select-count query returns `0`.
Data nesting	`queryforupdate`	SQL query to run when the select-count query returns a value other than `0`.

The names data, connectionstring, connectionstringodbc, queryforselectcount, queryforinsert, and queryforupdate are action conventions. Keep the query columns in the data nesting, not on the root ViewModel.

Configure an export

Create the ViewModel that the periodic server-side job will execute.
Add a root column named connectionstring for a SQL Server connection, or connectionstringodbc when the target must be reached through ODBC.
If an export command can take longer than 30 seconds, add a root CommandTimeout column and provide the timeout as an integer number of seconds.
Add a nesting with a name beginning with data.
Make the nesting collect the model objects that must be exported.
In the data nesting, add queryforselectcount, queryforinsert, and queryforupdate. Supply SQL expressions appropriate to the target database and the values in the current export row.
Configure the ViewModel as a server-side job and schedule it through MDrivenServer. See Training:MDrivenServer periodic server-side actions for how periodic jobs find objects and execute ViewModel actions.
Upload the model and update the server-side jobs on MDrivenServer before testing the job.

Example: export one Order

Assume an Order object must be copied to an external table. The root ViewModel holds the target connection string and has a data nesting. That nesting collects the Order to export and provides one set of select, insert, and update queries for that Order.

Even when the job processes one object at a time, the object must be collected by the data nesting. For a job whose context is the Order itself, use an expression such as:

self->asset

This creates a collection containing self. Without collecting the object, the data nesting has no row to export and SQLExport has nothing to process.

The select-count query must identify the corresponding target row. Its result controls the next operation: a result of 0 causes the insert query to run; any other result causes the update query to run.

Connection and timeout options

Option	Use when	Behavior
`connectionstring`	The external database is accessed with a SQL Server connection.	SQLExport uses the supplied SQL Server connection string.
`connectionstringodbc`	The external database is accessed through ODBC.	SQLExport uses an ODBC connection.
`CommandTimeout`	A query needs more than the default execution time.	Sets the command timeout in seconds. If omitted, the timeout is 30 seconds.

Common problems

The export does not write any rows

Check that the data nesting is collected. A nesting describes the export row shape, but SQLExport only processes rows that the nesting collection contains. When processing one current object, use self->asset to collect that object.

The wrong query runs

Check the result of queryforselectcount. SQLExport inserts only when that query returns 0; otherwise it updates. Ensure the query tests the same target key that identifies the exported row.

The action does not find the columns

Verify the column placement and naming:

Put the connection column on the root ViewModel.
Put the three query columns on the data nesting.
Ensure the nesting name starts with data. The match is case-insensitive.
Use the action column names exactly as shown.

The export times out

Add CommandTimeout on the root ViewModel and set it to the required number of seconds. If it is not present, SQLExport uses 30 seconds.

Related export and import patterns

SQLExport writes rows to an external SQL database. To read data from an external SQL source into the model, use Training:Import data from other SQL servers. To produce files instead of database rows, use HowTos:Exporting files from MDriven Server.

Contents