BatchExecuteStatementCommand

Suggest an Edit

Runs a batch SQL statement over an array of data.

You can run bulk update and insert operations for multiple records using a DML statement with different parameter sets. Bulk operations can provide a significant performance improvement over individual insert and update operations.

If a call isn't part of a transaction because it doesn't include the transactionID parameter, changes that result from the call are committed automatically.

There isn't a fixed upper limit on the number of parameter sets. However, the maximum size of the HTTP request submitted through the Data API is 4 MiB. If the request exceeds this limit, the Data API returns an error and doesn't process the request. This 4-MiB limit includes the size of the HTTP headers and the JSON notation in the request. Thus, the number of parameter sets that you can include depends on a combination of factors, such as the size of the SQL statement and the size of each parameter set.

The response size limit is 1 MiB. If the call returns more than 1 MiB of response data, the call is terminated.

Example Syntax

Use a bare-bones client and the command you need to make an API call.

import { RDSDataClient, BatchExecuteStatementCommand } from "@aws-sdk/client-rds-data"; // ES Modules import
// const { RDSDataClient, BatchExecuteStatementCommand } = require("@aws-sdk/client-rds-data"); // CommonJS import
const client = new RDSDataClient(config);
const input = { // BatchExecuteStatementRequest
  resourceArn: "STRING_VALUE", // required
  secretArn: "STRING_VALUE", // required
  sql: "STRING_VALUE", // required
  database: "STRING_VALUE",
  schema: "STRING_VALUE",
  parameterSets: [ // SqlParameterSets
    [ // SqlParametersList
      { // SqlParameter
        name: "STRING_VALUE",
        value: { // Field Union: only one key present
          isNull: true || false,
          booleanValue: true || false,
          longValue: Number("long"),
          doubleValue: Number("double"),
          stringValue: "STRING_VALUE",
          blobValue: new Uint8Array(), // e.g. Buffer.from("") or new TextEncoder().encode("")
          arrayValue: { // ArrayValue Union: only one key present
            booleanValues: [ // BooleanArray
              true || false,
            ],
            longValues: [ // LongArray
              Number("long"),
            ],
            doubleValues: [ // DoubleArray
              Number("double"),
            ],
            stringValues: [ // StringArray
              "STRING_VALUE",
            ],
            arrayValues: [ // ArrayOfArray
              {//  Union: only one key present
                booleanValues: [
                  true || false,
                ],
                longValues: [
                  Number("long"),
                ],
                doubleValues: [
                  Number("double"),
                ],
                stringValues: [
                  "STRING_VALUE",
                ],
                arrayValues: [
                  "<ArrayValue>",
                ],
              },
            ],
          },
        },
        typeHint: "STRING_VALUE",
      },
    ],
  ],
  transactionId: "STRING_VALUE",
};
const command = new BatchExecuteStatementCommand(input);
const response = await client.send(command);
// { // BatchExecuteStatementResponse
//   updateResults: [ // UpdateResults
//     { // UpdateResult
//       generatedFields: [ // FieldList
//         { // Field Union: only one key present
//           isNull: true || false,
//           booleanValue: true || false,
//           longValue: Number("long"),
//           doubleValue: Number("double"),
//           stringValue: "STRING_VALUE",
//           blobValue: new Uint8Array(),
//           arrayValue: { // ArrayValue Union: only one key present
//             booleanValues: [ // BooleanArray
//               true || false,
//             ],
//             longValues: [ // LongArray
//               Number("long"),
//             ],
//             doubleValues: [ // DoubleArray
//               Number("double"),
//             ],
//             stringValues: [ // StringArray
//               "STRING_VALUE",
//             ],
//             arrayValues: [ // ArrayOfArray
//               {//  Union: only one key present
//                 booleanValues: [
//                   true || false,
//                 ],
//                 longValues: [
//                   Number("long"),
//                 ],
//                 doubleValues: [
//                   Number("double"),
//                 ],
//                 stringValues: [
//                   "STRING_VALUE",
//                 ],
//                 arrayValues: [
//                   "<ArrayValue>",
//                 ],
//               },
//             ],
//           },
//         },
//       ],
//     },
//   ],
// };

BatchExecuteStatementCommand Input

See BatchExecuteStatementCommandInput for more details

Parameter
Type
Description
resourceArn
Required
string | undefined

The Amazon Resource Name (ARN) of the Aurora Serverless DB cluster.

secretArn
Required
string | undefined

The ARN of the secret that enables access to the DB cluster. Enter the database user name and password for the credentials in the secret.

For information about creating the secret, see Create a database secret .

sql
Required
string | undefined

The SQL statement to run. Don't include a semicolon (;) at the end of the SQL statement.

database
string | undefined

The name of the database.

parameterSets
SqlParameter[][] | undefined

The parameter set for the batch operation.

The SQL statement is executed as many times as the number of parameter sets provided. To execute a SQL statement with no parameters, use one of the following options:

  • Specify one or more empty parameter sets.

  • Use the ExecuteStatement operation instead of the BatchExecuteStatement operation.

Array parameters are not supported.

schema
string | undefined

The name of the database schema.

Currently, the schema parameter isn't supported.

transactionId
string | undefined

The identifier of a transaction that was started by using the BeginTransaction operation. Specify the transaction ID of the transaction that you want to include the SQL statement in.

If the SQL statement is not part of a transaction, don't set this parameter.

BatchExecuteStatementCommand Output

See BatchExecuteStatementCommandOutput for details

Parameter
Type
Description
$metadata
Required
ResponseMetadata
Metadata pertaining to this request.
updateResults
UpdateResult[] | undefined

The execution results of each batch entry.

Throws

Name
Fault
Details
AccessDeniedException
client

You don't have sufficient access to perform this action.

BadRequestException
client

There is an error in the call or in a SQL statement. (This error only appears in calls from Aurora Serverless v1 databases.)

DatabaseErrorException
client

There was an error in processing the SQL statement.

DatabaseNotFoundException
client

The DB cluster doesn't have a DB instance.

DatabaseResumingException
client

A request was cancelled because the Aurora Serverless v2 DB instance was paused. The Data API request automatically resumes the DB instance. Wait a few seconds and try again.

DatabaseUnavailableException
server

The writer instance in the DB cluster isn't available.

ForbiddenException
client

There are insufficient privileges to make the call.

HttpEndpointNotEnabledException
client

The HTTP endpoint for using RDS Data API isn't enabled for the DB cluster.

InternalServerErrorException
server

An internal error occurred.

InvalidResourceStateException
client

The resource is in an invalid state.

InvalidSecretException
client

The Secrets Manager secret used with the request isn't valid.

SecretsErrorException
client

There was a problem with the Secrets Manager secret used with the request, caused by one of the following conditions:

  • RDS Data API timed out retrieving the secret.

  • The secret provided wasn't found.

  • The secret couldn't be decrypted.

ServiceUnavailableError
server

The service specified by the resourceArn parameter isn't available.

StatementTimeoutException
client

The execution of the SQL statement timed out.

TransactionNotFoundException
client

The transaction ID wasn't found.

RDSDataServiceException
Base exception class for all service exceptions from RDSData service.