Database Toolbox™

setdbprefs

Set preferences for retrieval format, errors, NULLs, and more

GUI Alternatives

Click Query > Preferences to set database preferences from Visual Query Builder.

Syntax

setdbprefs s = setdbprefs setdbprefs('property') setdbprefs('property', 'value') setdbprefs({'property1'; ...},{'value1'; ...} setdbprefs(s)

Description

setdbprefs returns current values for database preferences.
s = setdbprefs returns current values for database preferences to the structure s. You can save s to a MAT-file to use your specified preferences in future MATLAB^® software sessions.
setdbprefs('property') returns the current value for the specified property.
setdbprefs('property', 'value') sets the specified property to value in the current MATLAB software session. You can include this statement in a MATLAB startup file to set preferences automatically when a MATLAB software session starts.
setdbprefs({'property1'; ...},{'value1'; ...} sets properties starting with property1 to values starting with value1, in the current MATLAB software session.
setdbprefs(s) sets preferences specified in the structure s to values that you specify.

Allowable properties appear in the following tables:

DataReturnFormat and ErrorHandling Properties and Values for setdbprefs

Property	Allowable Values	Description
`'DataReturnFormat'`	`'cellarray'` (default), `'numeric'`, or `'structure'`	Format for data to import into the MATLAB workspace. Set the format based on the type of data being retrieved, memory considerations, and your preferred method of working with retrieved data.
	`'cellarray'` (default)	Imports nonnumeric data into MATLAB cell arrays.
	`'numeric'`	Imports data into MATLAB matrix of doubles. Nonnumeric data types are considered `NULL` and appear as specified in the `NullNumberRead` property. Use only when data to retrieve is in numeric format, or when nonnumeric data to retrieve is not relevant.
	`'structure'`	Imports data into a MATLAB structure. Use for all data types. Facilitates working with returned columns.
`'ErrorHandling'`	`'store'` (default), `'report'`, or `'empty'`	Specifies how to handle errors when importing data. Set this parameter before you run `exec`.
	`'store'` (default)	Errors from running `database` are stored in the `Message` field of the returned connection object. Errors from running `exec` are stored in the `Message` field of the returned cursor object.
	`'report'`	Errors from running `database` or `exec` display immediately in the MATLAB Command Window.
	`'empty'`	Errors from running `database` are stored in the `Message` field of the returned connection object. Errors from running `exec` are stored in the `Message` field of the returned cursor object. Objects that cannot be created are returned as empty handles (`[ ]`).

Null Data Handling Properties and Values for setdbprefs

Property	Allowable Values	Description
`'NullNumberRead'`	User-specified, for example, `'0'`	Specifies how `NULL` numbers appear after being imported from a database into the MATLAB workspace. `NaN` is the default value. String values such as `'NULL'` cannot be set if `'DataReturnFormat'` is set to `'numeric'`. Set this parameter before running `fetch`.
`'NullNumberWrite'`	User-specified, for example, `'NaN'`	Numbers in the specified format, for example, `NaN` appears as `NULL` after being exported from the MATLAB workspace to a database. `NaN` is the default value.
`'NullStringRead'`	User-specified, for example, `'null'`	Specifies how `NULL` strings appear after being imported from a database into the MATLAB workspace. `NaN` is the default value. Set this parameter before running `fetch`.
`'NullStringWrite'`	User-specified, for example, `'NULL'`	Strings in the specified format, for example, `NaN`, appear as `NULL` after being exported from the MATLAB workspace to a database.`NaN` is the default value.

Other Properties and Values for setdbprefs (Not Accessible via Query > Preferences)

Property Allowable Values Description

Property	Allowable Values	Description
`'JDBCDataSourceFile'`	User-specified, for example, `'D:/file.mat'`	Path to MAT-file containing JDBC data sources. For more information, see Accessing Existing JDBC Data Sources.
`'UseRegistryForSources'`	`'yes'` (default) or `'no'`	When set to `yes`, VQB searches the Microsoft^® Windows^® registry for ODBC data sources that are not uncovered in the system `ODBC.INI` file. The following message may appear: `Registry editing has been disabled by your administrator`. This message is harmless and can safely be ignored.
`'TempDirForRegistryOutput'`	User-specified, for example, `'D:/work'`	Directory where VQB writes `ODBC` registry settings when you run `getdatasources`. Use when you add data sources and do not have write access to the MATLAB current directory. The default is the Windows temporary directory, which is returned by the command `getenv('temp'))`. If you specify a directory to which you do not have write access or which does not exist, the following error appears: Cannot export <directory-name>\ODBC.INI: Error opening the file. There may be a disk or file system error.

'JDBCDataSourceFile'

User-specified, for example, 'D:/file.mat'

Path to MAT-file containing JDBC data sources. For more information, see Accessing Existing JDBC Data Sources.

'UseRegistryForSources'

'yes' (default) or 'no'

When set to yes, VQB searches the Microsoft^® Windows^® registry for ODBC data sources that are not uncovered in the system ODBC.INI file. The following message may appear: Registry editing has been disabled by your administrator. This message is harmless and can safely be ignored.

'TempDirForRegistryOutput'

User-specified, for example, 'D:/work'

Directory where VQB writes ODBC registry settings when you run getdatasources. Use when you add data sources and do not have write access to the MATLAB current directory.

The default is the Windows temporary directory, which is returned by the command getenv('temp')).

If you specify a directory to which you do not have write access or which does not exist, the following error appears:

Cannot export
<directory-name>\ODBC.INI:
Error opening the file. 
There may be a disk 
or file system error.

Remarks

When you run clear all, setdbprefs values are cleared and returned to their default values. It is a good practice to set or verify preferences values before each fetch.

Examples

Example 1 — Display Current Values

Run setdbprefs.

setdbprefs
            DataReturnFormat: 'cellarray'
               ErrorHandling: 'store'
              NullNumberRead: 'NaN'
             NullNumberWrite: 'NULL'
              NullStringRead: 'null'
             NullStringWrite: 'null'
          JDBCDataSourceFile: ''
       UseRegistryForSources: 'yes'
    TempDirForRegistryOutput: ''

These values show that:

Data is imported from databases into MATLAB cell arrays.
Errors that occur during a database connection or SQL query attempt are stored in the Message field of the connection or cursor data object.
Each NULL number in the database is read into the MATLAB workspace as NaN. Each NaN in the MATLAB workspace is exported to the database as NULL. Each NULL string in the database is read into the MATLAB workspace as 'null'. Each 'null' string in the MATLAB workspace is exported to the database as a NULL string.
A MAT-file that specifies the JDBC source file has not been created.
Visual Query Builder looks in the Windows system registry for data sources that do not appear in the ODBC.INI file.
No temporary directory for registry settings has been specified.

Example 2 — Change a Preference

Run setdbprefs ('NullNumberRead').

setdbprefs ('NullNumberRead')
NullNumberRead: 'NaN'

Each NULL number in the database is read into the MATLAB workspace as NaN.

Change the value of this preference to 0.

setdbprefs ('NullNumberRead', '0')

Each NULL number in the database is read into the MATLAB workspace as 0.

Example 3 — Change the DataReturnFormat Preference

Specify that database data be imported into MATLAB cell arrays.
```
setdbprefs ('DataReturnFormat','cellarray')
```

Import data into the MATLAB workspace.

conn = database('SampleDB', '', '');
curs=exec(conn, ...
	'select all ProductName,UnitsInStock fromProducts');
curs=fetch(curs,3);
curs.Data
ans = 
    'Chai'             [39]
    'Chang'            [17]
    'Aniseed Syrup'    [13]

Change the data return format from cellarray to numeric.
```
setdbprefs ('DataReturnFormat','numeric')
```
Perform the same import operation as you ran in the cell array example. Note the format of the returned data.
```
curs.Data
ans =
   NaN    39
   NaN    17
   NaN    13
```
In the database, the values for ProductName are character strings, as seen in the previous example when DataReturnFormat was set to cellarray. Therefore, the ProductName values cannot be read when they are imported into the MATLAB workspace using the numeric format. Therefore, the MATLAB software treats them as NULL numbers and assigns them the current value for the NullNumberRead property of setdbprefs, NaN.

Change the data return format to structure.

setdbprefs ('DataReturnFormat','structure')

Then perform the same import operation as you ran in the cell array example.

curs.Data
ans = 
     ProductName: {3x1 cell}
    UnitsInStock: [3x1 double]

View the contents of the structure to see the data.

curs.Data.ProductName
ans = 
    'Chai'
    'Chang'
    'Aniseed Syrup'

curs.Data.UnitsInStock
ans =
    39
    17
    13

Example 4 — Change the Write Format for NULL Numbers

Specify NaN for the NullNumberWrite format.
```
setdbprefs('NullNumberWrite', 'NaN')
```
Numbers represented as NaN in the MATLAB workspace are exported to databases as NULL.
For example, the variable ex_data, contains a NaN.
```
ex_data =
      '09-24-2003'         NaN
```
Insert ex_data into a database using fastinsert. TheNaN data is exported into the database as NULL.
```
fastinsert (conn, 'Avg_Freight_Cost', colnames, ex_data)
```
Change the value of NullNumberWrite to Inf.
```
setdbprefs('NullNumberWrite', 'Inf')
```

Attempt to insert ex_data. A MATLAB error appears because the NaN in ex_data cannot be read.

fastinsert(conn, 'Avg_Freight_Cost', colnames, ex_data
??? Error using ==> fastinsert
[Microsoft][ODBC Microsoft Access Driver] 
Too few parameters.
Expected 1.

Example 5 — Specify Error Handling Settings

Specify the store format for the ErrorHandling preference.
```
setdbprefs ('ErrorHandling','store')
```
Errors generated from running database or exec are stored in the Message field of the returned connection or cursor object.

Now try to fetch data from a closed cursor by running:.

conn=database('SampleDB', '', '');
curs=exec(conn, 'select all ProductName from Products');
close(curs)
curs=fetch(curs,3);
curs=

        Attributes: []
              Data: 0
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select all ProductName from Products'
           Message: 'Error: Invalid cursor'
              Type: 'Database Cursor Object'
         ResultSet: 0
            Cursor: 0
         Statement: [1x1 sun.jdbc.odbc.JdbcOdbcStatement]
             Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

The error generated by this operation appears in the Message field.

To specify the report format for the ErrorHandling preference, run:
```
setdbprefs ('ErrorHandling','report')
```
Errors generated by running database or exec display immediately in the Command Window.

Now try to fetch data from a closed cursor by running:

conn = database('SampleDB', '', '');
curs=exec(conn, 'select all ProductName from Products');
close(curs)
curs=fetch(curs,3);
??? Error using ==> cursor/fetch (errorhandling)
Invalid Cursor
Error in ==> 
	D:\matlab\toolbox\database\database\@cursor\fetch.m
On line 36  ==>    errorhandling(initialCursor.Message);

The error generated by this operation appears immediately in the Command Window.

Specify the empty format for the ErrorHandling preference.
```
setdbprefs ('ErrorHandling','empty')
```
Errors generated while running database or exec are stored in the Message field of the returned connection or cursor object. In addition, objects that cannot be created are returned as empty handles, [ ].

Try to fetch data from a closed cursor.

conn = database('SampleDB', '', '');
curs=exec(conn, 'select all ProductName from Products');
close(curs)
curs=fetch(curs,3);
curs =
 
        Attributes: []
              Data: []
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select all ProductName from Products'
           Message: 'Invalid Cursor'
              Type: 'Database Cursor Object'
         ResultSet: 0
            Cursor: 0
         Statement: [1x1 sun.jdbc.odbc.JdbcOdbcStatement]
					Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

The error appears in the cursor object Message field. Furthermore, the Attributes field contains empty handles because no attributes could be created.

Example 6 — Change Multiple Settings

Specify that NULL strings are read from the database into a MATLAB matrix of doubles as 'NaN':

setdbprefs({'NullStringRead';'DataReturnFormat'},...
{'NaN';'numeric'})

See Example 8 — Assign Values to a Structure for more information on another way to change multiple settings.

Example 7 — Specify JDBC Data Sources for Use by VQB

Instruct VQB to connect to the database using the data sources specified in the file myjdbcdatsources.mat.

setdbprefs('JDBCDataSourceFile',...
	'D:/Work/myjdbcdatasources.mat')

Example 8 — Assign Values to a Structure

Assign values for preferences to fields in the structure s.

s.DataReturnFormat = 'numeric'; 
s.NullNumberRead = '0';
s.TempDirForRegistryOutput = 'C:\Work'
s = 
            DataReturnFormat: 'numeric'
              NullNumberRead: '0'
    TempDirForRegistryOutput: 'C:\Work'

Set preferences using the values in s:
```
setdbprefs(s) 
```

Runsetdbprefs to check your preferences settings:

setdbprefs
DataReturnFormat: 'numeric'
               ErrorHandling: 'store'
              NullNumberRead: '0'
             NullNumberWrite: 'NaN'
              NullStringRead: 'null'
             NullStringWrite: 'null'
          JDBCDataSourceFile: ''
       UseRegistryForSources: 'yes'
    TempDirForRegistryOutput: 'C:\Work'

Example 9 — Return Values to a Structure

Assign values for all preferences to s by running:

s = setdbprefs
s = 

            DataReturnFormat: 'cellarray'
               ErrorHandling: 'store'
              NullNumberRead: 'NaN'
             NullNumberWrite: 'NaN'
              NullStringRead: 'null'
             NullStringWrite: 'null'
          JDBCDataSourceFile: ''
       UseRegistryForSources: 'yes'
    TempDirForRegistryOutput: ''

Now use the MATLAB tab completion feature when obtaining the value for a preference. For example, enter:

s.U

Press the Tab key, and then Enter. MATLAB completes the field and displays the value.

s.UseRegistryForSources

ans =

yes

Example 10 — Save Preferences

You can save your preferences to a MAT-file to use them in future MATLAB software sessions. For example, say that you need to reuse preferences that you set for the Seasonal Smoothing project. Assign the preferences to the variable SeasonalSmoothing and save them to a MAT-file SeasonalSmoothingPrefs in your current directory:

SeasonalSmoothing = setdbprefs;
save SeasonalSmoothingPrefs.mat SeasonalSmoothing

At a later time, load the data and restore the preferences:

load SeasonalSmoothingPrefs.mat
setdbprefs(SeasonalSmoothing);

Example 11 — Access Existing JDBC Data Sources

Use the following command to access an existing JDBC data source in future MATLAB software sessions:

setdbprefs('JDBCDataSourceFile','fullpathtomatfile')

For example, to use the data sources in the MAT-file D:/Work/myjdbcdatasources.mat, run this command in the MATLAB Command Window:

setdbprefs('JDBCDataSourceFile',...
'D:/Work/myjdbcdatasources.mat')

Tip Include this statement in a MATLAB startup file to access a given JDBC data source automatically when your MATLAB software session starts.