SQL

A very common use case is, is to store the data of Forms into a database. This database could be either an “internal” one (only used by Portrait) or a database of a different system. However, in both cases, you need to set up a connection and check the permission first.

The SQL query can be a valid DML statement. For example:

INSERT statement for creating a new record.
UPDATE statement to edit an existing record.
DELETE statement to remove a record from the database.

DDL Statements are not possible.

onSubmit with SQL, supports the following settings:


onSubmit.type	Must be `SQL`
onSubmit.connection	name of the SQL connection - see SQL connections
onSubmit.query	The SQL query you want to execute. The form values are accessible via the form field names as handlebars. If required, you can use Field Processors to manipulate the data before executing the query.
onSubmit.fieldProcessor	See Post Processing (onSubmit) \| Field Processors
onSubmit.conditions	See Post Processing (onSubmit) \| Conditions
onSubmit.source	Used for Mode UPDATE details see Post Processing (onSubmit) \| Mode Update (new for SQL and Python) and to automatically reindex a given source

Example

- id: createNewCompany
  onSubmit:
    type: SQL
    connection: organigram
    source: demo-organigram
    fieldProcessor:
      - field: Name
        value: "{{Name}}"
      - field: PARENT_IDS
        value: "{{PARENT_IDS}}"
      - field: PARENT_LABELS
        value: "{{PARENT_LABELS}}"
    query: |
      INSERT INTO DemoOrganigram (Name, PARENT_IDS, PARENT_LABELS)
        VALUES (:Name, :PARENT_IDS, :PARENT_LABELS);
  dialog: config/forms/createNewCompany.json

In this given example, the form-data will be inserted into the table DemoOrganigram via the connection organigram.

The FieldProcessors can either be used to format fields or as a fallback in case of an optional field in the form. If not supplied the Query would fail as there would be no valid parameter :Name if not set previously. With the given FieldProcessor the fallback is an empty String.

Variables

The query can be customized with variables. Available parameters see https://portrait.atlassian.net/wiki/x/DQBwPQ