%% Create DatabaseDatastore Object Using Custom Import Options and Database Catalog and Schema
% Customize import options when importing a large data set from a database table. Control the import options by creating an SQLImportOptions object. Then, customize the import options for database columns that contain logical data. Create a DatabaseDatastore object using the specified database catalog and schema. Import the database data and preview it by using the preview function with the DatabaseDatastore object.
% This example uses the airlinesmall_subset.xls spreadsheet, which contains the column Cancelled. 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 flight information into the MATLAB® workspace.

flights = readtable('airlinesmall_subset.xlsx');

% Create the flights database table using the flight information and the toy_store database catalog and dbo database schema.

tablename = 'flights';
sqlwrite(conn,tablename,flights, ...
    'Catalog','toy_store','Schema','dbo')

%
% Create an SQLImportOptions object using the flights database table and the databaseImportOptions function. Specify the toy_store database catalog and dbo database schema.

opts = databaseImportOptions(conn,tablename, ...
    'Catalog','toy_store','Schema','dbo');

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

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

%
% Set the import options for the data type of the specified variable to logical. Also, set the import options to replace missing data in the specified variable with the fill value true.

opts = setoptions(opts,varnames,'Type','logical', ...
    'FillValue',true);

%
% Create the DatabaseDatastore object to import a large data set using import options, the toy_store database catalog, and the dbo database schema.

dbds = databaseDatastore(conn,tablename,opts, ...
    'Catalog','toy_store','Schema','dbo');

%
% Import the logical data in the selected variable and display a preview of the data. The imported data shows that the variable has the logical data type.

opts.SelectedVariableNames = varnames;
data = preview(dbds);
cancelled = data.Cancelled

%
% Delete the flights database table from the toy_store database catalog and the dbo database schema by using the execute function.

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

% Close the database connection.

close(conn)

% Copyright 2018 The MathWorks, Inc.