The ODBC Database provider connects to a database using ODBC.
The following Databases are typically what you might connect with Data Sync via ODBC.
Running 64-Bit Data Sync requires 64 Bit ODBC Drivers and configuration via 64 Bit ODBC Manager.
SELECT
, INSERT
, UPDATE
and DELETE
SQL Statements are automatically calculated from the Data Schema. Only those columns in the Schema Map are affected the Key(s) columns define the WHERE
condition for UPDATE
and DELETE
.
In Incremental Mode the IN
clause is used to return matching records based on the Key selection in groups of 500 across multiple CPU threads. It is advised that the Key column is an Indexed SQL Column.
The SQL Command timeout in seconds.
The SQL Connection String that connects to your database.
Typical ODBC connection string when using connections created in ODBC Connection manager. dsn=MYSQL01;database=test;UID=user;PWD=password
The TABLE
or VIEW
that the Data source is connected to.
A user defined SQL Query to use instead of SourceTable setting this property overrides SourceTable and makes the Data source read-only.
Appends an ORDER BY
clause to the Query.
Appends an WHERE
clause to the Query, only when using SourceTable if you have a user defined Query in Command this value is ignored.
The start character to use when quoting identifiers.
The end character to use when quoting identifiers.
The character to use in parametrised queries, such as INSERT
, UPDATE
and DELETE
statements.
The SQL Server provider also exposes helper functions that can be called from Project Automation to update the Data source ExecuteScalar
, ExecuteNonQuery
and UpdateSourceRow
.
Method ExecuteScalar creates a SQL Command from the supplied SQL and Parameters values and calls ExecuteScalar against the database.
int ExecuteScalar(string sql, params object[] parameters)
Method ExecuteNonQuery creates a SQL Command from the supplied SQL and Parameters values and calls ExecuteNonQuery against the database.
int ExecuteNonQuery(string sql, params object[] parameters)
Method UpdateSourceRow creates a SQL Command to update a single column in the Data source using the key from the DataCompareItem
bool UpdateSourceRow(string column, object value, DataCompareItemInvariant item)
Used to update the source row from project automation, for example setting a Sync flag once a record has been synchronised.
For example calling this method in the AfterUpdateItem
item event to mark a record in the source as synchronised.
public override void AfterUpdateItem(object sender, DataCompareItemInvariant item, object identity)
{
DataSourceA.UpdateSourceRow("Sync", true, item);
}