setdbprefs

(Not recommended) Set preferences for retrieval format, errors, NULLs, and more

The setdbprefs function is not recommended. For details about functionality to use instead, see Compatibility Considerations.

Syntax

setdbprefs

v = setdbprefs

setdbprefs(preference)

setdbprefs(preference,value)

setdbprefs(s)

Description

setdbprefs returns current values for database preferences.

v = setdbprefs returns current values to the structure v.

setdbprefs(preference) returns the current value for the specified preference.

setdbprefs(preference,value) sets the specified preference to value. After you set database preferences, they are retained across MATLAB^® sessions.

setdbprefs(s) sets preferences specified in the structure s to values that you specify.

Examples

Display Current Values

Display all database preferences and their current values.

setdbprefs

ans = 

  struct with fields:

    DataReturnFormat: 'table'
       ErrorHandling: 'store'
      NullNumberRead: 'NaN'
     NullNumberWrite: 'NaN'
      NullStringRead: 'null'
     NullStringWrite: 'null'

Display the current value for the specified database preference.

setdbprefs('ErrorHandling')

ans =

    'report'

Change Preference Setting

Set a database preference to a different value. Change the display of errors in MATLAB by modifying the database error handling preference.

Specify the store format for the ErrorHandling preference.

setdbprefs('ErrorHandling','store')

When you execute the database function, Database Toolbox™ stores any generated errors in the Message property of the returned connection object.

Establish the connection conn to a MySQL^® database with the user name username and an invalid password.

conn = database('MySQL','username','invalid');

Access the error message in the Message property of the connection object.

conn.Message

ans =

    'ODBC Driver Error: [MySQL][ODBC 5.3(a) Driver]Access denied for user 'username'@'servername' (using password: YES)'

Specify the report format for the ErrorHandling preference.

setdbprefs('ErrorHandling','report')

Connect to the database using the invalid password again. With the ErrorHandling preference set to report, the error generated by running the database function appears immediately in the Command Window.

conn = database('MySQL','username','invalid')

Error using database (line 156)
ODBC Driver Error: [MySQL][ODBC 5.3(a) Driver]Access denied for user
'username'@'servername' (using password: YES)

Assign Values to Structure

Assign values for specific preferences in a structure so you can change multiple database preferences simultaneously.

Assign values for preferences to fields in the structure s.

s.ErrorHandling = 'report'; 
s.NullStringRead = 'null';
s

s = 

  struct with fields:

       ErrorHandling: 'report'
      NullStringRead: 'null'

Set preferences using the values in s.

setdbprefs(s)

Run setdbprefs to check your database preference settings.

setdbprefs

ans = 

  struct with fields:

    DataReturnFormat: 'table'
       ErrorHandling: 'report'
      NullNumberRead: 'NaN'
     NullNumberWrite: 'NaN'
      NullStringRead: 'null'
     NullStringWrite: 'null'

Return Values to Structure

Assign values for all database preferences to s.

s = setdbprefs

s = 

  struct with fields:

    DataReturnFormat: 'table'
       ErrorHandling: 'report'
      NullNumberRead: 'NaN'
     NullNumberWrite: 'NaN'
      NullStringRead: 'null'
     NullStringWrite: 'null'
      

Save Preferences

Save your database preferences to the MAT-file to use them in future MATLAB sessions.

Assign the preferences to the variable ImportData and save them to a MAT-file ImportDataPrefs in your current folder.

ImportData = setdbprefs;
save ImportDataPrefs.mat ImportData

Load the data and restore the preferences.

load ImportDataPrefs.mat
setdbprefs(ImportData)

Input Arguments

preference — Database preference

character vector | cell array

Database preference, specified as a character vector or cell array. To set multiple database preferences, enter the preference values in a cell array of character vectors. Then, match the order with the corresponding values in the value argument.

You can specify database preferences for error handling and importing NULL strings from a database into MATLAB.

• 'ErrorHandling' — Specify how to handle errors when importing data. Set this parameter before you execute the database function. To specify displaying errors in the Command Window, enter setdbprefs('ErrorHandling','report'). Otherwise, you can access the error message in the Message property of the connection object.

• NULL data — Specify how to import NULL strings into the MATLAB workspace. To import NULL strings as the character vector 'null', enter setdbprefs('NullStringRead','null'). Set this parameter before running fetch.

Example: 'ErrorHandling'

Example: {'ErrorHandling';'NullStringRead'}

Data Types: char

value — Database preference value

character vector | cell array

Database preference value, specified as a character vector or cell array. To set multiple database preferences, enter the preference values in a cell array of character vectors. Then, match the order with the corresponding preferences in the preference argument.

Example: 'NaN'

Example: {'numeric';'NaN'}

Data Types: char

s — Database preferences

structure

Database preferences, specified as a structure that includes all the preferences you specify.

Data Types: struct

Output Arguments

v — Database preferences

structure

Database preferences, returned as a structure containing database preference settings and values.

Alternative Functionality

For a visual way to set database preferences, click Preferences in the Environment section of the MATLAB toolstrip, and then click Database Toolbox. Enter values for each database preference.

Version History

Introduced before R2006a

R2019a: setdbprefs function is not recommended

The setdbprefs function is not recommended. Use the following replacement functionality to specify the data return format, error handling, and missing data. Some differences between the workflows might require updates to your code.

• Data return format — For the 'DataReturnFormat' database preference, these values are not recommended:

  • 'numeric'

  • 'cellarray'

  • 'structure'

• Error handling — The 'ErrorHandling' database preference is not recommended.

• Missing data — The 'NullNumberWrite', 'NullStringWrite', and 'NullNumberRead' database preferences for handling NULL data values are not recommended.

There are no plans to remove the setdbprefs function at this time.

Update Code

To set the data return format in prior releases, you specified returning imported data as a numeric matrix by setting the 'DataReturnFormat' database preference to the value 'numeric'. For example:

setdbprefs('DataReturnFormat','numeric')
results = fetch(conn,sqlquery);

Now you can set the same value by using the 'DataReturnFormat' name-value pair argument of the fetch function.

results = fetch(conn,sqlquery,'DataReturnFormat','numeric');

Or, you can customize import options.

opts = databaseImportOptions(conn,tablename);
varnames = "quantity";
opts = setoptions(opts,varnames,'Type','int64');

To specify error handling in prior releases, you set the 'ErrorHandling' database preference to the value 'report' or 'store' by using the setdbprefs function. For example:

setdbprefs('ErrorHandling','store')

Now you specify error handling by using the 'ErrorHandling' name-value pair argument of the database function or the 'ErrorHandling' name-value pair argument of the executeSQLScript function.

conn = database(datasource,username,password,'ErrorHandling','store');

To specify the handling of missing data in prior releases, you set the 'NullNumberWrite' database preference to a specific value, for example. This table shows database preference settings that are not recommended and the functionality you can use instead.

Discouraged Functionality	Recommended Replacement
setdbprefs('NullNumberWrite','NaN')	data input argument of sqlwrite
setdbprefs('NullStringWrite','null')	data input argument of sqlwrite
setdbprefs('NullNumberRead','0')	SQLImportOptions object