After adding and authenticating an SQL Server Connection, you can add Form Connectors to push data to a database or pull data from a database using Data Source Connectors.
Our platform lets you quickly and easily connect your data sources to a SQL Server table. This enables a one-way synchronization - where any changes in the SQL table are downloaded into your data source regularly. Your data source will be automatically updated in this way until you remove the connector or an error occurs (such as losing authorization to access the database).
Before you add an SQL Connector, you must ensure you have a valid SQL Server Connection set up in your organization.
In This Article
Adding a Connection
To Set up a SQL Connector to your Data Source, follow these simple steps:
- Go to Connected Data -> Data Sources
- In the list of available data sources, hover your mouse over the listing of the data source you wish to connect.
A set of options will show; click on the settings link.
- On the Settings page, mid-way down you should see a Connector heading.
- Click
- A new window will appear with the selection of connectors to choose from.
Configuring
After adding the Data Source Connector, configure the connector. Enter an optional schema, the table name to retrieve rows from, or an optional clause to return specific rows.
Next, configure the following:
- Schema: Add an optional SQL Server database schema where the table is stored. If kept empty, the default schema will be selected.
- Table Name: The SQL Server database table from which rows will be retrieved.
- Where Clause: Add an optional where clause to the query that only returns desired records. e.g. WHERE LastName = 'Good'.
- Ignore Internal Columns: If enabled, this connector will ignore all columns where the column name starts with an underscore (e.g., _lastupdated). If your table has a column named _identity and internal columns are not ignored, this column will always be the first column of the data source. Other internal columns are added to the end.
- Refresh Frequency: The frequency that data is pulled by this connector.
When you save your changes, the connector will be fired for the first time and will subsequently run at the interval specified in the connector settings. Wait a minute or so and then view the data source's rows populated with your SQL Server data.
Connector Logs
If rows are not downloaded, then there may be an error with the connection, which can be confirmed by viewing the connector's logs. On the Data Source's Settings, under the connector area, hit the menu icon to bring up options to View Logs, Run Now, and Delete, depending on your needs.
Identify Updated and Deleted Rows in your SQL Table
For more advanced platform users who wish to have more granular control over their SQL data, we have a way for you to identify the updated and deleted rows in your SQL table. To do this, you will need to add the following two columns to the end of your SQL table unless the table is auto-maintained by a Form Connector that creates the columns for you.
_lastupdated
This column contains the date-time value of when this row was last updated.
Please ensure it is a not null column of the DateTime data type. All values for this column must be in the UTC timezone.
_deleted
A boolean column that represents whether or not this row has been deleted.
For the _lastupdated column, we expect users to update this column with the current UTC time whenever updating an existing row or adding a new row on the SQL Server side.
So when the next sync occurs on the data source and the value of this column exceeds the previous sync time, the platform will know to pull that row data across.
The _deleted column is how the platform determines whether the row was deleted on the SQL server side.
So, instead of simply deleting rows from the SQL table, you should mark these rows as deleted by setting this column to 1 (true) and also update the _lastupdated for that row to the current UTC time.
This is important because if you were to delete rows from the table, then the platform would never know about this, and the row would persist on the platform unless you have deleted and re-added the connector.
Once you are sure that these deletes have been synced, you can choose to delete these rows from the SQL Database entirely. However, we would suggest that you leave them in place.