fetch

Import data into MATLAB workspace from execution of SQL statement

collapse all in page

Syntax

results = fetch(conn,sqlquery)
results = fetch(conn,sqlquery,opts)
results = fetch(___,Name,Value)
[results,metadata] = fetch(___)
results = fetch(conn,pstmt)
results = fetch(conn,pstmt,Name,Value)

Description

example

results = fetch(conn,sqlquery) returns all rows of data after executing the SQL statement sqlquery for the connection object. fetch imports data in batches.

example

results = fetch(conn,sqlquery,opts) customizes options for importing data from an executed SQL query by using the SQLImportOptions object.

example

results = fetch(___,Name,Value) specifies additional options using one or more name-value pair arguments and any of the previous input argument combinations. For example, 'MaxRows',5 imports five rows of data.

example

[results,metadata] = fetch(___) also returns the metadata table, which contains metadata information about the imported data.

example

results = fetch(conn,pstmt) returns all rows of data after executing the SQL SELECT prepared statement pstmt for the connection object.

results = fetch(conn,pstmt,Name,Value) specifies additional options using one or more name-value pair arguments. For example, 'DataReturnFormat','structure' imports data as a structure.

Examples

collapse all

Open Script

Import all product data from a Microsoft® SQL Server® database table into MATLAB® by using the connection object. Then, determine the highest unit cost among products in the table.

Create an ODBC database connection to a Microsoft® SQL Server® database with Windows® authentication. Specify a blank user name and password. The database contains the table productTable.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Check the database connection. If the Message property is empty, then the connection is successful.

conn.Message

ans =

     []

Import all the data from productTable by using the connection object and SQL query, and display the imported data.

sqlquery = 'SELECT * FROM productTable';
results = fetch(conn,sqlquery)

results =

  10×5 table

    productNumber    stockNumber    supplierNumber    unitCost    productDescription 
    _____________    ___________    ______________    ________    ___________________

          9          1.2597e+05          1003            13       {'Victorian Doll' }
          8          2.1257e+05          1001             5       {'Train Set'      }
          7          3.8912e+05          1007            16       {'Engine Kit'     }
          2          4.0031e+05          1002             9       {'Painting Set'   }
          4          4.0034e+05          1008            21       {'Space Cruiser'  }
          1          4.0035e+05          1001            14       {'Building Blocks'}
          5          4.0046e+05          1005             3       {'Tin Soldier'    }
          6          4.0088e+05          1004             8       {'Sail Boat'      }
          3            4.01e+05          1009            17       {'Slinky'         }
         10          8.8865e+05          1006            24       {'Teddy Bear'     }

Determine the highest unit cost for all products in the table.

max(results.unitCost)

ans =

    24

Close the database connection.

close(conn)
Open Live Script

Customize import options when importing data from the results of an SQL query on a database. Control the import options by creating an SQLImportOptions object. Then, customize import options for different columns in the SQL query. Import data using the fetch function.

This example uses the employees_database.mat file, which contains the columns first_name, hire_date, and DEPARTMENT_NAME. The example also uses a Microsoft® SQL Server® Version 11.00.2100 database and the Microsoft SQL Server Driver 11.00.5058.

Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Load employee information into the MATLAB® workspace.

employeedata = load('employees_database.mat');

Create the employees and departments database tables using the employee information.

emps = employeedata.employees;
depts = employeedata.departments;

sqlwrite(conn,'employees',emps)
sqlwrite(conn,'departments',depts)

Create an SQLImportOptions object using an SQL query and the databaseImportOptions function. This query retrieves all information for employees who are sales managers or programmers.

sqlquery = ['SELECT * from employees e join departments d ' ...
    'on (e.department_id = d.department_id) where job_id ' ...
    'in (''IT_PROG'',''SA_MAN'')'];
opts = databaseImportOptions(conn,sqlquery)
opts = 
  SQLImportOptions with properties:

           ExcludeDuplicates: false
          VariableNamingRule: 'modify'

               VariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more}
               VariableTypes: {'double', 'char', 'char' ... and 13 more}
       SelectedVariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more}
                  FillValues: { NaN, '', ''  ... and 13 more }

             VariableOptions: Show all 16 VariableOptions

Display the current import options for the variables selected in the SelectedVariableNames property of the SQLImportOptions object.

vars = opts.SelectedVariableNames;
varOpts = getoptions(opts,vars)
varOpts = 
    1x16 SQLVariableImportOptions array with properties:

   Variable Options:
                         (1) |          (2) |         (3) |     (4) |            (5) |         (6) |      (7) |      (8) |              (9) |         (10) |            (11) |        (12) |            (13) |              (14) |         (15) |          (16)
         Name: 'employee_id' | 'first_name' | 'last_name' | 'email' | 'phone_number' | 'hire_date' | 'job_id' | 'salary' | 'commission_pct' | 'manager_id' | 'department_id' | 'temporary' | 'DEPARTMENT_ID' | 'DEPARTMENT_NAME' | 'MANAGER_ID' | 'LOCATION_ID'
         Type:      'double' |       'char' |      'char' |  'char' |         'char' |      'char' |   'char' | 'double' |         'double' |     'double' |        'double' |    'double' |        'double' |            'char' |     'double' |      'double'
  MissingRule:        'fill' |       'fill' |      'fill' |  'fill' |         'fill' |      'fill' |   'fill' |   'fill' |           'fill' |       'fill' |          'fill' |      'fill' |          'fill' |            'fill' |       'fill' |        'fill'
    FillValue:           NaN |           '' |          '' |      '' |             '' |          '' |       '' |      NaN |              NaN |          NaN |             NaN |         NaN |             NaN |                '' |          NaN |           NaN

	To access sub-properties of each variable, use getoptions

Change the data types for the hire_date, DEPARTMENT_NAME, and first_name variables using the setoptions function. Then, display the updated import options. Because hire_date stores date and time data, change the data type of this variable to datetime. Because DEPARTMENT_NAME designates a finite set of repeating values, change the data type of this variable to categorical. Also, change the name of this variable to lowercase. Because first_name stores text data, change the data type of this variable to string.

opts = setoptions(opts,'hire_date','Type','datetime');
opts = setoptions(opts,'DEPARTMENT_NAME','Name','department_name', ...
    'Type','categorical');
opts = setoptions(opts,'first_name','Type','string');

vars = opts.SelectedVariableNames;
varOpts = getoptions(opts,vars)
varOpts = 
    1x16 SQLVariableImportOptions array with properties:

   Variable Options:
                         (1) |          (2) |         (3) |     (4) |            (5) |         (6) |      (7) |      (8) |              (9) |         (10) |            (11) |        (12) |            (13) |              (14) |         (15) |          (16)
         Name: 'employee_id' | 'first_name' | 'last_name' | 'email' | 'phone_number' | 'hire_date' | 'job_id' | 'salary' | 'commission_pct' | 'manager_id' | 'department_id' | 'temporary' | 'DEPARTMENT_ID' | 'department_name' | 'MANAGER_ID' | 'LOCATION_ID'
         Type:      'double' |     'string' |      'char' |  'char' |         'char' |  'datetime' |   'char' | 'double' |         'double' |     'double' |        'double' |    'double' |        'double' |     'categorical' |     'double' |      'double'
  MissingRule:        'fill' |       'fill' |      'fill' |  'fill' |         'fill' |      'fill' |   'fill' |   'fill' |           'fill' |       'fill' |          'fill' |      'fill' |          'fill' |            'fill' |       'fill' |        'fill'
    FillValue:           NaN |    <missing> |          '' |      '' |             '' |         NaT |       '' |      NaN |              NaN |          NaN |             NaN |         NaN |             NaN |       <undefined> |          NaN |           NaN

	To access sub-properties of each variable, use getoptions

Select the three modified variables using the SelectVariableNames property.

opts.SelectedVariableNames = {'first_name','hire_date','department_name'};

Import and display the results of the SQL query using the fetch function.

employees_data = fetch(conn,sqlquery,opts)
employees_data=10×3 table
    first_name      hire_date     department_name
    ___________    ___________    _______________

    "Alexander"    03-Jan-2006         IT        
    "Bruce"        21-May-2007         IT        
    "David"        25-Jun-2005         IT        
    "Valli"        05-Feb-2006         IT        
    "Diana"        07-Feb-2007         IT        
    "John"         01-Oct-2004         Sales     
    "Karen"        05-Jan-2005         Sales     
    "Alberto"      10-Mar-2005         Sales     
    "Gerald"       15-Oct-2007         Sales     
    "Eleni"        29-Jan-2008         Sales

Delete the employees and departments database tables using the execute function.

execute(conn,'DROP TABLE employees')
execute(conn,'DROP TABLE departments')

Close the database connection.

close(conn)
Open Live Script

Specify the data return format and the number of imported rows for the results of an SQL query. Import data using an SQL query and the fetch function.

This example uses a Microsoft® SQL Server® Version 11.00.2100 database and the Microsoft SQL Server Driver 11.00.5058.

Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Load patient information into the MATLAB® workspace.

patients = readtable('patients.xls');

Create the patients database table using the patient information. 

tablename = 'patients';
sqlwrite(conn,tablename,patients)

Select all data from the patients database table and import five rows from the table as a structure. Use the 'DataReturnFormat' name-value pair argument to specify returning the result data as a structure. Also, use the 'MaxRows' name-value pair argument to specify five rows. Display the imported data.

sqlquery = ['SELECT * FROM ' tablename];
results = fetch(conn,sqlquery,'DataReturnFormat','structure', ...
    'MaxRows',5)
results = struct with fields:
                    LastName: {5×1 cell}
                      Gender: {5×1 cell}
                         Age: [5×1 double]
                    Location: {5×1 cell}
                      Height: [5×1 double]
                      Weight: [5×1 double]
                      Smoker: [5×1 double]
                    Systolic: [5×1 double]
                   Diastolic: [5×1 double]
    SelfAssessedHealthStatus: {5×1 cell}

Delete the patients database table using the execute function.

sqlquery = ['DROP TABLE ' tablename];
execute(conn,sqlquery)

Close the database connection.

close(conn)
Open Live Script

Import product data from a Microsoft® SQL Server® database table into MATLAB® by using an ODBC connection. The table contains a variable name with a non-ASCII character. When importing data, preserve the names of all the variables.

Create an ODBC database connection to an SQL Server database with Windows® authentication. Specify a blank user name and password. The database contains the table productTable.

datasource = "MSSQLServerAuth";
conn = database(datasource,"","");

Check the database connection. If the Message property is empty, then the connection is successful.

conn.Message
ans =

     []

Add a column to the database table productTable. The column name contains a non-ASCII character.

sqlquery = "ALTER TABLE productTable ADD tamaño varchar(30)"; 
execute(conn,sqlquery)

Import data from the database table productTable. The fetch function returns a MATLAB table that contains the product data. Display the first three rows of the data in the table.

sqlquery = "SELECT * FROM productTable";
data = fetch(conn,sqlquery);
head(data,3)
ans=3×6 table
    productNumber    stockNumber    supplierNumber    unitCost    productDescription      tama_o  
    _____________    ___________    ______________    ________    __________________    __________

          9          1.2597e+05          1003            13       {'Victorian Doll'}    {0×0 char}
          8          2.1257e+05          1001             5       {'Train Set'     }    {0×0 char}
          7          3.8912e+05          1007            16       {'Engine Kit'    }    {0×0 char}

The fetch function converts the name of the new variable into ASCII characters.

Preserve the name of the variable that contains the non-ASCII character by specifying the VariableNamingRule name-value pair argument. Import the data again.

data = fetch(conn,sqlquery, ...
    'VariableNamingRule',"preserve");
head(data,3)
ans=3×6 table
    productNumber    stockNumber    supplierNumber    unitCost    productDescription      tamaño  
    _____________    ___________    ______________    ________    __________________    __________

          9          1.2597e+05          1003            13       {'Victorian Doll'}    {0×0 char}
          8          2.1257e+05          1001             5       {'Train Set'     }    {0×0 char}
          7          3.8912e+05          1007            16       {'Engine Kit'    }    {0×0 char}

The fetch function preserves the non-ASCII character in the variable name.

Close the database connection.

close(conn)
Open Live Script

Retrieve metadata information when importing data from an SQL query. Import data using the fetch function and explore the metadata information by using dot notation.

This example uses the outages.csv file, which contains outage data. Also, the example uses a Microsoft® SQL Server® Version 11.00.2100 database and the Microsoft SQL Server Driver 11.00.5058.

Create a database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password.

datasource = "MS SQL Server Auth";
conn = database(datasource,"","");

Load outage information into the MATLAB® workspace.

outages = readtable("outages.csv");

Create the outages database table using the outage information.

tablename = "outages";
sqlwrite(conn,tablename,outages)

Import the data into the MATLAB workspace and return metadata information about the imported data. 

sqlquery = "SELECT * FROM outages";
[results,metadata] = fetch(conn,sqlquery);

View the names of the variables in the imported data.

metadata.Properties.RowNames
ans = 6×1 cell
    {'Region'         }
    {'OutageTime'     }
    {'Loss'           }
    {'Customers'      }
    {'RestorationTime'}
    {'Cause'          }

View the data type of each variable in the imported data.

metadata.VariableType
ans = 6×1 cell
    {'char'  }
    {'char'  }
    {'double'}
    {'double'}
    {'char'  }
    {'char'  }

View the missing data value for each variable in the imported data.

metadata.FillValue
ans=6×1 cell array
    {0×0 char}
    {0×0 char}
    {[   NaN]}
    {[   NaN]}
    {0×0 char}
    {0×0 char}

View the indices of the missing data for each variable in the imported data.

metadata.MissingRows
ans=6×1 cell array
    {  0×1 double}
    {  0×1 double}
    {604×1 double}
    {328×1 double}
    { 29×1 double}
    {  0×1 double}

Display the first eight rows of the imported data that contain missing restoration time. data contains restoration time in the fifth variable. Use the numeric indices to find the rows with missing data.

index = metadata.MissingRows{5,1};
nullrestoration = results(index,:);
head(nullrestoration)
ans=8×6 table
       Region                OutageTime              Loss     Customers     RestorationTime           Cause        
    _____________    ___________________________    ______    __________    _______________    ____________________

    {'SouthEast'}    {'2003-01-23 00:49:00.000'}    530.14    2.1204e+05      {0×0 char}       {'winter storm'    }
    {'NorthEast'}    {'2004-09-18 05:54:00.000'}         0             0      {0×0 char}       {'equipment fault' }
    {'MidWest'  }    {'2002-04-20 16:46:00.000'}     23141           NaN      {0×0 char}       {'unknown'         }
    {'NorthEast'}    {'2004-09-16 19:42:00.000'}      4718           NaN      {0×0 char}       {'unknown'         }
    {'SouthEast'}    {'2005-09-14 15:45:00.000'}    1839.2    3.4144e+05      {0×0 char}       {'severe storm'    }
    {'SouthEast'}    {'2004-08-17 17:34:00.000'}     624.1    1.7879e+05      {0×0 char}       {'severe storm'    }
    {'SouthEast'}    {'2006-01-28 23:13:00.000'}    498.78           NaN      {0×0 char}       {'energy emergency'}
    {'West'     }    {'2003-06-20 18:22:00.000'}         0             0      {0×0 char}       {'energy emergency'}

Delete the outages database table using the execute function.

sqlstr = "DROP TABLE ";
sqlquery = strcat(sqlstr,tablename);
execute(conn,sqlquery)

Close the database connection.

close(conn)
Open Live Script

Create an SQL prepared statement to import data from a Microsoft® SQL Server® database using a JDBC database connection. Use the SELECT SQL statement for the SQL query. Import the data from the database and display the results.

Create a JDBC database connection to an SQL Server database with Windows® authentication. Specify a blank user name and password. 

datasource = 'MSSQLServerJDBCAuth';
conn = database(datasource,'','');

Create an SQL prepared statement for importing data from the SQL Server database using the JDBC database connection. The question marks in the SELECT SQL statement indicate it is an SQL prepared statement. This statement selects all data from the database table inventoryTable for the inventory that has an inventory date within a specified date range.

query = strcat("SELECT * FROM inventoryTable ", ...
    "WHERE inventoryDate > ? AND inventoryDate < ?");
pstmt = databasePreparedStatement(conn,query)
pstmt = 
  SQLPreparedStatement with properties:

           SQLQuery: "SELECT * FROM inventoryTable where inventoryDate > ? AND inventoryDate < ?"
     ParameterCount: 2
     ParameterTypes: ["string"    "string"]
    ParameterValues: {[]  []}

pstmt is an SQLPreparedStatement object with these properties:

  • SQLQuery — SQL prepared statement query

  • ParameterCount — Parameter count

  • ParameterTypes — Parameter types

  • ParameterValues — Parameter values

Bind parameter values in the SQL prepared statement. Select both parameters in the SQL prepared statement using their numeric indices. Specify the values to bind as the inventory date range between January 1, 2014 and December 31, 2014. Match the format of dates in the database. The bindParamValues function updates the values in the ParameterValues property of the pstmt object. 

selection = [1 2];
values = {"2014-01-01 00:00:00.000", ...
    "2014-12-31 00:00:00.000"};
pstmt = bindParamValues(pstmt,selection,values)
pstmt = 
  SQLPreparedStatement with properties:

           SQLQuery: "SELECT * FROM inventoryTable where inventoryDate > ? AND inventoryDate < ?"
     ParameterCount: 2
     ParameterTypes: ["string"    "string"]
    ParameterValues: {["2014-01-01 00:00:00.000"]  ["2014-12-31 00:00:00.000"]}

Import data from the database using the fetch function and bound parameter values. The results contain four rows of data that represent all inventory with an inventory date between January 1, 2014 and December 31, 2014.

results = fetch(conn,pstmt)
results=4×4 table
    productNumber    Quantity    Price         inventoryDate     
    _____________    ________    _____    _______________________

          1            1700      14.5     {'2014-09-23 09:38:34'}
          2            1200         9     {'2014-07-08 22:50:45'}
          3             356        17     {'2014-05-14 07:14:28'}
          7            6034        16     {'2014-08-06 08:38:00'}

Close the SQL prepared statement and database connection.

close(pstmt)
close(conn)

Input Arguments

collapse all

Database connection, specified as a connection object created with the database function.

SQL statement, specified as a character vector or string scalar. The SQL statement can be any valid SQL statement, including nested queries. The SQL statement can be a stored procedure, such as {call sp_name (parm1,parm2,...)}. For stored procedures that return one or more result sets, use fetch function. For procedures that return output arguments, use runstoredprocedure.

For information about the SQL query language, see the SQL Tutorial.

Data Types: char | string

Database import options, specified as an SQLImportOptions object.

SQL prepared statement, specified as an SQLPreparedStatement object.

Name-Value Pair Arguments

Specify optional comma-separated pairs of Name,Value arguments. Name is the argument name and Value is the corresponding value. Name must appear inside quotes. You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN.

Example: results = fetch(conn,sqlquery,'MaxRows',50,'DataReturnFormat','structure') imports 50 rows of data as a structure.

Maximum number of rows to return, specified as the comma-separated pair consisting of 'MaxRows' and a positive numeric scalar. By default, the fetch function returns all rows from the executed SQL query. Use this name-value pair argument to limit the number of rows imported into MATLAB®.

Example: 'MaxRows',10

Data Types: double

Data return format, specified as the comma-separated pair consisting of 'DataReturnFormat' and one of these values:

  • 'table'

  • 'cellarray'

  • 'numeric'

  • 'structure'

Use the 'DataReturnFormat' name-value pair argument to specify the data type of the result data results. To specify integer classes for numeric data, use the opts input argument.

You can specify these values using character vectors or string scalars.

Example: 'DataReturnFormat','cellarray' imports data as a cell array.

Variable naming rule, specified as the comma-separated pair consisting of 'VariableNamingRule' and one of these values:

  • "modify" — Remove non-ASCII characters from variable names when the fetch function imports data.

  • "preserve" — Preserve most variable names when the fetch function imports data. For details, see the Limitations section.

Example: 'VariableNamingRule',"modify"

Data Types: string

Output Arguments

collapse all

Result data, returned as a table, cell array, structure, or numeric matrix. The result data contains all rows of data from the executed SQL statement by default.

Use the 'MaxRows' name-value pair argument to specify the number of rows of data to import. Use the 'DataReturnFormat' name-value pair argument to specify the data type of the result data.

When the executed SQL statement does not return any rows, the result data is an empty table.

Metadata information, returned as a table with these variables.

Variable NameVariable DescriptionVariable Data Type

VariableType

Data type of each variable in the imported data

Cell array of character vectors

FillValue

Value of missing data for each variable in the imported data

Cell array of missing data values

MissingRows

Indices for each occurrence of missing data in each variable of the imported data

Cell array of numeric indices

By default, the fetch function imports text data as a character vector and numeric data as a double. FillValue is an empty character array (for text data) or NaN (for numeric data) by default. To change the missing data value to another value, use the SQLImportOptions object.

The RowNames property of the metadata table contains the names of the variables in the imported data.

Limitations

The name-value pair argument 'MaxRows' has these limitations:

  • If you are using Microsoft® Access™, the native ODBC interface is not supported.

  • Not all database drivers support setting the maximum number of rows before query execution. For an unsupported driver, modify your SQL query to limit the maximum number of rows to return. The SQL syntax varies with the driver. For details, consult the driver documentation.

The name-value pair argument 'VariableNamingRule' has these limitations:

  • The fetch function returns an error when you specify the 'VariableNamingRule' name-value pair argument and set the 'DataReturnFormat' name-value pair argument to cellarray, structure, or numeric.

  • The fetch function returns a warning when you set the VariableNamingRule property of the SQLImportOptions object to "preserve" and set the 'DataReturnFormat' name-value pair argument to structure.

  • The fetch function returns an error when you use the 'VariableNamingRule' name-value pair argument with the SQLImportOptions object opts.

  • When the 'VariableNamingRule' name-value pair argument is set to the value 'modify':

    • These variable names are reserved identifiers for the table data type: Properties, RowNames, and VariableNames.

    • The length of each variable name must be less than the number returned by namelengthmax.

Tips

  • The order of records in your database does not remain constant. Sort data using the SQL ORDER BY command in your sqlquery statement.

  • For Microsoft Excel®, tables in sqlquery are Excel worksheets. By default, some worksheet names include a $ symbol. To select data from a worksheet with this name format, use an SQL statement of the form SELECT * FROM "Sheet1$" (or 'Sheet1$').

  • Before you modify database tables, ensure that the database is not open for editing. If you try to edit the database while it is open, you receive this MATLAB error:

    [Vendor][ODBC Driver] The database engine could not lock 
table 'TableName' because it is already in use by 
another person or process.

  • The PostgreSQL database management system supports multidimensional fields, but SQL SELECT statements fail when retrieving these fields unless you specify an index.

  • Some databases require that you include a symbol, such as #, before and after a date in a query, as follows:

    execute(conn,'SELECT * FROM mydb WHERE mydate > #03/05/2005#')

  • Executing the fetch function with the opts input argument and the 'DataReturnFormat' name-value pair argument set to the 'numeric' value has no effect. A corresponding warning message appears in the Command Window.

Alternative Functionality

App

The fetch function imports data using the command line. To import data interactively, use the Database Explorer app.

Compatibility Considerations

expand all

Behavior changed in R2018b

Behavior changed in R2018b

See Also

| | | | | | | | |

Topics

External Websites

Introduced in R2006b

Database Toolbox Documentation

Support