The Integration - MySQL Plugin allows Scripting to connect to an external MySQL database, and link Workflow fields with columns held within the table. Any linked Fields are automatically populated from the paired column on Workflow pop, and any changes made to the Fields are written back to the database upon Workflow completion.
How To
The Integration - MySQL Plugin needs to be enabled in the
Plugins section of the Admin module. Before the Plugin can be activated, a DSN entry must be configured to allow connection to the MySQL database, and the DSN Name then entered in the Plugin. Once the details have been entered, click the Save button, and then Activate.
An ODBC connector can be used to setup this System DSN that Scripting uses to connect to the MySQL database. Shown below is an example of this DSN configuration which contains the server and connection details. More information can be found on the
MySQL website
Once the Integration - MySQL Plugin is active, Workflows can be configured to link them to the table through the
External Links section of the
Workflow Properties screen for the desired Workflow.
When the External Links section has been expanded, the Manage button can be clicked to set up an External Link. Select the Integration - MySQL Plugin from the first list, and then select the DSN connection and the MySQL table that should be connected to. Once these have been set and the Add button clicked, the link will appear in the right-hand portion of the window, and the Reference Column can be set. The Reference Column acts as the record key for working out which record row to link to the fields in a Workflow run.
In this example, if a new record with reference of 101 was generated, the MySQL Plugin would search mysqltest for the value 101 in the id column, and then make the remaining column values for that row available to linked Fields within the Workflow.
After customising the External Link as desired, close the window and then click Save at the bottom of the Workflow Properties page. Enter the
Workflow Designer view, and then Edit a Field on the page that you want to link with the MySQL database. In the
Field Properties window, the column that you wish to link with the Field can be chosen. In this example, the value in the record's
age column will populate the Field on Workflow pop, and any changes made during the Workflow run will be written back to the
age column.
Popping
The Workflow can be popped by a variety of methods, please refer to
Popping Mechanisms for further information.
When all Fields have been linked as desired, then when the Workflow is popped a check is made in the MySQL Table against the pop reference (if provided):
-
If a record is found with this reference, then any Fields that are linked will be automatically populated with that record's data, and any alteration to those Fields will be written back to the record at the end of the Workflow run.
-
If no record is found with the reference, then no data is populated in the Workflow, but the reference is used in generating a new record at the end of the Workflow run.
-
If no reference is provided, then no data is populated in the Workflow, and a new record is generated at the end of the Workflow run with an automatically-generated reference (of the form
CSAUTO-#-#-#).
Notes
The account details used when setting up the Integration - MySQL Plugin need to have read/write access to the relevant SQL tables you intend to use.
The reference column in the MySQL Table needs to be of type varchar(16383) by default, to allow Scripting to write new records to it if no reference is specified. If a reference is always specified, then the column can be of any compliant type.
The reference of a popped Workflow can be changed by altering the value of [var_csReference] inside the Workflow. This needs to comply with the reference column datatype, or an error may occur.
When a Workflow is linked to a database table using this Plugin, a Campaign is automatically created with the Campaign Name being the name of the MySQL table that it is linked to.
