%% Retrieve Metadata Information About Imported Data
% Retrieve metadata information when importing data from a database table using the MySQL® native interface. Import data using the sqlread function and explore the metadata information by using dot notation.
% This example uses the outages.csv file, which contains outage data. 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 using the data source name, user name, and password.

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

%
% Load outage information into the MATLAB® workspace.

outages = readtable("outages.csv");

% Create the outages database table using the outage information. Use the 'ColumnType' name-value pair argument to specify the data types of the variables in the MATLAB® table.

tablename = "outages";
sqlwrite(conn,tablename,outages, ...
    'ColumnType',["varchar(120)","datetime","numeric(38,16)", ...
    "numeric(38,16)","datetime","varchar(150)"])

%
% Import the data into the MATLAB workspace and return metadata information about the imported data.

[data,metadata] = sqlread(conn,tablename);

%
% View the names of the variables in the imported data.

metadata.Properties.RowNames

%
% View the data type of each variable in the imported data.

metadata.VariableType

%
% View the missing data value for each variable in the imported data.

metadata.FillValue

%
% View the indices of the missing data for each variable in the imported data.

metadata.MissingRows

%
% Display the first eight rows of the imported data that contain missing restoration time values. data contains restoration time values in the fifth variable. Use the numeric indices to find the rows with missing data.

index = metadata.MissingRows{5,1};
nullrestoration = data(index,:);
head(nullrestoration)

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

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

% Close the database connection.

close(conn)

% Copyright 2020 The MathWorks, Inc.