%% Customize Options for Importing Data from Database into MATLAB Using MySQL Native Interface
% This example shows how to 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 the import options for multiple database columns. Import data using the sqlread function.
% The example uses the patients.xls spreadsheet, which contains patient information. Also, the example uses a MySQL database version 5.7.22 with the MySQL Connector/C++ driver version 8.0.15.
%
%% 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);

%
%% Load Example Data
% 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 SQLImportOptions Object
% Create an SQLImportOptions object using the patients database table and the databaseImportOptions function.

opts = databaseImportOptions(conn,tablename);

% Display the default data types of the variables by accessing the VariableNames and VariableTypes properties of the SQLImportOptions object using dot notation.

disp([opts.VariableNames' opts.VariableTypes'])

%
%% Customize Import Options
% Change the data types of multiple variables. Convert the data type for all text variables to string. Also, convert the data type for all numeric variables to single.

textvars = ["LastName" "Gender" "Location" "SelfAssessedHealthStatus"];
opts = setoptions(opts,textvars,'Type',"string");

numvars = ["Age" "Height" "Weight" "Systolic" "Smoker" "Diastolic"];
opts = setoptions(opts,numvars,'Type',"single");

%
% Display the updated data types of the variables.

disp([opts.VariableNames' opts.VariableTypes'])

%
% Set the import options to replace missing data in the specified variables with the fill value unknown.

varnames = ["LastName" "Location"];
opts = setoptions(opts,varnames,'FillValue',"unknown");

%
% Set the import options to omit rows with missing data in the LastName variable.

varname = "LastName";
opts = setoptions(opts,varname,'MissingRule',"omitrow");

%
%% Preview Data Before Importing
% Before importing the data, preview it by using the customized import options.

T = preview(opts)

%
%% Import Data Using Import Options
% Import the variables with the customized data types by using the sqlread function, and display the first eight rows of imported data.

T = sqlread(conn,tablename,opts);
head(T)

%
%% Delete Example Data and Close Database Connection
% 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.