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

SQL Server

SQL Server

The SQL Server module is responsible for connecting to a SQL Server DBMS, performing queries and handling the results inside the robot.

Methods​

connection​

Method responsible for creating a connection with a SQL Server database.

js
const connection = await Database.SQLServer.connection["v1_0_0"]({
  connectionString: `Server=localhost;1433;Database=master;User Id=admin;password=123;TrustServerCertificate=True;`,
  connectTimeout: 10,
  queryTimeout: 60,
})

Required parameters​

  • connectionString: String - expects a string with the connection parameters. The standard must be followed as in the example shown earlier.

Optional parameters​

  • connectTimeout: Number - maximum time, in seconds, to wait for the server to accept the connection.
  • queryTimeout: Number - default time limit, in seconds, for every query run on this connection. 0 = no limit.

Return​

Returns the open connection, which must be stored in a variable (like connection in the example) and provided in the connection parameter of the other methods.

Version v2_0_0 of this method receives, instead of the connectionString, the connectionId of a connection configured in the workspace (Workspace → Connections), besides the same connectTimeout and queryTimeout:

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

close​

Method responsible for closing an open connection with a SQL Server database.

js
await Database.SQLServer.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 returns.

transactionBegin​

Method responsible for creating a transaction with the SQL Server DBMS.

js
const transaction = await Database.SQLServer.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​

Returns the open transaction, which must be stored in a variable (like transaction in the example) and provided in the transaction parameter of the query, transactionCommit and transactionRollback methods.

transactionCommit​

Method responsible for committing a transaction opened by the transactionBegin action.

js
await Database.SQLServer.transactionCommit["v1_0_0"]({
  transaction: transaction,
})

Required parameters​

  • transaction: Return of the action that begins a transaction - expects the transaction obtained through the transactionBegin action.

Optional parameters​

This action has no optional parameters.

Return​

This action has no returns.

transactionRollback​

Method responsible for rolling back a transaction opened by the transactionBegin action.

js
await Database.SQLServer.transactionRollback["v1_0_0"]({
  transaction: transaction,
})

Required parameters​

  • transaction: Return of the action that begins a transaction - expects the transaction obtained through the transactionBegin action.

Optional parameters​

This action has no optional parameters.

Return​

This action has no returns.

query​

Method responsible for performing a query directly in the database through the connection obtained by the connection action.

js
const query = await Database.SQLServer.query["v1_0_0"]({
  query: `SELECT * FROM tabela;`,
  connection: connection,
  transaction: transaction,
  timeout: 30
})

Required parameters​

  • connection: Return of the connection action - expects the connection obtained through the connection action.
  • query: String - expects a string with the query to be performed.

Optional parameters​

  • transaction: Return of the transactionBegin action - expects the transaction obtained through the transactionBegin action.
  • timeout: Number - time limit, in seconds, for this query. When omitted, the connection's queryTimeout applies. 0 = no limit. When the limit is reached, the promise rejects with an error whose message starts with Timeout. When neither timeout nor the connection's queryTimeout is provided, the driver's default limit applies (15 seconds, or the Request Timeout of the connection string).

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.query: String - returns the query that was used.
  • query.results: Array - returns every result obtained with the query.
  • query.resultsCount: Number - returns the number of rows obtained in the result.