Roll Back Data in Database

This example shows how to connect to a database, update an existing row of data in the database, and roll back the update. Use the execute function to roll back the update after executing the update function.

Create a database connection conn to the Microsoft Access^® database. This code assumes that you are connecting to a data source named dbdemo with blank user name and password.

conn = database('dbdemo','','');

This database contains the table inventorytable that contains these columns:

• productnumber

• quantity

• price

• inventorydate

Set the AutoCommit property of the connection object to 'off'. Any updates you make after turning off this flag do not commit to the database automatically.

conn.AutoCommit = 'off';

Define a cell array containing the column names that you are updating in inventorytable.

colnames = {'price','inventorydate'};

Define a table that contains the data for insertion. Update the price to $15 and set the inventory timestamp to '2014-12-01 08:50:15.000'.

data = table(15,{'2014-12-01 08:50:15.000'}, ...
    'VariableNames',{'price','inventorydate'});

Update the columns price and inventorydate in the table inventorytable for the product number equal to 1.

tablename = 'inventorytable';
whereclause = 'WHERE productnumber = 1';

update(conn,tablename,colnames,data,whereclause)

Roll back data for the update.

sqlquery = 'ROLLBACK';
execute(conn,sqlquery)

You can commit data to the database by replacing the ROLLBACK SQL statement with COMMIT. You can also roll back or commit data after executing an INSERT SQL statement using the sqlwrite function.

Close the database connection.

close(conn)