getoptions
Retrieve import options for database data
Description
returns the import options for all variables in the varOpts
= getoptions(opts
)SQLImportOptions
object.
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
See Also
databaseImportOptions
| setoptions
| reset
| close
| database
| execute
| sqlwrite
| sqlread
External Websites
MATLAB Command
You clicked a link that corresponds to this MATLAB command:
Run the command by entering it in the MATLAB Command Window. Web browsers do not support MATLAB commands.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list:
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)