getoptions

Retrieve import options for database data

Syntax

varOpts = getoptions(opts)

varOpts = getoptions(opts,varnames)

varOpts = getoptions(opts,index)

Description

varOpts = getoptions(opts) returns the import options for all variables in the SQLImportOptions object.

varOpts = getoptions(opts,varnames) returns the import options for the specified variable names.

varOpts = getoptions(opts,index) returns the import options for the variables specified by a numeric index.

Examples

Retrieve Default Import Options for Database Table

Control the import options by creating an SQLImportOptions object. Then, retrieve the default import options from a database table.

This example uses the patients.xls spreadsheet, which contains patient information. 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 and display the default import options for the patients database table.

varOpts = getoptions(opts)

varOpts = 
    1x10 SQLVariableImportOptions array with properties:

   Variable Options:
                    (1) |      (2) |      (3) |        (4) |      (5) |      (6) |      (7) |        (8) |         (9) |                       (10)  
       Name: 'LastName' | 'Gender' |    'Age' | 'Location' | 'Height' | 'Weight' | 'Smoker' | 'Systolic' | 'Diastolic' | 'SelfAssessedHealthStatus'
       Type:     'char' |   'char' | 'double' |     'char' | 'double' | 'double' | 'double' |   'double' |    'double' |                     'char'
  FillValue:         '' |       '' |    [NaN] |         '' |    [NaN] |    [NaN] |    [NaN] |      [NaN] |       [NaN] |                         ''

	To access sub-properties of each variable, use getoptions

To modify the variable import options, see the setoptions function.

Delete the patients database table using the execute function.

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

Close the database connection.

close(conn)

Retrieve Default Import Options for Database Columns Using Variable Names

Control the import options by creating an SQLImportOptions object. Then, retrieve the default import options for several columns from a database table. Specify the columns to retrieve by using the database column names.

This example uses the patients.xls spreadsheet, which contains the columns LastName, Age, and Location. 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);

Specify the names of the database columns in the patients database table.

varnames = {'LastName','Age','Location'};

Retrieve and display the default import options for the specified database columns.

varOpts = getoptions(opts,varnames)

varOpts = 
    1x3 SQLVariableImportOptions array with properties:

   Variable Options:
                    (1) |      (2) |        (3)  
       Name: 'LastName' |    'Age' | 'Location'
       Type:     'char' | 'double' |     'char'
  FillValue:         '' |    [NaN] |         ''

	To access sub-properties of each variable, use getoptions

To modify the variable import options, see the setoptions function.

Delete the patients database table using the execute function.

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

Close the database connection.

close(conn)

Retrieve Default Import Options for Database Columns Using Numeric Index

Control the import options by creating an SQLImportOptions object. Then, retrieve the default import options for several columns from a database table. Specify the columns to retrieve by using a numeric index.

This example uses the patients.xls spreadsheet, which contains the columns LastName, Gender, and Age. 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);

Specify the first three database columns by using a numeric index.

index = [1,2,3];

Retrieve and display the default import options for the specified database columns.

varOpts = getoptions(opts,index)

varOpts = 
    1x3 SQLVariableImportOptions array with properties:

   Variable Options:
                    (1) |      (2) |      (3)  
       Name: 'LastName' | 'Gender' |    'Age'
       Type:     'char' |   'char' | 'double'
  FillValue:         '' |       '' |    [NaN]

	To access sub-properties of each variable, use getoptions

To modify the variable import options, see the setoptions function.

Delete the patients database table using the execute function.

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

Close the database connection.

close(conn)

Input Arguments

opts — Database import options

SQLImportOptions object

Database import options, specified as an SQLImportOptions object.

varnames — Variable names

character vector | cell array of character vectors | string scalar | string array | numeric vector

Variable names, specified as a character vector, cell array of character vectors, string scalar, string array, or numeric vector. The varnames input argument indicates the variables in the VariableNames property of the SQLImportOptions object to use for importing data.

Example: 'productname'

Data Types: double | char | string | cell

index — Index

numeric vector

Index, specified as a numeric vector that identifies the variables in the VariableNames property of the SQLImportOptions object to use for importing data.

Example: [1,2,3]

Data Types: double

Output Arguments

varOpts — Type-dependent options for selected variables

array of variable import options objects

Type-dependent options for selected variables, returned as an array of variable import options objects. The array contains an object corresponding to each variable in the opts input argument or in the selected variables specified by the varnames or index input argument. The data type of each object in the array depends on the data type of the corresponding variable.

For categorical and datetime data types, each variable import options object contains additional properties that correspond to the data type.

To modify the properties of the individual objects, use the setoptions function.

Version History

Introduced in R2018b