rollback

Revert changes made to database

collapse all in page

Syntax

rollback(conn)

Description

rollback(conn) reverts all changes made to a database since the last COMMIT or ROLLBACK operation. This function works only when the connection is in transactional mode (AutoCommit is disabled). Because AutoCommit is on by default, set AutoCommit to off before using this function. Use rollback to revert the database to its previous state when an error occurs during a transaction.

conn can be any of the following connection objects:

  • MySQL®

  • PostgreSQL®

  • DuckDB™

  • SQLite

  • ODBC

  • JDBC

example

Examples

Reverse Changes Made to MySQL Database

Use a MySQL® native interface database connection to insert product data from MATLAB® into a new table in a MySQL database. Then, reverse the changes made to the database.

Create a MySQL native interface database connection to a MySQL database using the data source name, username, and password. The database contains the tables productTable and suppliers. If the database requires authentication, the recommended practice is to store credentials in your MATLAB® vault using setSecret instead of including them in your code. To connect to the database, specify the datasource and retrieve your credentials using the getSecret function.

Before R2024a: setSecret and getSecret are not available. Specify username and password using character vectors or strings.

datasource = "MySQLNative";
setSecret(“usernamemysql”);
setSecret(“passwordmysql”);
conn = mysql(datasource,getSecret("usernamemysql"),getSecret("passwordmysql"));

Allow manual committing of changes to the database by setting the AutoCommit property to off.

conn.AutoCommit = "off";

Create a MATLAB table that contains data for two products. The data is stored in the productTable and suppliers tables.

data = table([30;40],[500000;600000],[1000;2000],[25;30], ...
    ["Rubik's Cube";"Doll House"],'VariableNames',["productNumber" ...
    "stockNumber" "supplierNumber" "unitCost" "productDescription"]);

Insert the product data into a new table named toyTable.

tablename = "toyTable";
sqlwrite(conn,tablename,data)

Import the contents of the database table into MATLAB and display the rows. The results contain two rows for the inserted products.

rows = sqlread(conn,tablename)
rows=2×5 table
    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

         30             5e+05            1000            25         "Rubik's Cube"  
         40             6e+05            2000            30         "Doll House"

Reverse the changes made to the database.

rollback(conn)

Import and display the contents of the database table again. The results are empty.

rows = sqlread(conn,tablename)
rows =

  0×5 empty table

Close the database connection.

close(conn)

Reverse Changes Made to PostgreSQL Database

Use a PostgreSQL native interface database connection to insert product data from MATLAB® into a new table in a PostgreSQL database. Then, reverse the changes made to the database.

Create a PostgreSQL native interface database connection to a PostgreSQL database using the data source name, user name, and password. The database contains the tables productTable and suppliers.

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);

Allow manual committing of changes to the database by setting the AutoCommit property to off.

conn.AutoCommit = "off";

Create a MATLAB table that contains data for two products. The data is stored in the productTable and suppliers tables.

data = table([30;40],[500000;600000],[1000;2000],[25;30], ...
    ["Rubik's Cube";"Doll House"],'VariableNames',["productNumber" ...
    "stockNumber" "supplierNumber" "unitCost" "productDescription"]);

Insert the product data into a new table named toytable.

tablename = "toytable";
sqlwrite(conn,tablename,data)

Import the contents of the database table into MATLAB and display the rows. The results contain two rows for the inserted products.

rows = sqlread(conn,tablename)
rows=2×5 table
    30    500000    1000    25    "Rubik's Cube"
    40    600000    2000    30      "Doll House"

Reverse the changes made to the database.

rollback(conn)

Search for the table. The table no longer exists. 

data = sqlfind(conn,tablename)
data =

  0×5 empty table

Close the database connection.

close(conn)

Undo Changes Made to DuckDB Database

Create a transient, in-memory DuckDB™ database connection by using the duckdb function. Then, create a table by using the execute function.

conn = duckdb();
execute(conn,"CREATE TABLE demo (COL1 INTEGER)");

To manually rollback changes, you must set the AutoCommit property to "off".

conn.AutoCommit = "off";

Next, use the execute function to insert data into the table. Then, reverse the changes to the database by using the rollback function.

execute(conn,"INSERT INTO demo VALUES (1)");
rollback(conn);

To verify that the changes are undone, use the fetch function to access the table and import the data into MATLAB®. fetch returns an empty table indicating that the changes are undone.

data = fetch(conn,"SELECT * FROM demo")
data =

  0×1 empty table

    COL1
    ____

Reverse Changes Made to SQLite Database

Use the MATLAB® interface to SQLite to insert product data from MATLAB into a new table in an SQLite database. Then, reverse the changes made to the database.

Create the SQLite connection conn to the existing SQLite database file tutorial.db. The database file contains the table productTable. The SQLite connection is an sqlite object.

dbfile = "tutorial.db";
conn = sqlite(dbfile);

Allow manual committing of changes to the database by setting the AutoCommit property to off. 

conn.AutoCommit = "off";

Create a MATLAB table that contains data for two products. The data is stored in the productTable and suppliers tables.

data = table([30;40],[500000;600000],[1000;2000],[25;30], ...
    ["Rubik's Cube";"Doll House"],'VariableNames',["productNumber" ...
    "stockNumber" "supplierNumber" "unitCost" "productDescription"]);

Insert the product data into a new table named toyTable.

tablename = "toyTable";
sqlwrite(conn,tablename,data)

Import the contents of the database table into MATLAB and display the rows. The results contain two rows for the inserted products.

rows = sqlread(conn,tablename)
rows=2×5 table
    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

         30             5e+05            1000            25         "Rubik's Cube"  
         40             6e+05            2000            30         "Doll House"

Reverse the changes made to the database.

rollback(conn)

Import and display the contents of the database table again. The results are empty.

rows = sqlread(conn,tablename)
rows =

  0×5 empty table

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

Delete the new table to maintain the dataset.

sqlquery = "DROP TABLE toyTable";
execute(conn,sqlquery)

Close the database connection.

close(conn)

Reverse Changes Made Using ODBC Database Connection

Connect to a MySQL database by using an ODBC database connection. Then insert some data and commit the changes to the database.

Create a database connection to the ODBC data source, MySQL ODBC. Before R2024a: setSecret and getSecret are not available. Specify username and password using character vectors or strings.

DataSourceName = "MySQL ODBC";
setSecret("usernamemysql");
setSecret("passwordmysql");
conn = database(DataSourceName,getSecret("usernamemysql"),getSecret("passwordmysql"))
conn = 
  connection with properties:

                  DataSource: 'MySQL ODBC'
                    UserName: ''
                     Message: ''
                        Type: 'ODBC Connection Object'
  Database Properties:

                  AutoCommit: 'on'
                    ReadOnly: 'off'
                LoginTimeout: 0
      MaxDatabaseConnections: 0

  Catalog and Schema Information:

              DefaultCatalog: 'toystore_doc'
                    Catalogs: {'information_schema', 'detsdb', 'mysql' ... and 4 more}
                     Schemas: {}

  Database and Driver Information:

         DatabaseProductName: 'MySQL'
      DatabaseProductVersion: '5.7.22'
                  DriverName: 'myodbc5a.dll'
               DriverVersion: '05.03.0014'

conn has an empty Message property, which indicates a successful connection.

The property sections of the conn object are:

Database Properties -- Information about the database configuration

Catalog and Schema Information -- Names of catalogs and schemas in the database

Database and Driver Information -- Names and versions of the database and driver

Allow manual committing of changes to the database by setting the AutoCommit property to off.

conn.AutoCommit = "off";

Create a MATLAB table that contains data for two products. The data is stored in the productTable and suppliers tables.

data = table([30;40],[500000;600000],[1000;2000],[25;30], ...
    ["Rubik's Cube";"Doll House"],'VariableNames',["productNumber" ...
    "stockNumber" "supplierNumber" "unitCost" "productDescription"]);

Insert the product data into a new table named toyTable.

tablename = "toyTable";
sqlwrite(conn,tablename,data)

Import the contents of the database table into MATLAB and display the rows. The results contain two rows for the inserted products.

rows = sqlread(conn,tablename)
rows=2×5 table
    30    500000    1000    25    "Rubik's Cube"
    40    600000    2000    30      "Doll House"

Reverse the changes made to the database.

rollback(conn)

Import and display the contents of the database table again. The results are empty.

rows = sqlread(conn,tablename)
rows =

  0×5 empty table

Close the database connection.

close(conn)

Input Arguments

collapse all

Database connection, specified as any of the following:

  • MySQL connection object created by using the mysql function.

  • PostgreSQL connection object created by using the postgresql function.

  • DuckDB connection object created by using the duckdb function.

  • SQLite connection object created by using the sqlite function.

  • ODBC connection object created by using the database function.

  • JDBC connection object created by using the database function.

Version History

Introduced in R2020b

expand all

See Also

| | | |

Topics