%% Import Data from Database Table Using Import Options
% Customize import options when importing data from a database table using the MySQL® native interface. Control the import options by creating an SQLImportOptions object. Then, customize import options for different database columns. Import data using the sqlread function.
% This example uses the patients.xls file, which contains the columns Gender, Location, SelfAssessedHealthStatus, and Smoker. The example also uses a MySQL database version 5.7.22 with the MySQL Connector/C++ driver version 8.0.15.
%
% Create a MySQL native interface database connection to a MySQL database.

datasource = "MySQLNative";
username = "root";
password = "matlab";

conn = mysql(datasource,username,password);

%
% Load patient information into the MATLAB® workspace.

patients = readtable("patients.xls");

% Create the patients database table using the patient information.

tablename = "patients";
sqlwrite(conn,tablename,patients)

%
% Create an SQLImportOptions object using the patients database table and the databaseImportOptions function.

opts = databaseImportOptions(conn,tablename)

%
% Display the current import options for the variables in the SelectedVariableNames property of the SQLImportOptions object.

vars = opts.SelectedVariableNames;
varOpts = getoptions(opts,vars)

% Change the data types for the Gender, Location, Smoker, and SelfAssessedHealthStatus variables using the setoptions function. Because the Gender, Location, and SelfAssessedHealthStatus variables indicate a finite set of repeating values, change their data type to categorical. Because the Smoker variable stores the values 0 and 1, change its data type to double. Then, display the updated import options.

opts = setoptions(opts,{'Gender','Location','SelfAssessedHealthStatus'}, ...
    'Type','categorical');
opts = setoptions(opts,'Smoker','Type','double');

varOpts = getoptions(opts,{'Gender','Location','Smoker', ...
    'SelfAssessedHealthStatus'})

%
% Import the patients database table using the sqlread function, and display the last eight rows of the table.

data = sqlread(conn,tablename,opts);
tail(data)

% Display a summary of the imported data. The sqlread function applies the import options to the variables in the imported data.

summary(data)

%
% Delete the patients database table using the execute function.

sqlquery = strcat("DROP TABLE ",tablename);
execute(conn,sqlquery)

% Close the database connection.

close(conn)

% Copyright 2020 The MathWorks, Inc.