fetch

Import data from execution of SQL statement

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

results = fetch(conn,sqlquery) executes the SQL statement specified by sqlquery using the database connection conn and returns all rows.

conn can be any of the following connection objects:

• MySQL^®

• PostgreSQL^®

• DuckDB™

• SQLite

• ODBC

• JDBC

Use this function when you need to run a custom SQL query and retrieve its results directly into MATLAB^®.

results = fetch(conn,sqlquery,opts) applies customized import options from the SQLImportOptions object, opts.

results = fetch(___,Name=Value) specifies additional options using one or more name-value arguments with any of the previous syntaxes. For example, set MaxRows=5 to import five rows of data.

[results,metadata] = fetch(___) also returns the a table with metadata about the imported data. The metadata argument is not valid when conn is an SQLite connection object.

results = fetch(conn,pstmt) executes the SQL SELECT prepared statement pstmt and returns all rows of data. Use prepared statements for parameterized SQL queries to enhance performance and security when running the same query multiple times. conn must be a JDBC connection object.

results = fetch(conn,pstmt,Name=Value) specifies additional options using one or more name-value arguments, where conn is a JDBC connection object. For example, fetch(conn,pstmt,DataReturnFormat="structure") imports data as a structure.

Examples

Import All Data Using MySQL Native Interface

Import all product data from a MySQL® database table into MATLAB® using the MySQL native interface and the fetch function. Determine the highest unit cost among products in the table. Then, use a row filter to import only the data for products with a unit cost less than 15.

Create a MySQL native interface database connection to a MySQL database using a data source, username, and password. The database contains the table productTable.

datasource = "MySQLNative";
username = "root";
password = "matlab";
conn = mysql(datasource,username,password);

Import all the data from productTable by using the connection object and SQL query. Then, display the first five rows of the imported data.

sqlquery = "SELECT * FROM productTable";
data = fetch(conn,sqlquery);
head(data,5)

    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"  

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

max(data.unitCost)

ans = 
24

Now, import the data using a row filter. The filter condition is that unitCost must be less than 15.

rf = rowfilter("unitCost");
rf = rf.unitCost < 15;
data = fetch(conn,sqlquery,"RowFilter",rf);

Again, display the first five rows of the imported data.

head(data,5)

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

          9          1.2597e+05          1003            13       "Victorian Doll"  
          8          2.1257e+05          1001             5       "Train Set"       
          2          4.0031e+05          1002             9       "Painting Set"    
          1          4.0034e+05          1001            14       "Building Blocks" 
          5          4.0046e+05          1005             3       "Tin Soldier"     

Close the database connection.

close(conn)

Import Data from SQL Query Using Import Options with PostgreSQL Native Interface

Customize import options when importing data from the results of an SQL query on a PostgreSQL database using the PostgreSQL native interface. 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 uses a PostgreSQL database version 9.405 database and the libpq driver version 10.12.

Create a PostgreSQL native interface database connection to a PostgreSQL database with a data source name, username, and password.

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";

conn = postgresql(datasource,username,password);

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 = strcat("SELECT * from employees e join departments d ", ...
    "on (e.department_id = d.department_id) WHERE ", ...
    "(job_id = 'IT_PROG' or job_id = 'SA_MAN')");
opts = databaseImportOptions(conn,sqlquery)

opts = 
  SQLImportOptions with properties:

           ExcludeDuplicates: false
          VariableNamingRule: 'preserve'

               VariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more}
               VariableTypes: {'double', 'string', 'string' ... and 13 more}
       SelectedVariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more}
                  FillValues: { NaN,  <missing>,  <missing>  ... and 13 more }
                   RowFilter: <unconstrained> 

             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_1' | 'department_name' | 'manager_id_1' | 'location_id'
         Type:      'double' |     'string' |    'string' |  'string' |       'string' |  'datetime' |  'string' | 'double' |         'double' |     'double' |        'double' |   'logical' |          'double' |          'string' |       'double' |      'double'
  MissingRule:        'fill' |       'fill' |      'fill' |    'fill' |         'fill' |      'fill' |    'fill' |   'fill' |           'fill' |       'fill' |          'fill' |      'fill' |            'fill' |            'fill' |         'fill' |        'fill'
    FillValue:           NaN |    <missing> |   <missing> | <missing> |      <missing> |         NaT | <missing> |      NaN |              NaN |          NaN |             NaN |           0 |               NaN |         <missing> |            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. For efficiency, change the data type of the hire_date variable to string. Because department_name designates a finite set of repeating values, change the data type of this variable to categorical. Because first_name stores text data, change the data type of this variable to char.

opts = setoptions(opts,"hire_date","Type","string");
opts = setoptions(opts,"department_name","Type","categorical");
opts = setoptions(opts,"first_name","Type","char");

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_1' | 'department_name' | 'manager_id_1' | 'location_id'
         Type:      'double' |       'char' |    'string' |  'string' |       'string' |    'string' |  'string' | 'double' |         'double' |     'double' |        'double' |   'logical' |          'double' |     'categorical' |       'double' |      'double'
  MissingRule:        'fill' |       'fill' |      'fill' |    'fill' |         'fill' |      'fill' |    'fill' |   'fill' |           'fill' |       'fill' |          'fill' |      'fill' |            'fill' |            'fill' |         'fill' |        'fill'
    FillValue:           NaN |           '' |   <missing> | <missing> |      <missing> |   <missing> | <missing> |      NaN |              NaN |          NaN |             NaN |           0 |               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"];

Set the filter condition to import only the data for the employees hired before January 1, 2006.

opts.RowFilter = opts.RowFilter.hire_date < datetime(2006,01,01)

opts = 
  SQLImportOptions with properties:

           ExcludeDuplicates: false
          VariableNamingRule: 'preserve'

               VariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more}
               VariableTypes: {'double', 'char', 'string' ... and 13 more}
       SelectedVariableNames: {'first_name', 'hire_date', 'department_name'}
                  FillValues: { NaN, '',  <missing>  ... and 13 more }
                   RowFilter: hire_date < 01-Jan-2006 

             VariableOptions: Show all 16 VariableOptions

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

employees_data = fetch(conn,sqlquery,opts)

employees_data=4×3 table
    'David'    "2005-06-25 00:00:00"    IT
    'Alberto'    "2005-03-10 00:00:00"    Sales
    'Karen'    "2005-01-05 00:00:00"    Sales
    'John'    "2004-10-01 00:00:00"    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)

Retrieve Limited Number of Rows from DuckDB Database Table

Load the airline data.

airlineData = readtable("airlinesmall_subset.xlsx");

Create a connection to a DuckDB™ database file by using the duckdb function. This example assumes you are creating a database file, airlineDatabase.db, to store the airline data.

conn = duckdb("airlineDatabase.db");

Export the airline data to a database table named airlinesTable by using the sqlwrite function.

tablename = "airlinesTable";
sqlwrite(conn,tablename,airlineData)

Import all rows into MATLAB® by using the fetch function.

sqlquery = "SELECT * FROM airlinesTable";
results = fetch(conn,sqlquery)

results=1338×29 table
    Year    Month    DayofMonth    DayOfWeek    DepTime    CRSDepTime    ArrTime    CRSArrTime    UniqueCarrier    FlightNum    TailNum     ActualElapsedTime    CRSElapsedTime    AirTime    ArrDelay    DepDelay    Origin    Dest     Distance    TaxiIn    TaxiOut    Cancelled    CancellationCode    Diverted    CarrierDelay    WeatherDelay    SDelay    SecurityDelay    LateAircraftDelay
    ____    _____    __________    _________    _______    __________    _______    __________    _____________    _________    ________    _________________    ______________    _______    ________    ________    ______    _____    ________    ______    _______    _________    ________________    ________    ____________    ____________    ______    _____________    _________________

    1996      1          18            4         2117         2120        2305         2259           "HP"            415       "N637AW"           108                 99             85          6          -3       "COS"     "PHX"       551        5         18           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
    1996      1          12            5         1252         1245        1511         1500           "HP"            610       "N905AW"            79                 75             58         11           7       "LAX"     "PHX"       370        3         18           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
    1996      1          16            2         1441         1445        1708         1721           "HP"            211       "N165AW"            87                 96             74        -13          -4       "RNO"     "PHX"       601        4          9           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
    1996      1           1            1         2258         2300        2336         2335           "HP"           1245       "N183AW"            38                 35             20          1          -2       "TUS"     "PHX"       110        6         12           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
    1996      1           4            4         1814         1814        1901         1910           "US"            683       "N963VJ"            47                 56             34         -9           0       "DTW"     "PIT"       201        6          7           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
    1996      1          31            3         1822         1820        1934         1925           "US"            757       "N912VJ"            72                 65             52          9           2       "PHL"     "PIT"       267        6         14           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
    1996      1          18            4          729          730         841          843           "US"           1564       "N941VJ"            72                 73             58         -2          -1       "DCA"     "PVD"       357        3         11           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
    1996      1          26            5         1704         1705        1829         1839           "NW"           1538       "N960N"             85                 94             69        -10          -1       "DTW"     "RIC"       456        3         13           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
    1996      1          11            4         1858         1850        1959         1956           "US"           2225       "N351US"            61                 66             48          3           8       "PHL"     "ROC"       257        2         11           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
    1996      1           7            7         2100         2100        2215         2220           "WN"            174       "N677"              75                 80             65         -5           0       "OAK"     "SAN"       446        3          7           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
    1996      1          16            2         1715         1715        1805         1810           "WN"            167       "N20"               50                 55             41         -5           0       "DAL"     "SAT"       248        2          7           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
    1996      1          30            2         1205         1210        1312         1322           "CO"           1127       "N10236"            67                 72             52        -10          -5       "CLE"     "SDF"       304        4         11           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
    1996      1           8            1         1232         1232        1520         1517           "AS"             62       "N755AS"           108                105             94          3           0       "KTN"     "SEA"       680        5          9           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
    1996      1          30            2         1558         1600        1744         1757           "UA"           2410       "N310UA"           106                117             92        -13          -2       "SFO"     "SEA"       679        3         11           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
    1996      1          14            7         2331         2325         616          605           "UA"            132       "N4714U"           285                280            263         11           6       "HNL"     "SFO"      2398        5         17           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
    1996      1           7            7         1513            0        1738            0           "AA"             83       "N339AA"           265                268            246         10          13       "ORD"     "SFO"      1846        2         17           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
      ⋮

Limit the number of imported rows by using the MaxRows name-value argument.

results = fetch(conn,sqlquery,MaxRows=5)

results=5×29 table
    Year    Month    DayofMonth    DayOfWeek    DepTime    CRSDepTime    ArrTime    CRSArrTime    UniqueCarrier    FlightNum    TailNum     ActualElapsedTime    CRSElapsedTime    AirTime    ArrDelay    DepDelay    Origin    Dest     Distance    TaxiIn    TaxiOut    Cancelled    CancellationCode    Diverted    CarrierDelay    WeatherDelay    SDelay    SecurityDelay    LateAircraftDelay
    ____    _____    __________    _________    _______    __________    _______    __________    _____________    _________    ________    _________________    ______________    _______    ________    ________    ______    _____    ________    ______    _______    _________    ________________    ________    ____________    ____________    ______    _____________    _________________

    1996      1          18            4         2117         2120        2305         2259           "HP"            415       "N637AW"           108                 99            85           6          -3       "COS"     "PHX"      551         5         18           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
    1996      1          12            5         1252         1245        1511         1500           "HP"            610       "N905AW"            79                 75            58          11           7       "LAX"     "PHX"      370         3         18           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
    1996      1          16            2         1441         1445        1708         1721           "HP"            211       "N165AW"            87                 96            74         -13          -4       "RNO"     "PHX"      601         4          9           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
    1996      1           1            1         2258         2300        2336         2335           "HP"           1245       "N183AW"            38                 35            20           1          -2       "TUS"     "PHX"      110         6         12           0              NaN              0            NaN             NaN          NaN           NaN                NaN       
    1996      1           4            4         1814         1814        1901         1910           "US"            683       "N963VJ"            47                 56            34          -9           0       "DTW"     "PIT"      201         6          7           0              NaN              0            NaN             NaN          NaN           NaN                NaN       

Close the database connection.

close(conn);

Import Data from Database Table in SQLite Database File

Import airline data from a table in an SQLite database file into MATLAB®.

First, create an SQLite connection to the file sample_dataset.db that contains the table airlinesmall. The SQLite connection conn is an sqlite object.

dbfile = fullfile(matlabroot,"toolbox","database","database","sample_dataset.db");
conn = sqlite(dbfile);

Import the first ten rows of data from airlinesmall. The output contains the imported data as a table.

sqlquery = 'SELECT * FROM airlinesmall LIMIT 10';
results = fetch(conn,sqlquery)

results=10×29 table
    1996    1    18    4    2117    2120    2305    2259    "HP"     415    "N637AW"    108     99     "85"      6    -3    "COS"    "PHX"     551    "5"    "18"    0    "NA"    0    "NA"    "NA"    "NA"    "NA"    "NA"
    1996    1    25    4    1712       0    1908       0    "AA"    1733    "N283AA"    176    151    "138"     29     4    "DFW"    "PHX"     868    "6"    "32"    0    "NA"    0    "NA"    "NA"    "NA"    "NA"    "NA"
    1996    1    26    5     755     755     938     940    "WN"     708      "N675"    163    165    "151"     -2     0    "HOU"    "PHX"    1020    "3"     "9"    0    "NA"    0    "NA"    "NA"    "NA"    "NA"    "NA"
    1996    1    17    3     732     731     934     930    "HP"     546    "N622AW"     62     59     "39"      4     1    "LAS"    "PHX"     256    "5"    "18"    0    "NA"    0    "NA"    "NA"    "NA"    "NA"    "NA"
    1996    1    12    5    1252    1245    1511    1500    "HP"     610    "N905AW"     79     75     "58"     11     7    "LAX"    "PHX"     370    "3"    "18"    0    "NA"    0    "NA"    "NA"    "NA"    "NA"    "NA"
    1996    1    23    2    2040    2000    2245    2210    "WN"    1306      "N334"     65     70     "51"     35    40    "LAX"    "PHX"     370    "4"    "10"    0    "NA"    0    "NA"    "NA"    "NA"    "NA"    "NA"
    1996    1    24    3     913     915     NaN    1135    "NW"    1815    "N308US"    NaN    200     "NA"    NaN    -2    "MSP"    "PHX"    1276    "0"    "15"    0    "NA"    1    "NA"    "NA"    "NA"    "NA"    "NA"
    1996    1    27    6     855     855    1056    1100    "WN"     822      "N612"     61     65     "47"     -4     0    "ONT"    "PHX"     325    "2"    "12"    0    "NA"    0    "NA"    "NA"    "NA"    "NA"    "NA"
    1996    1    16    2    1441    1445    1708    1721    "HP"     211    "N165AW"     87     96     "74"    -13    -4    "RNO"    "PHX"     601    "4"     "9"    0    "NA"    0    "NA"    "NA"    "NA"    "NA"    "NA"
    1996    1    30    2    1344    1344    1730    1724    "AS"      98    "N778AS"    166    160    "146"      6     0    "SEA"    "PHX"    1107    "2"    "18"    0    "NA"    0    "NA"    "NA"    "NA"    "NA"    "NA"

Display the names of the unique airline carriers.

unique(results.UniqueCarrier)

ans = 5×1 string array
    "AA"
    "AS"
    "HP"
    "NW"
    "WN"

Use a row filter to display the entries where DayOfWeek is 3.

rf = rowfilter("DayOfWeek");
rf = rf.DayOfWeek == 3;
results = fetch(conn,sqlquery,"RowFilter",rf)

results=2×29 table
    1996    1    17    3    732    731    934     930    "HP"     546    "N622AW"     62     59    "39"      4     1    "LAS"    "PHX"     256    "5"    "18"    0    "NA"    0    "NA"    "NA"    "NA"    "NA"    "NA"
    1996    1    24    3    913    915    NaN    1135    "NW"    1815    "N308US"    NaN    200    "NA"    NaN    -2    "MSP"    "PHX"    1276    "0"    "15"    0    "NA"    1    "NA"    "NA"    "NA"    "NA"    "NA"

Close the SQLite connection.

close(conn)

Import All Data Using connection Object

Import all product data from a Microsoft® SQL Server® database table into MATLAB® by using the connection object. Determine the highest unit cost among products in the table. Then, use a row filter to import only the data for products with a unit cost less than 15.

Create an ODBC database connection to a Microsoft® SQL Server® database with Windows® authentication. Specify a blank username 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 =

  15×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.0034e+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'     }
         11          4.0814e+05          1004            11       {'Convertible'    }
         12          2.1046e+05          1010            22       {'Hugsy'          }
         13          4.7082e+05          1012            17       {'Pancakes'       }
         14           5.101e+05          1011            19       {'Shawl'          }
         15          8.9975e+05          1011            20       {'Snacks'         }

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

max(results.unitCost)

ans =

    24

Now, import the data using a row filter. The filter condition is that unitCost must be less than 15.

rf = rowfilter("unitcost");
rf = rf.unitcost < 15;
results = fetch(conn,sqlquery,"RowFilter",rf)

results =

  7×5 table

    productNumber    stockNumber    supplierNumber    unitCost    productDescription 
    _____________    ___________    ______________    ________    ___________________

          9          1.2597e+05          1003            13       {'Victorian Doll' }
          8          2.1257e+05          1001             5       {'Train Set'      }
          2          4.0031e+05          1002             9       {'Painting Set'   }
          1          4.0034e+05          1001            14       {'Building Blocks'}
          5          4.0046e+05          1005             3       {'Tin Soldier'    }
          6          4.0088e+05          1004             8       {'Sail Boat'      }
         11          4.0814e+05          1004            11       {'Convertible'    }

Close the database connection.

close(conn)

Import Data Using SQL Prepared Statement

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
    1    1700    14.5000    '2014-09-23 09:38:34'
    2    1200     9.0000    '2014-07-08 22:50:45'
    3     356    17.0000    '2014-05-14 07:14:28'
    7    6034    16.0000    '2014-08-06 08:38:00'

Close the SQL prepared statement and database connection.

close(pstmt)
close(conn)

Retrieve Metadata Information About Imported Data

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 array
    "'Region'"
    "'OutageTime'"
    "'Loss'"
    "'Customers'"
    "'RestorationTime'"
    "'Cause'"

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

metadata.VariableType

ans = 6×1 cell array
    "'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
     ''
     ''
    NaN
    NaN
     ''
     ''

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

metadata.MissingRows

ans=6×1 cell array
             [ ]
             [ ]
    604×1 double
    328×1 double
     29×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
    'SouthEast'    '2003-01-23 00:49:00.000'      530.1399    2.1204e+05    ''        'winter storm'
    'NorthEast'    '2004-09-18 05:54:00.000'             0             0    ''     'equipment fault'
      'MidWest'    '2002-04-20 16:46:00.000'    2.3141e+04           NaN    ''             'unknown'
    'NorthEast'    '2004-09-16 19:42:00.000'    4.7180e+03           NaN    ''             'unknown'
    'SouthEast'    '2005-09-14 15:45:00.000'    1.8392e+03    3.4144e+05    ''        'severe storm'
    'SouthEast'    '2004-08-17 17:34:00.000'      624.0955    1.7879e+05    ''        'severe storm'
    'SouthEast'    '2006-01-28 23:13:00.000'      498.7777           NaN    ''    'energy emergency'
         'West'    '2003-06-20 18:22:00.000'             0             0    ''    '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)

Import Data from SQL Query Using Import Options

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 a database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank username 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 = strcat("SELECT * from employees e join departments d ", ...
    "on (e.department_id = d.department_id) WHERE ", ...
    "(job_id = 'IT_PROG' or job_id = '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 }
                   RowFilter: <unconstrained> 

             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"];

Set the filter condition to import only the data for the employees hired before January 1, 2006.

opts.RowFilter = opts.RowFilter.hire_date < datetime(2006,01,01)

opts = 
  SQLImportOptions with properties:

           ExcludeDuplicates: false
          VariableNamingRule: 'modify'

               VariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more}
               VariableTypes: {'double', 'string', 'char' ... and 13 more}
       SelectedVariableNames: {'first_name', 'hire_date', 'department_name'}
                  FillValues: { NaN,  <missing>, ''  ... and 13 more }
                   RowFilter: hire_date < 01-Jan-2006 

             VariableOptions: Show all 16 VariableOptions

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

employees_data = fetch(conn,sqlquery,opts)

employees_data=4×3 table
    "David"    25-Jun-2005    IT
    "John"    01-Oct-2004    Sales
    "Karen"    05-Jan-2005    Sales
    "Alberto"    10-Mar-2005    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)

Input Arguments

conn — Database connection

MySQLconnection object | PostgreSQL connection object | DuckDB connection object | SQLite connection object | ODBC connection object | JDBC connection object

Database connection, specified as any of the following:

• MySQL connection object created by using the mysql function.

• PostgreSQL connection object created by using the postgresql function.

• DuckDB connection object created by using the duckdb function.

• SQLite connection object created by using the sqlite function.

• ODBC connection object created by using the database function.

• JDBC connection object created by using the database function.

sqlquery — SQL query

string scalar | character vector

SQL query, specified as a string scalar or character vector. sqlquery can be a nested query or as a stored procedure.

For information about the SQL query language, see the SQL Tutorial on the W3Schools website.

Example: {CALL getSupplierInfo('New York")}

Data Types: string | char

opts — Database import options

SQLImportOptions object

Database import options, specified as an SQLImportOptions object.

pstmt — SQL prepared statement

SQLPreparedStatement object

SQL prepared statement, specified as an SQLPreparedStatement object. A prepared statement is a precompiled SQL command that can include parameter placeholders. Use this object when you need to execute parameterized queries securely or run the same SQL statement multiple times with different values.

Name-Value Arguments

Specify optional pairs of arguments as Name1=Value1,...,NameN=ValueN, where Name is the argument name and Value is the corresponding value. Name-value arguments must appear after other arguments, but the order of the pairs does not matter.

Before R2021a, use commas to separate each name and value, and enclose Name in quotes.

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

MaxRows — Maximum number of rows

positive numeric scalar

Maximum number of rows, specified as a positive numeric scalar. By default, the fetch function returns all rows from the executed SQL query. Use this argument to limit the number of rows.

Example: MaxRows=10

Data Types: double

DataReturnFormat — Data return format

"table" (default) | "cellarray" | "numeric" | "structure"

Data return format, specified as one of the following:

• "table"

• "cellarray"

• "numeric"

• "structure"

Use DataReturnFormat to specify the data type of results. To specify integer classes for numeric data, use the opts input argument.

Example: DataReturnFormat="cellarray"

Note

This argument is not valid when conn is an SQLite connection object.

VariableNamingRule — Variable naming rule

"modify" (default) | "preserve"

Variable naming rule, specified as one of the following:

• "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.

Example: VariableNamingRule="modify"

RowFilter — Row filter condition

<unconstrained> (default) | matlab.io.RowFilter object

Row filter condition, specified as a matlab.io.RowFilter object.

Example: rf = rowfilter("productnumber"); rf = rf.productnumber <= 5; fetch(conn,sqlquery,RowFilter=rf)

MaxTextLength — Maximum text length

0 (default) | positive numeric scalar

Maximum text length in bytes, specified as a positive numeric scalar. This parameter sets an upper bound on the size of TEXT data when reading from database tables. Increase the value to prevent truncation from certain database servers, or, decrease it to reduce memory usage improve performance for small text fields. Set MaxTextLength to 0 to impose no limit.

You can adjust this parameter together with BatchSize. If MaxTextLength is set to a high value, reduce BatchSize to prevent out-of-memory errors.

Example: MaxTextLength=2e9

Note

MaxTextLength applies only to ODBC connection objects.

Data Types: double

BatchSize — Batch size

100000 (default)

Batch size, specified as positive numeric scalar. This parameter sets the number of rows in each batch of TEXT data read by fetch.

Adjust BatchSize together with the MaxTextLength. If MaxTextLength is set to a high value, reduce BatchSize to prevent out-of-memory errors.

Example: BatchSize=50000

Note

BatchSize applies to only ODBC connection objects.

Data Types: double

Output Arguments

results — Result data

table (default) | cell array | structure | numeric matrix

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

When the executed SQL statement does not return any rows, results is an empty table. The following table lists how each database data type maps to a MATLAB data type.

Database Data Type	MySQL	PostgreSQL	DuckDB	SQLite	JDBC/ODBC
BOOLEAN	N/A	logical	logical	int64	logical
TINYINT	double	N/A	double	double	double
SMALLINT		double
INTEGER	N/A			N/A
INT		N/A	N/A	double
BIGINT	double	double	double
UTINYINT	N/A	N/A		N/A
USMALLINT
UINTEGER
UBIGINT
FLOAT				double
DOUBLE	double	double
HUGEINT	N/A	N/A		N/A
UHUGEINT
NUMERIC	double	double	N/A	double
SMALLSERIAL	N/A			N/A
SERIAL
BIGSERIAL
REAL	double			double
MONEY	N/A			N/A
MEDIUMINT		N/A		double
YEAR	double			N/A
TIMESTAMP	datetime	datetime	datetime		cell array or character vectors
TIMESTAMP_S	N/A	N/A
TIMESTAMP_MS
TIMESTAMP_NS
TIMESTAMPZ		datetime	N/A
ABSTIME
DATE	datetime		datetime (12AM on specified date)	string
DATETIME	N/A	N/A	N/A
DECIMAL	double	double	double	N/A	double
VARCHAR	string	string		string	cell array or character vectors
BIT	logical	N/A	N/A	N/A	logical
BYTEA	N/A	string			cell array or character vectors
UUID			string
CIDR			N/A
INET
MACADDR
XML
TIME	duration	duration	duration
TIMEZ	N/A		N/A
TIME_TZ		N/A	duration
INTERVAL		calendarDuration	calendarDuration
HUGEINT		N/A	double		double
UHUGEINT
BLOB		cell array of uint8 vectors	cell array of uint8 vectors	Nx1 uint8 vector	cell array or character vectors
ENUM		categorical (categories match values of ENUM type)	categorical (categories match values of ENUM type)	N/A
CHAR	string	string	N/A	string
TEXT	N/A			N/A
RELTIME		calendarDuration
JSON	char	N/A
LONGVARCHAR	string
LIST	N/A		Cell array where each cell contains a vector of the corresponding type. For example, DOUBLE[] maps to a cell array of double vectors.
STRUCT			Each field maps to its corresponding DuckDB type. For example, STRUCT(name VARCHAR, age INTEGER) maps to a MATLAB struct, where name fields are strings and age fields are int32.
MAP			Cell array of dictionaries. Key and value types match the DuckDB types. For example, MAP(DOUBLE, VARCHAR) maps to a cell array of dictionaries with double keys and string values.
ARRAY			Cell array with each cell containing a vector of the corresponding type. For example, DOUBLE[3] maps to a cell array of double vectors.
UNION			Cell array where each cell can contain a value matching any DuckDB type defined in the UNION. For example, UNION(t TIMESTAMP, d DOUBLE), maps to a cell array where each element is either a datetime or a double.

metadata — Metadata information

table

Metadata information, returned as a table with these variables.

Variable Name	Variable Description	Variable 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, 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 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 argument VariableNamingRule has these limitations:

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

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

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

• If you set the VariableNamingRule name-value argument 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.

The name-value argument RowFilter has this limitation:

• The fetch function returns an error if you specify the RowFilter name-value argument with the SQLImportOptions object opts. It is ambiguous which of the RowFilter object to use in this case, especially if the filter conditions are different.

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

Version History

Introduced in R2006b

R2026a: Import Data from DuckDB Database

You can now use the fetch function to import data from a DuckDB database by specifying a DuckDB connection object.

R2023a: Selectively import rows of data based on filter condition

You can use the RowFilter name-value argument to selectively import rows of data from a database table.

R2018b: fetch function returns results as a table

The fetch function returns results as a table instead of a cell array, by default. In prior releases, when fetch found no data to import, it returned a cell array containing the character vector 'No Data'. Now, when the function finds no data to import, it returns an empty table.

R2018b: fetch function ignores 'DataReturnFormat', 'NullNumberRead', and 'NullStringRead' database preferences

The fetch function ignores these database preferences:

• 'DataReturnFormat'

• 'NullNumberRead'

• 'NullStringRead'

You can set the data type of the imported data by using the 'DataReturnFormat' name-value argument of the fetch function. For more customization of data types and fill values for missing data in the imported data, use the SQLImportOptions object.