reset

Reset import options when importing numeric data from a database table. Control the import options by creating an SQLImportOptions object. Then, customize the import options for a numeric database column. Import data using the sqlread function. Then, reset the import options back to the original state.

This example uses the patients.xls file, which contains the column Weight. The example also uses a Microsoft® SQL Server® Version 11.00.2100 database and the Microsoft SQL Server Driver 11.00.5058.

Create an ODBC 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 import options for the Weight variable. This variable has the double data type.

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

varOpts = 
  SQLVariableImportOptions with properties:

  Variable Properties :
               Name: 'Weight'
               Type: 'double'
          FillValue: NaN

Customize the import options for the Weight column in the patients database table. Because this column contains numeric data, change the data type to int64.

opts = setoptions(opts,varnames,'Type','int64');

Import the numeric data in the specified column and display a summary of the imported variable. The summary shows that the variable has the int64 data type.

opts.SelectedVariableNames = varnames;
data = sqlread(conn,tablename,opts);
summary(data)

Variables:

    Weight: 100×1 int64

        Values:

            Min         111   
            Median      143   
            Max         202   

Reset the import options back to their original state, and retrieve the import options for the Weight variable. This variable has the double data type again.

opts = reset(opts);
varOpts = getoptions(opts,varnames)

varOpts = 
  SQLVariableImportOptions with properties:

  Variable Properties :
               Name: 'Weight'
               Type: 'double'
          FillValue: NaN

Import the numeric data again using the default import options, and display a summary of the imported variable.

opts.SelectedVariableNames = varnames;
data = sqlread(conn,tablename,opts);
summary(data)

Variables:

    Weight: 100×1 double

        Values:

            Min          111  
            Median     142.5  
            Max          202  

Delete the patients database table using the execute function.

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

Close the database connection.

close(conn)