%% Preview Database Data After Customizing Options
% Customize import options when importing text data from a database table. Control the import options by creating an SQLImportOptions object. Customize the import options for a text database column. Preview the database data before importing data. Then, import the data using the sqlread function.
% This example uses the patients.xls spreadsheet, which contains the first column LastName. Also, the example uses a Microsoft® SQL Server® Version 11.00.2100 database and the Microsoft SQL Server Driver 11.00.5058.
%
% Create a database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

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

%
% Retrieve the default import options for the LastName variable.

varnames = 'LastName';
varOpts = getoptions(opts,varnames)

%
% Set the import options for the data type of the LastName variable to string. Specify the LastName variable by using a numeric index that finds the variable within the SelectedVariables property of the SQLImportOptions object. Also, set the import options to replace missing data in the LastName variable with the NoName fill value.

index = 1;
opts = setoptions(opts,index,'Type','string', ...
    'FillValue','NoName');

%
% Preview the first eight rows of database data using the import options. The data preview shows that the LastName variable has the string data type.

T = preview(opts)

%
% Import the text data in the selected variable and display the first eight rows. The imported data shows that the variable has the string data type.

opts.SelectedVariableNames = 'LastName';
data = sqlread(conn,tablename,opts);
head(data)

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

sqlquery = ['DROP TABLE ' tablename];
execute(conn,sqlquery)

% Close the database connection.

close(conn)

% Copyright 2018 The MathWorks, Inc.