Bulk Delete with SQL

Deletes data in bulk in a database table based on the data in a data source.

Command availability: IBM RPA SaaS and IBM RPA on premises

Script syntax

IBM RPA's proprietary script language has a syntax similar to other programming languages. The script syntax defines the command's syntax in the script file. You can work with this syntax in IBM RPA Studio's Script mode.

sqlBulkDelete --dataTable(DataTable) --connection(DbConnection) --tablename(String) [--batchsize(Numeric)] [--mappings(String)] [--timeout(TimeSpan)] (Boolean)=value

Dependencies

You must create a database connection with a valid connection string. Use the following commands according to the database management system (DBMS) you use:

Note:This command doesn't support ODBC connections.

Input parameter

The following table displays the list of input parameters available in this command. In the table, you can see the parameter name when working in IBM RPA Studio's Script mode and its Designer mode equivalent label.

Designer mode label	Script mode name	Required	Accepted variable types	Description
Data Table	`dataTable`	`Required`	`Data Table`	Data table as data source.
Connection	`connection`	`Required`	`Database Connection`	Database connection variable.
Table Name	`tablename`	`Required`	`Text`	Name of the table in the database. The table must exist in the database.
Batch Size	`batchsize`	`Optional`	`Number`	The batch size. The bulk insertion is sliced into batches based on the size you provide. All the rows are inserted no matter the value of the batch size.
Mappings	`mappings`	`Optional`	`Text`	Mapping of the data source table column to the database table column. You must to fill the following fields: Column Name: Data table column name that you want to map. Column Number: Data table column index that you want to map. Column index start at 1. Unnamed field: Database table column name. To identify the data table columns, you must to enter a value either in Column name or Column number field.
Timeout	`timeout`	`Optional`	`Time Span`, `Number` `Text`	The maximum time to wait to run the command. The default timeout is 5 seconds (`5000` or `00:00:05`). See the `timeout` parameter section for details.

The `timeout` parameter

You can define the timeout by entering a String representation in milliseconds, or a Time span representation. For example:

String representation: 50000 (fifty seconds)
Time span representation: 2.05:20:10.001 (two days, five hours, twenty minutes, ten seconds, and one millisecond)

For these representations, IBM RPA Studio rounds them up to the nearest in seconds value. For example:

1001 milliseconds are 00:00:01.001 and rounds up to 00:00:02
60020 milliseconds are 00:01:00.020 and rounds up to 00:01:01

Output parameter

Designer mode label	Script mode name	Accepted variable types	Description
Success	`value`	`Boolean`	Returns `True` if the command runs successfully, or `False` otherwise.

Example

The following code example demonstrates how to delete data in bulk in a database table by using a data table as the data source. The script needs two files in the My Documents folder; the sample.db database file and the sample.csv file. The sample.csv file stores sample data that you need to delete in the database table. It deletes data from the identifier, sample, and type columns on the database table.

defVar --name dbConnection --type DbConnection
defVar --name pathMyDocuments --type String
defVar --name databaseFile --type String
defVar --name dataTableFile --type String
defVar --name dataSource --type DataTable
// Gets the 'My Documents' folder path.
getSpecialFolder --folder "MyDocuments" pathMyDocuments=value
// Assign the 'sample.db' folder path to the 'databaseFile' variable.
setVar --name "${databaseFile}" --value "${pathMyDocuments}\\sample.db"
// Assign the 'sample.csv' folder path to the 'dataTableFile' variable.
setVar --name "${dataTableFile}" --value "${pathMyDocuments}\\sample.csv"
// Reads a CSV file and gets the sample data table to use as data source. The table is stored in the cache to be handled.
readCSV --filepath "${dataTableFile}" --delimiter "," --hasheaders  --encoding "Default" --missingfieldaction "ParseError" dataSource=value
// Connects to the database management system (DBMS). You must provide a valid connection string to connect with the database before you run this example.
sqliteConnect --connectionString "Data Source=${databaseFile};Version=3;UseUTF16Encoding=True;" dbConnection=connection
// Deletes every data in the database table that exists in the data source.
sqlBulkDelete --dataTable ${dataSource} --connection ${dbConnection} --tablename sample --mappings "number=1=identifier,number=2=sample,number=3=type" --timeout "00:00:52"

Limitations

The table must contain a primary key. Otherwise, the command returns an error.