Custom SQL

There are two ways in which you can control the flow of data to your SQL tables using one of our SQL form connectors ( SQL Server, MySQL and PostgreSQL), "Auto Maintain" and "Custom SQL"

In this article, we'll look at "Custom SQL" and how you can user it in your SQL form connectors for even greater control of your SQL data.

Custom SQL Overview

When managing your data through an SQL form connector, the first and most popular option is to enable the "Auto Maintain" option on the form connector. When this option is set on your form connector, our platform will (as the name suggests) auto-maintain the SQL table to which it writes data. This can be very convenient as if, for example, you remove various fields from your form and add some new ones, "Auto Maintain" will ensure that the SQL table connected to your form is also updated so that the records can be written there without you having to do anything.

For most use cases, this will suffice; however, some users need the option to do more advanced data processing by writing their own custom SQL scripts providing total control over what data goes to what tables in their SQL configuration. For these users, we recommend the "Custom SQL" option on their SQL form connector.

Custom SQL is available on all of our SQL form connectors. To enable it, untick the Auto Maintain Table option on your respective SQL form connector, and the Custom SQL Template text input box will appear.

Usage Scenarios

To give you an idea of typical use cases when you might find Custom SQL on our SQL form connectors to be the best option, consider these scenarios:

Whenever form data is submitted, you wish to write data to various tables for data analytics and reporting purposes. Custom SQL lets you write only the data you need to the tables you wish to write to, ensuring your production and reporting systems have the data they need to support your operations.
You need to maintain a Platform Sync (Hosted GET) table with _lastupdated and/or _deleted values. Custom SQL gives you the granular control necessary to maintain your data with the greatest precision.

Custom SQL allows you to write multiple queries, which will be executed upon form submission. This opens up several possible use cases that are far too many to list in this article.

Custom SQL Details

Our SQL Form Connectors can execute multiple queries simultaneously in a Custom SQL statement.

The total time for this SQL connector to execute is about 15 seconds.

All form connectors are subject to a 5-second timeout delay for establishing a connection. If our platform is not able to connect to your database within 5 seconds, then the connector will fail.

All SQL connectors have a 10-second maximum execution timeout. This means that if all of your SQL commands don't execute within 10 seconds after establishing a connection to your database, then the connector will fail.

To add Custom SQL code to your SQL form connector, you enter the custom SQL you want the form connector to execute in the "Custom SQL Template" text area.

The Custom SQL code supports the execution of more than one statement, however, all SQL statements must execute within a single SQL transaction.

Custom SQL Syntax

While the syntax used to populate the "Custom SQL Template" text area is regular SQL, you can use our platform's data templating syntax to inject Form field placeholders as well as define repeating sections in your Custom SQL syntax, as shown in the example below:

DELETE FROM [MyTableName] WHERE Id = CAST({{%ENTRYGUIDFULL}} as uniqueidentifier);
INSERT INTO [MyTableName] (
  [id],
  [itemno],
  [formversion],
  [myField1],
  [myfield2],
  [myfieldn]
)
VALUES {{!REPEATSTART}} (
  CAST({{%ENTRYGUIDFULL}} as uniqueidentifier),
  CAST({{%ITEMNO as int}}),
  CAST({{%FORMVERSION}} as int),
  CAST({{myField1}} as datetime),
  CAST({{myfield2}} as nvarchar),
  CAST({{myfield2}} as int)
), {{!REPEATEND}};

A special built-in data command is available to SQL connectors, namely {{%UTCNOW}}, which will contain the date and time (in UTC) of when the query is executed. This can be useful for updating the _lastupdated column if you use Platform Sync.

This built-in data command, along with ENTRYGUID, ENTRYGUIDFULL, and ITEMNO, is always guaranteed to have a value regardless of whether "Fill Repeat Rows" is unchecked (i.e., using NULL in Repeat Rows).

Sample Custom SQL Code

To give you an idea of a typical use case for Custom SQL, we've added some sample code that you can use as a guide for writing your own SQL scripts and controlling the flow of your platform-generated data to your SQL databases and tables.

Conditional SQL Statements

There may come a time when you'd like to use SQL's conditional logic to give you the flexibility to execute various SQL statements only when a certain condition has been met. To do this, you can take advantage of SQL's native IF ELSE logic to control the execution of your Custom SQL scripts, as shown in the example below:

IF EXISTS (SELECT * FROM tb_project WHERE projectID = '{{projectID}}')
BEGIN
   UPDATE tb_projects
SET project = '{{project}}',  description= '{{description}}', client = '{{client}}', projecttype = '{{projecttype}}', user = '{{user}}', foreman = '{{foreman}}'
WHERE projectID = '{{projectID}}'
END
ELSE
BEGIN
    INSERT INTO tb_projects  (projectID, project, description , client, projecttype, user, foreman)
    VALUES('{{projectID}}', '{{project}}', '{{description}}', '{{client}}', '{{projecttype}}', '{{creator}}', '{{foreman}}')
END

Repeatable Rows in Custom SQL

If you ever want to write data from a repeatable table/page in your form to an SQL table, the syntax below will help you do so.

BEGIN
INSERT INTO SQL_Test_form (Id, firstName, lastName)
{{!REPEATSTART}}
    VALUES({{h_id}}, {{firstName}}, {{lastName}}),
{{!REPEATEND}}
END