MySQL Database connector
[ | version 3.2]
Connector
The connector is permitted for use of the platform background agent.
Connector processing type: Both (Row by row & Bulk), Default type: Bulk!
Debug script enabled.
The MySQL Database connector allows you to execute SQL commands and stored procedures remotely in MySQL database.
Accessing input data
If the input schema is selected, the input data will be predefined in the temporary table InputData (see Predefined Variables). You can use entire table in your statements or target concrete value by using placeholder ${input}:
| Placeholder | Type | Description |
|---|---|---|
${inputData} |
temporary table | Temporary table filled with input data. This table will be deleted after the connector run is over. |
${input[<row number>].<column name>} |
value | Takes the value from <row number>th row (zero-based index) and column named <column name>. |
Connector is able to process complex data columns on input schema. Such data are represented as longtext column and filled with serialized JSON.
Using placeholders
Assume the connector input data in the following format, followed by placeholders, which can be used for direct reference to the input data table:
| Row number | ID | Login |
|---|---|---|
0 |
1 ${input[0].ID} |
Alice ${input[0].Login} |
1 |
2 ${input[1].ID} |
Bob ${input[1].Login} |
Returning all input data
select * from InputData;
| ID | Login |
|---|---|
| 1 | Alice |
| 2 | Bob |
Referring to a specific value from the input data
select
${input[0].ID} as ID,
Login as Login
from InputData
Returns the list as in the previous example, except that the value from the first row and column of the ID input table is always used as ID (${input[0].ID} = 1):
| ID | Login |
|---|---|
| 1 | Alice |
| 1 | Bob |
Predefined variables
| Variable | Data type | Description |
|---|---|---|
InputData |
temporary table | Temporary table filled with input data. This table will be deleted when the connector is finished running. |
@TaskRunID |
text | ID of the currently executing TaskRun. No need to cast as signed when comparing. |
@EndpointTokenID |
int | ID of the currently used EndpointToken. |
@EndpointTokenName |
longtext | Name of the currently used EndpointToken. |
@DataCheckpoint |
longtext | Datacheckpoint value. |
Returning output data
If the output scheme is selected, the result of the last select statement, which is not assigned to any variable, is used as the step output. The structure of the query result must match the structure of the output schema.
If there are some complex input schemas returned to output, the result will contain column data serialized as JSON string.
Example
-- NOT the last select in the statement, data will NOT be sent to the output.
select 0 as ID, Login from InputData;
-- IS the last valid select in the statement, data WILL be sent to the output.
select 1 as ID, Login from InputData;
-- Using variable as parameter
select ID as TaskRunID, @DataCheckpoint as DataCheckpoint from InputData where ID != @TaskRunID
select @TaskRunID as TaskRunID
Logging
Direct logging from SQL connector into task run log is not supported.
Configuration
Input & Output Schema
Input
Input schema is optional
Output
Output schema is optional
Release notes
3.2.2
- Connection and commands timeout is respecting task step timeout
3.2.1
- Fixed processing sensitive errors
3.2.0
- @EndpointTokenID, @EndpointTokenName and @DataCheckpoint variables available to use
- Independence of
Connection stringandSQL Statementconfigurations
3.1.4
- Updated package binaries because of changes in another included connector
3.1.0
- Implemantation of debug script generating.
3.0.10
- Updated package binaries because of changes in another included connector
3.0.6
- Updated package binaries because of changes in another included connector.
3.0.5
- Fix replacing input placeholders in connection string configuration.
3.0.4
- Fixed processing complex input schema.
3.0.3
- Fixed shared nuget package versions.
3.0.2
- Fixed right processing of nullable properties.