Roll Back Data in SQLite Database

This example shows how to connect to an SQLite database, insert a row into an existing database table, and then roll back the insertion using the MATLAB® interface to SQLite. The database contains the table productTable.

Create SQLite Connection

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

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

Append Data to Existing Database Table

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";

To view the existing database table productTable before appending data, import its contents into MATLAB and display the last few rows.

tablename = "productTable";
rows = sqlread(conn,tablename);
tail(rows,3)

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

         13            470816            1012            16           "Pancakes"    
         14            510099            1011            19           "Shawl"       
         15            899752            1011            20           "Snacks"      

Create a MATLAB table that contains the data for one product.

data = table(30,500000,1000,25,"Rubik's Cube", ...
    'VariableNames',["productnumber" "stocknumber" ...
    "suppliernumber" "unitcost" "productdescription"]);

Append the product data into the database table productTable.

sqlwrite(conn,tablename,data)

Import the contents of the database table into MATLAB again and display the last few rows. The results contain a new row for the inserted product.

rows = sqlread(conn,tablename);
tail(rows,4)

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

         13            470816            1012            16         "Pancakes"      
         14            510099            1011            19         "Shawl"         
         15            899752            1011            20         "Snacks"        
         30            500000            1000            25         "Rubik's Cube"  

Roll Back Data

Roll back the inserted row.

rollback(conn)

Import the contents of the database table into MATLAB again and display the last few rows. The results no longer contain the inserted row.

rows = sqlread(conn,tablename);
tail(rows,3)

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

         13            470816            1012            16           "Pancakes"    
         14            510099            1011            19           "Shawl"       
         15            899752            1011            20           "Snacks"      

Close Database Connection

close(conn)