Skip to main content
Docs
Go to the Studio
Roberty Studio
RPADatabase

MySQL

MySQL

The MySQL module is responsible for creating a connection and handling data of a MySQL database.

Methods​

connection​

Action responsible for creating a connection with the MySQL database.

js
const connection = await Database.MySQL.connection.v1_0_0({
  host: '',
  port: number,
  user: '',
  password: '',
  localAddress?: '',
  socketPath?: '',
  timezone?: '',
  connectTimeout?: number,
  stringifyObjects?: boolean,
  insecureAuth?: boolean,
  typeCast?: boolean,
  supportBigNumbers?: boolean,
  bigNumberStrings?: boolean,
  dateStrings?: boolean,
  debug?: boolean,
  trace?: boolean,
  multipleStatements?: boolean,
  flags?: '',
  ssl?: '',
  database?: '',
})

Required parameters​

  • host: String - expects the host name of the database you are connecting to.
  • port: Number - expects the number of the port you want to connect to.
  • user: String - expects the MySQL user.
  • password: String - expects the MySQL password.

Optional parameters​

  • database: String - name of the database.
  • localAddress: String - origin IP address to be used for the TCP connection.
  • socketPath: String - path to a unix domain socket you want to connect to.
  • timezone: String - time zone configured on the MySQL server.
  • connectTimeout: Number - milliseconds for the connection time limit with the MySQL server.
  • stringifyObjects: Boolean - if true, objects are converted to string.
  • insecureAuth: Boolean - if true, it allows connection with instances that request the old (insecure) authentication.
  • typeCast: Boolean - if true, column values are converted into native JavaScript types.
  • supportBigNumbers: Boolean - if true, it handles BIGINT and DECIMAL columns.
  • bigNumberStrings: Boolean - if true, forces BIGINT and DECIMAL to be returned as String.
  • dateStrings: Boolean - if true, forces date types to be returned as strings.
  • debug: Boolean - if true, prints protocol details to stdout.
  • trace: Boolean - if true, it generates Error stack traces.
  • multipleStatements: Boolean - if true, it allows multiple MySQL statements per query.
  • flags: String - flags to be inserted into or removed from the queries.
  • ssl: String - object with SSL parameters or a string with the name of the SSL profile.
  • urlConnection: String - connection URL of the database (e.g. mysql://user:password@localhost:3306/database). When provided, it is used instead of the other access data.

Return​

Version v1_0_0 does not open the connection: it only validates and returns the configuration object provided, which must be passed in the connection parameter of version v1_0_0 of the query method. This object does not work with the close, transactionBegin, transactionCommit and transactionRollback methods.

To get an open connection, use version v2_0_0 (receives the connectionString) or v3_0_0 (receives the connectionId of a connection configured in the workspace, under Workspace → Connections). Both accept connectTimeout (maximum time, in seconds, to wait for the server to accept the connection) and queryTimeout (default time limit, in seconds, for every query run on this connection; 0 = no limit) and return the connection, which is what the connection parameter of version v2_0_0 of the query method and of the transaction and close methods expects:

js
// connection string
const connection = await Database.MySQL.connection["v2_0_0"]({
  connectionString: "mysql://usuario:senha@localhost:3306/banco",
  connectTimeout: 10,
  queryTimeout: 60,
})

// connection configured in the workspace
const connection = await Database.MySQL.connection["v3_0_0"]({
  connectionId: "id-of-the-connection-configured-in-the-workspace",
  connectTimeout: 10,
  queryTimeout: 60,
})

getDataValue​

Action responsible for getting a given piece of data from a position of the response of an SQL command executed in a MySQL database.

js
const getDataValue = await Database.MySQL.getDataValue.v1_0_0({
  column: '',
  row: 1,
  query: query
})

Required parameters​

  • column: String - name of the desired MySQL column.
  • row: Number - position (starting at 0) of the desired row in the return of the executed query.
  • query: Object - expects the return of version v1_0_0 of the query method (the data is read from the response property).

Optional parameters​

This action has no optional parameters.

Return​

The constant or variable created, such as the getDataValue of the example shown earlier, receives a value of the Object type. The object returned has the following property:

  • getDataValue.value: text - returns the data obtained from a MySQL query.

getReply​

Action responsible for getting the response of an SQL command executed in a MySQL database. It can be used after a SELECT.

js
const getReply = await Database.MySQL.getReply.v1_0_0({
  query: query
})

Required parameters​

  • query: Object - expects the return of version v1_0_0 of the query method (the results are read from the response property).

Optional parameters​

This action has no optional parameters.

Return​

The constant or variable created, such as the getReply of the example shown earlier, receives a value of the Object type. The object returned has the following property:

  • getReply.values: array - returns the results of a SELECT executed in a MySQL query.

query​

Action responsible for executing an SQL command in a MySQL database.

js
const query = await Database.MySQL.query.v1_0_0({
  connection: connection,
  query: '',
  timeout: 1
})

Required parameters​

  • connection: Return of the connection action - ID of the MySQL connection created earlier.
  • query: String - query to be executed. Example: SELECT * FROM teste.teste.

Optional parameters​

  • timeout: Number - time limit, in seconds, for this query. Default: 120. 0 = no limit. When the limit is reached, the promise rejects with an error whose message starts with Timeout.

Return​

The constant or variable created, such as the query of the example shown earlier, receives a value of the Object type. The object returned has the following properties:

  • query.size: number - amount of data returned by the query.
  • query.query: text - returns the query that was executed.
  • query.response: array - results of the query.
  • query.insertedId: number - ID of the record inserted.
  • query.affectedRows: number - number of rows affected.
  • query.changedRows: number - number of rows changed.

Version v2_0_0 of this method receives the connection opened by versions v2_0_0 or v3_0_0 of the connection method. In it, when timeout is omitted, the connection's queryTimeout applies (and, without it, 120 seconds), and the return has query.results (Array) and query.resultsCount (Number) instead of query.response and query.size, besides query.query, query.insertedId, query.affectedRows and query.changedRows.

close​

Action responsible for closing a connection with the MySQL database.

js
await Database.MySQL.close["v1_0_0"]({
  connection: connection,
})

Required parameters​

  • connection: Return of the connection action - expects the connection obtained through the connection action.

Optional parameters​

This action has no optional parameters.

Return​

This action has no return.

transactionBegin​

Action responsible for starting a transaction in a MySQL connection.

js
await Database.MySQL.transactionBegin["v1_0_0"]({
  connection: connection,
})

Required parameters​

  • connection: Return of the connection action - expects the connection obtained through the connection action.

Optional parameters​

This action has no optional parameters.

Return​

This action has no return.

transactionCommit​

Action responsible for committing a transaction started in a MySQL connection.

js
await Database.MySQL.transactionCommit["v1_0_0"]({
  connection: connection,
})

Required parameters​

  • connection: Return of the connection action - expects the connection obtained through the connection action.

Optional parameters​

This action has no optional parameters.

Return​

This action has no return.

transactionRollback​

Action responsible for rolling back a transaction started in a MySQL connection.

js
await Database.MySQL.transactionRollback["v1_0_0"]({
  connection: connection,
})

Required parameters​

  • connection: Return of the connection action - expects the connection obtained through the connection action.

Optional parameters​

This action has no optional parameters.

Return​

This action has no return.