Documentation

This is machine translation

Translated by Microsoft
Mouseover text to see original. Click the button below to return to the English verison of the page.

Note: This page has been translated by MathWorks. Please click here
To view all translated materals including this page, select Japan from the country navigator on the bottom of this page.

setdbprefs

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

Syntax

setdbprefs
v = setdbprefs
setdbprefs(preference)
setdbprefs(preference,value)
setdbprefs(s)

Description

example

setdbprefs returns current values for database preferences.

example

v = setdbprefs returns current values to the structure v.

example

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

Note

This syntax has been removed: setdbprefs('JDBCDataSourceFile').

example

setdbprefs(preference,value) sets the specified preference to value. After database preferences are set, they are retained across MATLAB® sessions.

Note

These syntaxes have been removed:

  • setdbprefs('DefaultRowPreFetch','10000')

  • setdbprefs('UseRegistryForSources','""')

  • setdbprefs('TempDirForRegistryOutput','""')

This syntax has been removed: setdbprefs('ErrorHandling','empty'). Use setdbprefs('ErrorHandling','report') instead.

These syntaxes will be removed in a future release: setdbprefs('FetchInBatches','yes') and setdbprefs('FetchBatchSize','1000').

This syntax will be removed in a future release: setdbprefs('DataReturnFormat','dataset'). Use setdbprefs('DataReturnFormat','table') instead.

example

setdbprefs(s) sets preferences specified in the structure s to values that you specify.

Examples

collapse all

View the current values of all database preferences

Display all database preference properties and their current values.

setdbprefs
ans = 

  struct with fields:

    DataReturnFormat: 'table'
       ErrorHandling: 'store'
      NullNumberRead: 'NaN'
     NullNumberWrite: 'NaN'
      NullStringRead: 'null'
     NullStringWrite: 'null'
      FetchInBatches: 'no'
      FetchBatchSize: '1000'

Set a database preference to another value.

Display the current value of the NullNumberRead database preference.

setdbprefs('NullNumberRead')
ans =

    '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.

Change the way data returns to the MATLAB workspace by altering the database DataReturnFormat preference.

Specify that database data be imported into MATLAB cell arrays.

setdbprefs('DataReturnFormat','cellarray')

Establish connection conn to a MySQL® database with user name username and password pwd. This database contains the table producttable with these columns: productnumber and productdescription.

conn = database('MySQL','username','pwd');

Import data into the MATLAB workspace.

sqlquery = 'SELECT productnumber,productdescription FROM producttable';
curs = exec(conn,sqlquery);
curs = fetch(curs,3);
curs.Data
ans = 

    [9]    'Victorian Doll'
    [8]    'Train Set'     
    [7]    'Engine Kit' 

Resulting data displays as a cell array.

Change the data return format from cellarray to numeric.

setdbprefs('DataReturnFormat','numeric')

Import data into the MATLAB workspace.

sqlquery = 'SELECT productnumber,productdescription FROM producttable';
curs = exec(conn,sqlquery);
curs = fetch(curs,3);
curs.Data
ans =

     9   NaN
     8   NaN
     7   NaN

In the database, the values for productDescription are character strings, as seen in the previous example when DataReturnFormat was set to cellarray. The productDescription values cannot be read when they are imported into the MATLAB workspace using the numeric format. Therefore, MATLAB treats these values as NULL numbers and assigns them the current value for the NullNumberRead preference setting of NaN.

Change the data return format to structure.

setdbprefs('DataReturnFormat','structure')

Import data into the MATLAB workspace.

sqlquery = 'SELECT productnumber,productdescription FROM producttable';
curs = exec(conn,sqlquery);
curs = fetch(curs,3);
curs.Data
ans = 

         productnumber: [3x1 double]
    productdescription: {3x1 cell}

Resulting data displays as a structure.

View the contents of the structure curs.Data to see the data.

curs.Data.productdescription
curs.Data.productnumber
ans = 

    'Victorian Doll'
    'Train Set'
    'Engine Kit'

ans =

     9
     8
     7

After you finish working with the cursor object, close it. Close the database connection.

close(curs)
close(conn)

Enable the insertion of a NaN as a NULL in the database by altering the write format setting for NULL numbers.

Establish connection conn to a MySQL database with user name username and password pwd. This database contains the table inventoryTable with these columns: productNumber, Quantity, Price, and inventoryDate.

conn = database('MySQL','username','pwd');

Specify NaN for the NullNumberWrite format.

setdbprefs('NullNumberWrite','NaN')

Numbers represented as NaN in the MATLAB workspace are exported to databases as NULL.

Select data in the table inventoryTable.

sqlquery = 'SELECT * FROM inventoryTable';
curs = exec(conn,sqlquery);
curs = fetch(curs);
curs.Data
ans = 

    ...
    [14]    [2000]    [19.1000]    '2014-10-22 10:52...'
    [15]    [1200]    [20.3000]    '2014-10-22 10:52...'
    [16]    [1400]    [34.3000]    '1999-12-31 00:00...'

Specify data ex_data to export into inventoryTable. The variable ex_data contains a NaN. For the inventory date, specify the date as the current moment.

ex_data = {24,NaN,30.00,datestr(now,'yyyy-mm-dd HH:MM:SS')};

Insert ex_data into the database using fastinsert with column names: productNumber, Quantity, Price, and inventoryDate.

colnames = {'productNumber','Quantity','Price','inventoryDate'};

fastinsert(conn,'inventoryTable',colnames,ex_data)

Select data in the table inventoryTable to see the last row with NaN data.

sqlquery = 'SELECT * FROM inventoryTable';
curs = exec(conn,sqlquery);
curs = fetch(curs);
curs.Data
ans = 

    ... 
    [15]    [1200]    [20.3000]    '2014-10-22 10:52...'
    [16]    [1400]    [34.3000]    '1999-12-31 00:00...'
    [24]    [ NaN]    [     30]    '2014-10-22 11:19...'

After you finish working with the cursor object, close it. Close the database connection.

close(curs)
close(conn)

Change the display of errors in MATLAB by altering the database error handling preferences.

Specify the store format for the ErrorHandling preference.

setdbprefs('ErrorHandling','store')

Database Toolbox™ stores errors generated by running database or exec in the Message property of the returned connection or cursor object.

Establish connection conn to a MySQL database with user name username and password pwd. This database contains the table productTable with the column productdescription.

conn = database('MySQL','username','pwd');

The cursor object contains the executed query. Close the cursor object. Fetch data from a closed cursor object.

sqlquery = 'SELECT productdescription FROM productTable';
curs = exec(conn,sqlquery);
close(curs)
curs = fetch(curs,3)
curs = 

  cursor with properties:

         Data: 0
     RowLimit: 0
     SQLQuery: 'SELECT productdescription FROM productTable'
      Message: 'Invalid Cursor: Invalid Cursor'
         Type: 'ODBCCursor Object'
    Statement: [1×1 database.internal.ODBCStatementHandle]

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

Specify the report format for the ErrorHandling preference.

setdbprefs('ErrorHandling','report')

With the ErrorHandling preference setting set to report, errors generated by running database or exec appear immediately in the Command Window.

The cursor object curs contains the executed query. Close the cursor object. Fetch data from a closed cursor object.

sqlquery = 'SELECT productdescription FROM productTable';
curs = exec(conn,sqlquery);
close(curs)
curs = fetch(curs,3);
Error using database.odbc.cursor/fetch (line 54)
Invalid Cursor

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

After you finish working with the cursor object, close it. Close the database connection.

close(curs)
close(conn)

Change multiple database preference simultaneously using setdbprefs.

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

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

For details about another way to change multiple settings, see Assign Values to Structure.

Assign values for specific preferences in a structure to let you change multiple database preferences simultaneously.

Assign values for preferences to fields in the structure s.

s.DataReturnFormat = 'numeric'; 
s.NullNumberRead = '0';
s
s = 

  struct with fields:

    DataReturnFormat: 'numeric'
      NullNumberRead: '0'

Set preferences using the values in s.

setdbprefs(s)

Run setdbprefs to check your preferences settings.

setdbprefs
ans = 

  struct with fields:

    DataReturnFormat: 'numeric'
       ErrorHandling: 'store'
      NullNumberRead: '0'
     NullNumberWrite: 'NaN'
      NullStringRead: 'null'
     NullStringWrite: 'null'
      FetchInBatches: 'no'
      FetchBatchSize: '1000'

Capture all preferences and their values in a structure.

Assign values for all preferences to s.

s = setdbprefs
s = 

  struct with fields:

    DataReturnFormat: 'numeric'
       ErrorHandling: 'store'
      NullNumberRead: '0'
     NullNumberWrite: 'NaN'
      NullStringRead: 'null'
     NullStringWrite: 'null'
      FetchInBatches: 'no'
      FetchBatchSize: '1000'

Use the MATLAB tab completion feature when obtaining the value for a preference.

s.D

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

s.DataReturnFormat
ans =

    'numeric'

You can save your preferences to a MAT-file to use them in future MATLAB sessions.

Assign the preferences to the variable ImportData and save them to a MAT-file ImportDataPrefs in your current folder.

ImportData = setdbprefs;
save ImportDataPrefs.mat ImportData

Later, load the data and restore the preferences.

load ImportDataPrefs.mat
setdbprefs(ImportData)

Input Arguments

collapse all

Database preference, specified as a character vector. To set multiple database preferences, enter the preference values in a cell array of character vectors. Then, match the order with the corresponding values in the value argument.

  • 'DataReturnFormat' — Format for data to import into the MATLAB workspace using the preference values listed here. Set the format based on the type of data being retrieved, memory considerations, and your preferred method of working with retrieved data. For example, to specify the format as a table, enter setdbprefs('DataReturnFormat','table').

    Allowable ValuesDescription

    'cellarray' (default)

    Import nonnumeric data into MATLAB cell arrays.

    'table'

    Import data into a MATLAB table. Use for all data types. Facilitates working with returned columns.

    'dataset'

    Note

    This value will be removed in a future release. Use 'table' instead.

    Import data into a MATLAB dataset array. Use for all data types. Facilitates working with returned columns. This value requires Statistics and Machine Learning Toolbox™.

    'numeric'

    Import data into a 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'

    Import data into a MATLAB structure. Use for all data types. Facilitates working with returned columns.

  • 'ErrorHandling' — Specify how to handle errors when importing data using the preference values listed here. Set this parameter before you run database or exec. For example, to specify storing errors in the Message field of the returned connection object, enter setdbprefs('ErrorHandling','store').

    Allowable ValuesDescription

    'store' (default)

    Store errors from running database in the Message field of the returned connection object. Store errors from running exec in the Message property of the returned cursor object.

    'report'

    Immediately display errors from running database or exec in the Command Window.

  • NULL data — Specify how to import or export NULL data in the MATLAB workspace or the database using the preference values listed here. For example, to import data and display all NULL numbers in the database as a 0 in the MATLAB workspace, enter setdbprefs('NullNumberRead','0').

    Database PreferenceAllowable ValuesDescription

    'NullNumberRead'

    Character vector; for example, '0'

    How NULL numbers appear after being imported from a database into the MATLAB workspace. NaN is the default value.

    • If 'DataReturnFormat' is set to 'numeric', then values such as 'NULL' cannot be set.

    • If 'DataReturnFormat' is set to 'cellarray', then numbers appear as NaN and not as empty brackets.

    Set this parameter before running fetch.

    'NullNumberWrite'

    Character vector; for example, 'NaN' (default)

    How numbers appear in the database after being exported from the MATLAB workspace to a database.

    Regardless of the value of 'NullNumberWrite', a NULL value is always written to the database when you input [] or NaN for a numeric data type.

    'NullStringRead'

    Character vector; for example, 'null' (default)

    How NULL strings appear after being imported from a database into the MATLAB workspace. Set this parameter before running fetch.

    'NullStringWrite'

    Character vector; for example, 'null' (default)

    Specify the character vector that represents a NULL string in a database after exporting it from the MATLAB workspace to the database.

    For character vector inputs, a NULL value is written to the database only when the input value matches the value of 'NullStringWrite'.

  • Fetching data — Specify how to import data using the preference values listed here. Control the number of rows that are imported from the database at a time. For example, to automate fetching data in batches, enter setdbprefs('FetchInBatches','yes').

    Note

    These database preferences will be removed in a future release.

    Database PreferenceAllowable ValuesDescription
    'FetchInBatches'

    'yes' or 'no' (default)

    Automates fetching in batches for large data sets where you can run into Java® heap memory errors in MATLAB. When the value is 'yes', fetch and runsqlscript import the data in batches in size of 'FetchBatchSize'. For an example, see fetch.

    'FetchBatchSize'

    Input numeric value, default value is '1000'. Supported values are 1000 through 1000000.

    Automates fetching in batches for large data sets when used with 'FetchInBatches'. When the value of 'FetchInBatches' is 'yes', fetch and runsqlscript import the data in batches in size of 'FetchBatchSize'.

    For an example, see fetch.

Example: 'DataReturnFormat'

Example: {'DataReturnFormat';'NullStringRead'}

Data Types: char

Database preference value, specified as a character vector. To set multiple database preferences, enter the preference values in a cell array of character vectors. Then, match the order with the corresponding preferences in the preference argument. For allowable values, see the tables in preference.

Example: 'NaN'

Example: {'numeric';'NaN'}

Data Types: char

Database preferences, specified as a structure to include all the database preferences that you specify.

Data Types: struct

Output Arguments

collapse all

Database preferences, returned as a structure containing database preference settings and values.

Alternative Functionality

For a visual way to set database preferences, click Preferences in the MATLAB toolstrip and click Database Toolbox. Enter values for each database preference.

Introduced before R2006a

Was this topic helpful?