setdbprefs (Database Toolbox)

Set preferences for retrieval format, errors, and NULLs

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 nonnumeric 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. Nonnumeric 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 MATLAB Data Types 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'
Any numbers in the specified format, for example, NaN are represented as NULL 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'
Any strings in the specified format, for example, NaN, are represented as NULL 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 nonnumeric 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. Nonnumeric 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 MATLAB Data Types 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'`	Any numbers in the specified format, for example, `NaN` are represented as `NULL` 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'`	Any strings in the specified format, for example, `NaN`, are represented as `NULL` when exported to a database. `NaN` is the default value.

Remarks

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

Examples

Example 1--Display Current Values

Type setdbprefs and MATLAB returns

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

This specifies that

Data is imported into MATLAB cell arrays.
Any errors that occur during a connection or an 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: 'NaN'
```

This specifies that any NULL number in the database is read into MATLAB as NaN.

To change the value to 0, type

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

This specifies 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')

This specifies 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 Write Format for NULL Numbers

To specify the NullNumberWrite format as NaN, type

```
setdbprefs('NullNumberWrite', 'NaN')
```

This specifies that any numbers represented as NaN in MATLAB are exported to a database as NULL.

For example, the variable ex_data, contains a NaN

ex_data =
      '09-24-2003'         NaN

Executing an insert for ex_data will export the NaN as NULL as in

insert(conn, 'Avg_Freight_Cost', colnames, ex_data)

Change the NullNumberWrite value to Inf.

```
setdbprefs('NullNumberWrite', 'Inf')
```

Attempt to insert ex_data, which contains a NaN. MATLAB does not recognize the NaN in ex_data and generates an error.

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

Example 5--Change the ErrorHandling

Store:. To specify the store format, type

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

This specifies 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')
```

This specifies 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')
```

This specifies 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 because no attributes could be created.

Example 6--Change Multiple Settings

Type

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

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

See Also

clear, fetch

set sql2native