This simple automation pack allow to execute a single at a time generic query on a MySQL Server. It adds:
- Process "MySQL Generic Query", and a "Sample SQL" to illustrate how to use it
- MySQL Database target type, with a "MySQL Server" tab to specify connectivity parameters, and is the required target type for "MySQL Generic Query" process
Input parameters:
- MySQL database
- Query: only a single query at a time. If you put multiple queries (separated by a ";"), the process would fail (or have an untested behavior).
- SELECT with multiple output format: choose between XML/JSON/RAW
- SELECT with single output expected: If you expect a single output, like "SELECT COUNT(FIELD) FROM ...", set this to true. Unless, let the default value (false)
Output values:
- Result:
- SELECT (single output) will return the value
- SELECT will return an XML / JSON / RAW (like following examples)
- INSERT/UPDATE/DELETE will return the count of affected rows
- For other types of query it depends (direct output of powershell ExecuteNonQuery). For example, DROP TABLE will return 0 even if the table is actually dropped.
- Warning: should be empty, unless multiple ";" are detected (may be normal, but may also be because of multiple queries detected) or if you execute other type of query, to warn you that the return value may be untrustworthy.
Prerequisites:
- Requieres the MySQL .NET provider for Windows, which can be download from https://dev.mysql.com/downloads/connector/net/ (tested with 6.8.3)
- Depending on the size of the output (string value), you may experience warnings / errors in the windows event log. This can be configured since CPO 3.0 using File --> Environment properties --> Execution limits (mainly String Variable Size).
Please test and give me feedbacks for futur releases!
Changelog:
v1.0.1.1: Choosing output format XML / JSON / ROW
v1.0.1.0: Adding target type "MySQL Database" and using this target type (not initial "generic service" extension), Checking MySQL .NET Connector presence, Adding the sample workflow in the MySQL category.
v1.0.0.2: Correcting a bug regarding SELECT statements beginning with '(' (ex: may be used when using UNION)
v1.0.0.1: Simplifying outputs to simplify usability
v1.0.0.0: Initial release
Sample SELECT XML output (you can easily parse it with Read Table from XML activity using MySQL-CPO-Query-Row as Row XML element name):
<MySQL-CPO-DataSet>
<MySQL-CPO-Query-Row>
<surname>Jean</surname>
<name>Dupont</name>
<age>35</age>
</MySQL-CPO-Query-Row>
<MySQL-CPO-Query-Row>
<surname>John</surname>
<name>Doe</name>
<age>45</age>
</MySQL-CPO-Query-Row>
</MySQL-CPO-DataSet>
Sample SELECT JSON output:
{
"MySQL-CPO-Query-Row": [
{
"surname": "Jean",
"name": "Dupont",
"age": "35"
},
{
"surname": "John",
"name": "Doe",
"age": "45"
}
]
}
Sample SELECT RAW:
surname name age
------- ---- ---
Jean Dupont 30
John Doe 45
