Description
The MySQL Exec component allows you to execute an SQL statement against MySQL to update/delete data. Note, that each component can contain one statement.
It is compatible with MySQL 5.6 and above.
Output Payload
| Output Payload | Description |
|---|---|
cv.Payload | In addition to the input payload, it passes LastInsertId or RowsAffected by the executed SQL statement |
LastInsertId is returned when the executed SQL statement was an INSERT statement and the ID generated by the DB could be obtained.
RowsAffected returns the number of records affected by the executed SQL statement. For example, it returns the number of rows deleted when the statement was a DELETE statement.
Component Properties
| Property Name | Description |
|---|---|
| SQL | Specifies the SQL statement to execute |
| Bulk Mode | Choose whether to use bulk mode in executing the SQL statement |
| Bulk Size | Specifies the size when using bulk mode |
| Host | Specifies the host of MySQL |
| Port | Specifies the port of MySQL |
| Username | Specifies the username of MySQL |
| Password | Specifies the password of MySQL |
| Database | Specifies the database |
| Timezone | Specifies the timezone of the database |
| TLS | Specifies whether to encrypt communication, options are: true (connect with SSL/TLS), false (do not connect with SSL/TLS), skip-verify (connect with SSL/TLS but do not verify the server certificate), preferred (try to connect with SSL/TLS first, if unable to connect, try to connect without SSL/TLS) |
| Root Certificate | Specifies the root certificate to use. If using AWS's Aurora or RDS, you will need to use the server certificate issued by AWS. This will be the root certificate of that server certificate |
Placeholders
You must use placeholders in the SQL statement to be executed and use the value of cv.Playload in the SQL statement.
UPDATE table1 SET name='Updated name' WHERE id=?The value of cv.Payload is used for the placeholder specified by ?. When cv.Payload contains 1, it updates the record where id is 1.
You can specify more than one placeholder.
UPDATE table1 SET name=? WHERE id=?To specify values for more than one placeholder, specify the values in an array in cv.Payload.
["First Last", 20]
If you do not use the above method to construct your SQL query, you may encounter the error message failed to Exec: sql: expected 0 arguments, got 1
Text2SQL
Text2SQL is a function that can generate SQL statements from Japanese entered into the prompt using the functionality of OpenAI's ChatGPT.
For how to use it, please refer to the Text2SQL Usage Guide in the Setup Guide.