Breadcrumbs

SQL Query Form Actions

The SQL Query form action runs a SQL statement when a submission is created or updated. Use it to save form data to a table, call a stored procedure, or create a record whose new ID is stored with the submission.

Step 1: Configure the database connection

Add the database under settings.databases in config\local.json, using mssql, mysql or postgresql, then restart the GlobalForms service:

JSON
{
  "settings": {
    "databases": {
      "mssql": {
        "host": "sql-server",
        "port": "1433",
        "database": "FormsData",
        "user": "svc_globalforms",
        "password": "<Password>"
      }
    }
  }
}

  • All five settings are required. A database type appears in the action only when all of them are filled in.

  • Use the database server's name or IP address. localhost and 127.0.0.1 are not accepted, even if the database is on the same server.

  • For SQL Server, TCP/IP connections must be enabled.

Step 2: Add the action

  1. On the form's Form Actions tab, add a SQL Query action and give it a clear title.

  2. Choose the SQL Server Type.

  3. Enter the Query, using form values as described below.

  4. Choose when it runs. Create runs for new submissions; add Update to run it again when a submission is edited, for example in GlobalCapture.

  5. Select Save Action, then submit a test entry and check the table.

Using form values in the query

Insert a form value with {{ data.fieldKey }}, where fieldKey is the component's API key. Follow these rules:

  • Include a space inside the braces: {{ data.firstName }}. {{data.firstName}} is not replaced.

  • Put quotes around text values: '{{ data.firstName }}'. Numbers don't need quotes.

  • Quotes inside the user's text are handled automatically.

  • Values that are not text or numbers, such as checkboxes, lists or grids, are inserted as empty. Convert them to text first, for example in a hidden field.

Example:

SQL
INSERT INTO Employees (FirstName, LastName, StartYear)
VALUES ('{{ data.firstName }}', '{{ data.lastName }}', {{ data.startYear }});

The SQL Query action and SQL lookups (Resquel) use different rules. In Resquel routes, placeholders must not be quoted.

Store the new record's ID

When an insert creates a new row, GlobalForms stores the new row's ID with the submission:

  • SQL Server: the identity value is captured automatically.

  • MySQL: the auto-increment ID is captured automatically.

  • PostgreSQL: add RETURNING id to the insert.

In later actions on the same submission, such as an Update, use {{ id }} to refer to that row:

SQL
UPDATE Employees SET LastName = '{{ data.lastName }}' WHERE ID = {{ id }};