setdbprefs (Database Toolbox)

Set preferences for data retrieval format, error notification, and NULL values

Graphical Interface

As an alternative to the setdbprefs function, you can select Preferences from the Visual Query Builder File menu and use the Preferences dialog box.

Syntax

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

Description

setdbprefs returns the current values for database action preferences.

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

setdbprefs('property', 'value') sets the preference to value for the specified property for the current session.

setdbprefs({'property1'; ... }, {'value1'; ... }) for the properties starting with property1, sets the preference values starting with value1, for the current session.

Allowable properties are listed in the following table.

Allowable Properties
Allowable Values
Description

'DataReturnFormat'

Format for data imported into MATLAB. Select a value based on the type of data you are importing, memory considerations, and your preferred method of working with retrieved data. Set the value before using fetch.

'cellarray' (default)
Imports data into MATLAB cell arrays. Use for non-numeric data types. Requires substantial system memory when retrieving large amounts of data. Has slower performance than numeric format. To address memory problems, use the RowLimit option with fetch. For more information about cell arrays, see Working with Cell Arrays in MATLAB.

'numeric'
Imports data into a MATLAB matrix of doubles. Non-numeric data types are considered to be NULL numbers and are shown as specified for the NullNumberRead property. Uses less system memory and offers better performance than the cellarray format. Use only when data to be retrieved is in numeric format, or when the nonnumeric data retrieved is not relevant.

'structure'
Imports data as a MATLAB structure. Can use for all data types. Makes it easy to work with returned columns. Requires substantial system memory when retrieving large amounts of data. Has slower performance than numeric format. To address memory problems, use the RowLimit option with fetch. For more information on using structures, see Structures and Cell Arrays" in the MATLAB documentation.

'ErrorHandling'

Behavior for handling errors when importing data. Set the value before running exec.

'store' (default)
Any errors from running database are stored in the Message field of the returned connection object. Any errors from running exec are stored in the Message field of the returned cursor object.

'report'
Any errors from running database or exec display immediately in the Command Window.

'empty'
Any errors from running database are stored in the Message field of the returned connection object. Any 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, [ ].

'NullNumberRead'
User-specified, for example, '0'
How NULL numbers in a database are represented when imported into MATLAB. NaN is the default value. Cannot specify a string value, such as 'NULL', if 'DataReturnFormat' is set to 'numeric'. Set the value before using fetch.

'NullNumberWrite'
User-specified, for example, 'NaN'
How NULL numbers in MATLAB are represented when exported to a database. NaN is the default value.

'NullStringRead'
User-specified, for example, 'null'
How NULL strings in a database are represented when imported into MATLAB. NaN is the default value. Set the value before using fetch.

'NullStringWrite'
User-specified, for example, 'NULL'
How NULL strings in MATLAB are represented when exported to a database. NaN is the default value.

Allowable Properties	Allowable Values	Description
`'DataReturnFormat'`		Format for data imported into MATLAB. Select a value based on the type of data you are importing, memory considerations, and your preferred method of working with retrieved data. Set the value before using `fetch`.
	`'cellarray'` (default)	Imports data into MATLAB cell arrays. Use for non-numeric data types. Requires substantial system memory when retrieving large amounts of data. Has slower performance than `numeric` format. To address memory problems, use the `RowLimit` option with `fetch`. For more information about cell arrays, see Working with Cell Arrays in MATLAB.
	`'numeric'`	Imports data into a MATLAB matrix of doubles. Non-numeric data types are considered to be `NULL` numbers and are shown as specified for the `NullNumberRead` property. Uses less system memory and offers better performance than the `cellarray` format. Use only when data to be retrieved is in numeric format, or when the nonnumeric data retrieved is not relevant.
	`'structure'`	Imports data as a MATLAB structure. Can use for all data types. Makes it easy to work with returned columns. Requires substantial system memory when retrieving large amounts of data. Has slower performance than `numeric` format. To address memory problems, use the `RowLimit` option with `fetch`. For more information on using structures, see Structures and Cell Arrays" in the MATLAB documentation.
`'ErrorHandling'`		Behavior for handling errors when importing data. Set the value before running `exec`.
	`'store'` (default)	Any errors from running `database` are stored in the `Message` field of the returned connection object. Any errors from running `exec` are stored in the `Message` field of the returned cursor object.
	`'report'`	Any errors from running `database` or `exec` display immediately in the Command Window.
	`'empty'`	Any errors from running `database` are stored in the `Message` field of the returned connection object. Any 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, [ ].
`'NullNumberRead'`	User-specified, for example, `'0'`	How `NULL` numbers in a database are represented when imported into MATLAB. `NaN` is the default value. Cannot specify a string value, such as `'NULL'`, if `'DataReturnFormat'` is set to `'numeric'`. Set the value before using `fetch`.
`'NullNumberWrite'`	User-specified, for example, `'NaN'`	How `NULL` numbers in MATLAB are represented when exported to a database. `NaN` is the default value.
`'NullStringRead'`	User-specified, for example, `'null'`	How `NULL` strings in a database are represented when imported into MATLAB. `NaN` is the default value. Set the value before using `fetch`.
`'NullStringWrite'`	User-specified, for example, `'NULL'`	How `NULL` strings in MATLAB are represented when exported to a database. `NaN` is the default value.

Examples

Example 1--Display Current Values

Type setdbprefs and MATLAB returns

    DataReturnFormat: 'cellarray'
       ErrorHandling: 'store'
      NullNumberRead: 'NaN'
     NullNumberWrite: 'NULL'
      NullStringRead: 'null'
     NullStringWrite: 'null'

which means

Data is imported into MATLAB cell arrays.
Any errors that occur during a connection or a SQL query are stored in the Message field of the connection or cursor data object.
Any NULL number in the database is read into MATLAB as NaN. Any NaN number in MATLAB is exported to the database as a NULL number. Any NULL string in the database is read into MATLAB as 'null'. Any 'null' string in MATLAB is exported to the database as a NULL string.

Example 2--Change a Value

Type setdbprefs ('NullNumberRead') and MATLAB returns

```
NullNumberRead: '0'
```

which means that any NULL number in the database is read into MATLAB as NaN.

To change the value to 0, type

```
setdbprefs ('NullNumberRead', '0')
```

which means that any NULL number in the database is read into MATLAB as 0.

Example 3--Change the DataReturnFormat

Cell array: To specify the cellarray format, type

setdbprefs ('DataReturnFormat','cellarray')

which means that data is imported into MATLAB cell arrays. The following illustrates a subsequent import.

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

Numeric: Specify the numeric format by typing

setdbprefs ('DataReturnFormat','numeric')

Performing the same set of import functions results in

curs.Data
ans =
   NaN    39
   NaN    17
   NaN    13

In the database, the values for ProductName are all character strings, as seen in the previous results when DataReturnFormat is set to cellarray. The ProductName values cannot be read when they are imported using the numeric format. Therefore, MATLAB treats them as NULL numbers and assigns them as NaN, which is the current value for the NullNumberRead property of setdbprefs in this example.

Structure: Specify the structure format by typing

setdbprefs ('DataReturnFormat','structure')

Performing the same set of import functions results in

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 ErrorHandling

Store: To specify the store format, type

```
setdbprefs ('ErrorHandling','store')
```

which means that any errors from running database or exec are stored in the Message field of the returned connection or cursor object.

The following illustrates an example of trying to fetch from a closed cursor.

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 indication appears in the Message field.

Report: To specify the report format, type

```
setdbprefs ('ErrorHandling','report')
```

which means that any errors from running database or exec display immediately in the Command Window.

The following illustrates the same example as above when trying to fetch from a closed cursor.

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 indication appears immediately in the Command Window.

Empty: To specify the empty format, type

```
setdbprefs ('ErrorHandling','empty')
```

which means that any errors from 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, [ ].

The following illustrates the same example as above when trying to fetch 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 indication appears in the cursor object Message field. In addition, the Attributes field returned empty handles since no attributes could be created.

Example 5--Change Multiple Settings

Type

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

which means any NULL string in the database is read into MATLAB as 'NaN', and data is retrieved into a matrix of doubles.

set sql2native