Documentation:SQLExport from MDriven Server

SQLExport from MDriven Server

This page was created by Wikiadmin on 2023-12-20. Last edited by Wikiadmin on 2026-07-29.

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

What SQLExport does

SQLExport is a server-side action recognized by MDrivenServer. It writes rows collected by a ViewModel to an external SQL database. Use it when you need to replicate selected model-driven data to another SQL-based database on a schedule.

The export decides whether to insert or update each row by running a count query:

If queryforselectcount returns 0, MDrivenServer runs queryforinsert.
If queryforselectcount returns a value other than 0, MDrivenServer runs queryforupdate.

For example, an export can check whether an external customer row already exists. A missing row is inserted; an existing row is updated.

When to use it

Use SQLExport when:

You need to write data from your MDriven model into a separate external SQL database.
You want the export to run from MDrivenServer, with no separate export program to deploy or maintain.
You need a periodic replication job, for example to synchronize selected customers or reference data every few minutes.

SQLExport writes to an external database. It does not expose the MDrivenServer database for general application access. For MDrivenServer interfaces for working with its own data, see Documentation:MDriven Server User/Web interface.

Required ViewModel structure

Configure the ViewModel used by the server-side action with a root connection column and a collected data nesting. The periodic action supervisor identifies the export from these column names.

Location	Required name	Purpose
Root	`connectionstring`	The connection string for the external SQL database.
Root	A nesting whose column name starts with `data`	Contains the row or rows to export. The `data` prefix is case-insensitive.
Data nesting	`queryforselectcount`	SQL query that determines whether the target row exists.
Data nesting	`queryforinsert`	SQL query MDrivenServer runs when the count query returns `0`.
Data nesting	`queryforupdate`	SQL query MDrivenServer runs when the count query returns a value other than `0`.

Connection options

By default, SQLExport uses the root connectionstring column. To connect through ODBC instead, use connectionstringodbc on the root; MDrivenServer then uses an ODBC connection rather than a SQL Server connection.

You can also add a root column named CommandTimeout. Its value is an integer number of seconds. The default command timeout is 30 seconds.

Root column	Use
`connectionstring`	Connect to the external SQL database.
`connectionstringodbc`	Connect through ODBC instead of the SQL Server connection path.
`CommandTimeout`	Set the SQL command timeout in whole seconds. If omitted, the timeout is 30 seconds.

Configure an export

Create the ViewModel that the MDrivenServer server-side job will execute.
Add the root connectionstring column. Evaluate it to the connection string for the external database. If the target requires ODBC, use connectionstringodbc instead.
Add CommandTimeout when the default 30-second timeout is not appropriate. Return an integer value in seconds.
Add a nesting with a name that starts with data, such as dataCustomer. Collect the model objects that should become export rows.
In that data nesting, add queryforselectcount, queryforinsert, and queryforupdate. Each attribute evaluates to the SQL needed for the current data row.
Configure the ViewModel as a periodic MDrivenServer server-side action. See Training:MDrivenServer periodic server-side actions for how periodic server-side jobs select work and execute their actions.
Run the job against a test target database. Verify both outcomes: a new source object produces an insert, and an already exported object produces an update.

Example export flow

Assume the data nesting collects customers to replicate:

The dataCustomer nesting contains one collected customer row.
queryforselectcount checks whether that customer's corresponding row exists in the external database.
For a new customer, the count query returns 0, so SQLExport runs queryforinsert.
On a later periodic run, the count query finds the row and returns a nonzero value, so SQLExport runs queryforupdate.

The SQL expressions and target schema are application-specific. Ensure that the count, insert, and update queries use the same external key so that an existing row is updated rather than inserted again.

Collection requirement

The data nesting must be collected, even when you export only one object. Do not use a single-object expression without collecting it.

For example, when the current object has an asset association and you want to export that one asset, collect it with:

self->asset

This ensures that the export receives a data row in its data... nesting.

Scheduling and operational guidance

SQLExport is commonly used as a periodic replication job. Keep the job focused on the objects that require export, and choose a schedule that matches the acceptable delay in the target system. Training:MDrivenServer periodic server-side actions describes the execution model for these jobs.

If another system writes directly to the database used by MDrivenServer, consider how MDriven clients will discover those external changes. Documentation:SQL Server change tracking describes the separate change-tracking pattern for externally modified SQL Server rows.

Related export and import options

SQLExport writes rows to an external SQL database. If you need to create files instead, use HowTos:Exporting files from MDriven Server, which supports saving ViewModel output and optional transformations. If you need to read from an external SQL source into the model, use Training:Import data from other SQL servers.

Contents