MySql v2

MySQL is a relational database platform used to store and query structured application and business data.

The Flowgear MySql Node queries databases and upserts rows through a direct connection or an optional SSH tunnel.

Revision History

0.0.0.2 - Initial release.
0.0.0.3 - Fixed templates.
0.0.0.7 - Fixed Node attributes.
0.0.0.9 - Optimized template discovery.

Connection

Use the Connection to configure the MySQL database credentials and optional SSH tunnel.

Property Type Description
Server String The name or IP address of the server hosting the MySQL instance.
Database String The name of the database to connect to.
Username String The MySQL username used for the Connection.
Password Masked The MySQL password used for the Connection.
Use SSH Boolean When true, the Connection uses an SSH tunnel. The default is false.
SSH Server String The SSH server used to establish the tunnel.
SSH Port Integer The SSH server port. The default is 22.
Database Port Integer The MySQL database port. The default is 3306.
SSH Username String The username used to authenticate with the SSH server.
SSH Password Masked The SSH password used when key authentication is not provided.
SSH Key File String The SSH private-key contents used for key authentication.
SSH Key File Pass Phrase Masked The pass phrase for the SSH private key.
Connection Timeout Integer The time in seconds to wait while establishing a Connection. The minimum and default value is 15.

Setup Notes

Enter Server, Database, and Username for a direct database Connection. Enter Password when the database account requires one.

To connect through SSH:

  • Set Use SSH to true.
  • Enter SSH Server, SSH Port, and SSH Username.
  • Enter either SSH Password or the private-key contents in SSH Key File.
  • Enter SSH Key File Pass Phrase if the private key is encrypted.
  • Set Database Port to the MySQL port that the SSH server can reach.

Test the Connection after saving it.

Methods

The MySql Node exposes Methods for running SQL queries, calling stored procedures, and upserting rows into a table.

Query

Executes a query against a MySQL database and returns the results.

Parameter Type Description
Connection Connection The MySql Connection.
Query String The SQL query to execute. Use named placeholders for external values.
Parameters Object Optional named SQL values. Keys can include or omit the leading @; for example, @CustomerId and CustomerId bind the same placeholder.
Return Type Description
Items Array The rows returned by the query. Each row is emitted as an object.

Run Stored Procedure

Executes a stored procedure using a CALL statement and returns its result rows. Select a stored-procedure template from the configured database to obtain the statement and input Property names.

Parameter Type Description
Connection Connection The MySql Connection.
QueryStatement String The generated CALL statement. Keep input values in CustomParameters instead of inserting them into SQL text.
CustomParameters Object Optional named procedure values, keyed with or without a leading @. The template creates a Property for each supported input parameter.
Return Type Description
Items Array Rows returned by the procedure. The Node executes the statement and attempts the first row read before returning the remaining rows as a stream. A procedure with no result rows returns an empty array.

MySQL procedure templates support IN parameters. Selecting a procedure with OUT or INOUT parameters reports an unsupported-parameter error.

To call a procedure from a Workflow:

  1. Add this Node and select Run Stored Procedure.
  2. Select the database Connection and the required procedure template.
  3. Review the generated QueryStatement and map each input under CustomParameters.
  4. Run with a small input appropriate to the procedure, then inspect Items and any changes the procedure made in the database.

For example, a procedure with an input named CustomerId has a template input named @InputCustomerId. Map the source customer ID to that Property and retain its placeholder in the generated statement. The procedure must already exist in the selected database; the Node does not create it.

Upsert

Upserts data into a MySQL database table.

Parameter Type Description
Connection Connection The MySql Connection.
TableName String The name of the table to upsert.
KeyFields String Comma-delimited key fields used to identify rows to update.
Items Array The items to upsert.
BatchSize Integer The number of rows to upsert per batch. The default is 100.
Return Type Description
RowsAffected Integer The number of rows affected by the upsert operation.

Usage Notes

  • Query emits one output item per row in the result set.
  • Query templates are generated from tables in the configured database and select up to ten rows by default.
  • Upsert templates use the current table schema to describe Items.
  • For Upsert, use item Property names that match the destination column names.
  • Supply KeyFields when existing rows must be updated. A duplicate-key error is returned when an insert conflicts and no suitable key fields identify the row to update.
  • SSH authentication requires either SSH Password or SSH Key File contents.

See also