%% 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 version 5.7.22 with the MySQL Connector/C++ driver version 8.0.15. 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)

% 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)

%% 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)

%% Close Database Connection

close(conn)

% Copyright 2020 The MathWorks, Inc.