Roll Back Data in Database Using MySQL Native Interface

This example shows how to connect to a database, insert a row into an existing database table, and roll back the insert using the MySQL® native interface. The example uses a MySQL database with the MariaDB® C Connector driver. The database contains the table productTable.

Create Database Connection

Create a MySQL native interface database connection to a MySQL database using the data source name, user name, and password.

datasource = "MySQLDataSource";
username = "root";
password = "matlab";
conn = mysql(datasource,username,password);

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          4.7082e+05          1012            17           "Pancakes"    
         14           5.101e+05          1011            19           "Shawl"       
         15          8.9975e+05          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          4.7082e+05          1012            17         "Pancakes"      
         14           5.101e+05          1011            19         "Shawl"         
         15          8.9975e+05          1011            20         "Snacks"        
         30               5e+05          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          4.7082e+05          1012            17           "Pancakes"    
         14           5.101e+05          1011            19           "Shawl"       
         15          8.9975e+05          1011            20           "Snacks"      

Close Database Connection

close(conn)