Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 

MySQL Generic Query automation pack

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

Version history
Revision #:
1 of 1
Last update:
‎04-26-2014 03:17 AM
Updated by:
 
Labels (1)
Attachments
Everyone's tags (4)