Specifying Preferences for NULLS, Data Format, and Error Handling

Database Toolbox

Specifying Preferences for NULLS, Data Format, and Error Handling

Using preferences, you can specify

How the query builder represents NULL data
Format of data retrieved
Method for error notification

To set preferences

Select Preferences from the Query menu.

The Database Toolbox Preferences dialog box appears, showing the current settings.

Change the current preference settings to the new values and click OK. For this example, make the following changes.

Preference
Description
New Value

Read NULL numbers as
How NULL numbers in a database are represented when imported into MATLAB.
For the new value, 0, the NULL data in the example results will appear as 0's. Previously, they appeared as NaN values.
0

Data return format
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.
Cell arrays and structures support mixed data types, but require more memory and are processed more slowly than numeric matrices. Use the numeric format if the data you are retrieving consists only of numeric data or if the nonnumeric data is not relevant. With the numeric format, any nonnumeric data is converted to the representation specified in the Read NULL numbers as preference, for example, NaN. When Read NULL numbers as is numeric, the Data return format must also be numeric. For information about cell arrays, see Working with Cell Arrays in MATLAB. For information about working with strings, see Characters and Strings in the MATLAB Programming documentation.
Because results in the example are all numeric, we can change from cellarray to numeric to reduce memory required.
numeric

Error handling
Behavior for handling errors when importing data. In the Visual Query Builder, setting the value to store or empty means any errors are reported in a dialog box rather than in the Command Window.
Set the value to report, which means that any errors from running the query will display immediately in the Command Window.
report

Preference	Description	New Value
Read NULL numbers as	How `NULL` numbers in a database are represented when imported into MATLAB. For the new value, `0`, the `NULL` data in the example results will appear as `0`'s. Previously, they appeared as `NaN` values.	`0`
Data return format	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. Cell arrays and structures support mixed data types, but require more memory and are processed more slowly than numeric matrices. Use the `numeric` format if the data you are retrieving consists only of numeric data or if the nonnumeric data is not relevant. With the `numeric` format, any nonnumeric data is converted to the representation specified in the Read NULL numbers as preference, for example, `NaN`. When Read NULL numbers as is `numeric`, the Data return format must also be numeric. For information about cell arrays, see Working with Cell Arrays in MATLAB. For information about working with strings, see Characters and Strings in the MATLAB Programming documentation. Because results in the example are all numeric, we can change from `cellarray` to `numeric` to reduce memory required.	`numeric`
Error handling	Behavior for handling errors when importing data. In the Visual Query Builder, setting the value to `store` or `empty` means any errors are reported in a dialog box rather than in the Command Window. Set the value to `report`, which means that any errors from running the query will display immediately in the Command Window.	`report`

For more information about these preferences, see the property descriptions on the reference page for setdbprefs, which is the equivalent function for setting preferences.

Enter a workspace variable, A, and click Execute to run the query again.

Information about the retrieved data appears in the Data area. Note that the Memory size of A is 320 bytes, compared to 2720 bytes when we ran the query using the previous settings for preferences. This is because we changed the Data return format to numeric, where previously it was set to cellarray. The numeric format requires far less memory than the cellarray format. However, the cellarray (or structure) format is required if you want to retrieve data that is not all numeric, such as strings. If you use the numeric format to retrieve data that contains strings, the strings are returned as NULL values, represented by the preference you specified for Read NULL numbers as.

To see the results, type A in the Command Window.

>> A

A =

      125970        1400        1100         981
      212569        2400        1721        1414
      389123        1800        1200         890
      400314        3000        2400        1800
      400339        4300           0        2600
      400345        5000        3500        2800
      400455        1200         900         800
      400876        3000        2400        1500
      400999        3000        1500        1000
      888652           0         900         821

Results are not in brackets because data is a numeric matrix rather than a cell array. NULL values are now represented by 0's instead of NaNs.

Saving Preferences

Preferences apply to the current MATLAB session. They are not saved with a query. The default preferences apply when you start a new session, or after clearing all variables (for example, clear all). It is a good practice to verify the preference settings before you run a query.

Another way to set preferences is by using the setdbprefs function. To use the same preferences whenever you run MATLAB, include the setdbprefs function in your startup file.

Clearing Variables in the Data Area Viewing Query Results