%% Insert Data into Database Table Using MySQL Native Interface
% This example shows how to import data from a database into MATLAB®, perform calculations on the data, and export the results to a database table.
% The example assumes that you are connecting to a MySQL® database that contains tables named salesVolume and yearlySales. Also, the example uses a MySQL database version 5.7.22 with the MySQL Connector/C++ driver version 8.0.15. The salesVolume table contains the column names for each month. The yearlySales table contains the column names Month and SalesTotal.
%% 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);

%% Calculate Sum of Sales Volume for One Month
% Import sales volume data for the month of March using the database connection. The salesVolume database table contains sales volume data.

tablename = "salesVolume"; 
data = sqlread(conn,tablename); 

% Display the first three rows of sales volume data. The fourth variable contains the data for the month of March.

head(data(:,4),3) 

% Calculate the sum of the March sales. Assign the result to the MATLAB workspace variable total and display the result.

total = sum(data.March) 

%% Insert Total Sales for One Month into Database
% Retrieve the name of the month from the sales volume data.

month = data.Properties.VariableNames(4);

% Define the names of the columns for the data to insert as a string array.

colnames = ["Month" "SalesTotal"]; 

% Create a MATLAB table that stores the data to export.

results = table(month,total,'VariableNames',colnames); 

% Determine the status of the AutoCommit database flag. This status determines whether or not the insert action can be undone.

conn.AutoCommit 

% The AutoCommit flag is set to on. The database commits the exported data automatically to the database, and this action cannot be undone.
% Insert the sum of sales for the month of March into the yearlySales table using the toystore_doc catalog.

tablename = "yearlySales"; 
sqlwrite(conn,tablename,results,'Catalog','toystore_doc') 

% Import the data from the yearlySales table. This data contains the calculated result.

data = sqlread(conn,tablename) 

%% Close Database Connection

close(conn)

% Copyright 2020 The MathWorks, Inc.