Database Toolbox    

Specifying Preferences for NULLS, Data Format, and Error Handling

Using preferences, you can specify

To set preferences:

  1. Select Preferences from the Query menu.
  1. The Database Toolbox Preferences dialog box appears, showing the current settings.

  1. 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 0s. 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. You cannot use the Display menu items for the numeric and structure formats.
    Because our results 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
  1. For more information about these preferences, see the property descriptions on the reference page for setdbprefs, which is the equivalent function for setting preferences. Note that the settings for writing strings and numbers are not relevant for use in the Visual Query Builder, since you cannot export data using the Visual Query Builder. However, you can change the settings in the Preferences dialog box and those settings will be used if you export data using the Database Toolbox functions.

  1. Enter a workspace variable, A, and click Execute to run the query again.
  1. Information about the retrieved data appears in the Data section. 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, or if you want to use the Display menu items. 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.

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

  1. Note that 0s are displayed where previously there were NaNs to represent NULL values. Also note that the data is not in brackets since it is a numeric matrix rather than a cell array.

Saving Preferences

Preferences apply to the current MATLAB session. They are not saved with a query. It is a good practice to verify the preference settings before you run a query, especially if it retrieves a large amount of data.

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.


  Saving a Query Using Retrieved Data in MATLAB