executeSQLScript

Execute SQL script on database

Syntax

results = executeSQLScript(conn,scriptfile)

results = executeSQLScript(conn,scriptfile,Name,Value)

Description

results = executeSQLScript(conn,scriptfile) uses the database connection conn to return a structure array that contains results as a table (by default) for each executed SQL SELECT statement in the SQL script file. For any non-SELECT SQL statements, the corresponding table is empty. The executeSQLScript function executes all SQL statements in the SQL script file.

results = executeSQLScript(conn,scriptfile,Name,Value) specifies additional options using one or more name-value pair arguments. For example, 'DataReturnFormat','cellarray' stores the results of an executed SQL statement as a cell array. The results are stored in the Data field of the structure array.

Examples

Execute SQL Script

Connect to a Microsoft® SQL Server® database. Then, run two SQL SELECT statements from the SQL script file compare_sales.sql, import the results, and perform simple sales data analysis. The file contains two SQL queries in order. The first SQL query retrieves sales of products from US suppliers and the second SQL query retrieves sales of products from foreign suppliers.

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,'','');

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

conn.Message

ans =

     []

Execute the SQL script. The SQL script has two SQL queries that retrieve sales data from US and foreign suppliers, respectively.

scriptfile = 'compare_sales.sql';
results = executeSQLScript(conn,scriptfile)

results = 1×2 struct array with fields:
    SQLQuery
    Data
    Message

The executeSQLScript function returns a structure array that contains two tables in the Data field. The first table contains the results of executing the first SQL query in the SQL script file. The second table contains the results of executing the second SQL query.

Display the first eight rows of imported data for the second SQL query in the SQL script file. The data shows sales results from foreign suppliers.

data = head(results(2).Data)

data=8×6 table
    productDescription             supplierName                city       Jan_Sales    Feb_Sales    Mar_Sales
    __________________    ______________________________    __________    _________    _________    _________

     'Victorian Doll'     'Wacky Widgets'                   'Adelaide'      1400         1100          981   
     'Painting Set'       'Terrific Toys'                   'London'        3000         2400         1800   
     'Sail Boat'          'Incredible Machines'             'Dublin'        3000         2400         1500   
     'Slinky'             'Doll's Galore'                   'London'        3000         1500         1000   
     'Convertible'        'Incredible Machines'             'Dublin'        6000         3100         8800   
     'Hugsy'              'The Great Teddy Bear Company'    'Belfast'       1800         9700          800   
     'Pancakes'           'Aunt Jemimas'                    'New York'      3100         9400         1540   
     'Shawl'              'Indian Export'                   'Mumbai'         235         1800         1040   

Retrieve the variable names in the table.

names = data.Properties.VariableNames

names = 1×6 cell array
    {'productDescription'}    {'supplierName'}    {'city'}    {'Jan_Sales'}    {'Feb_Sales'}    {'Mar_Sales'}

Determine the highest sales amount in January.

max(data.Jan_Sales)

ans = 6000

Close the database connection.

close(conn)

Execute SQL Script and Return Results as Structures

Connect to a Microsoft® SQL Server® database. Then, run two SQL SELECT statements from the SQL script file compare_sales.sql. Import the results from the SQL queries as structures and perform simple sales data analysis. The file contains two SQL queries in order. The first SQL query retrieves sales of products from US suppliers and the second SQL query retrieves sales of products from foreign suppliers.

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,'','');

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

conn.Message

ans =

     []

Execute the SQL script. The SQL script has two SQL queries that retrieve sales data from US and foreign suppliers, respectively. Specify structure as the data return format for importing the query results.

scriptfile = 'compare_sales.sql';
results = executeSQLScript(conn,scriptfile, ...
    'DataReturnFormat','structure')

results = 1×2 struct array with fields:
    SQLQuery
    Data
    Message

The executeSQLScript function returns a structure array that contains two structures in the Data field. The first structure contains the results of executing the first SQL query in the SQL script file. The second structure contains the results of executing the second SQL query.

Display the imported data for the second SQL query in the SQL script file. The data contains sales results from foreign suppliers.

data = results(2).Data

data = struct with fields:
    productDescription: {9×1 cell}
          supplierName: {9×1 cell}
                  city: {9×1 cell}
             Jan_Sales: [9×1 double]
             Feb_Sales: [9×1 double]
             Mar_Sales: [9×1 double]

Determine the highest sales amount in January.

max(data.Jan_Sales)

ans = 6000

Close the database connection.

close(conn)

Input Arguments

conn — Database connection

connection object

Database connection, specified as an ODBC connection object or JDBC connection object created using the database function.

scriptfile — Name of SQL script file

character vector | string scalar

Name of SQL script file that contains one or more SQL statements to run, specified as a character vector or string scalar. The file must be a text file and can contain comments in addition to SQL queries. Start single-line comments with --. Enclose multiline comments in /*...*/.

The SQL script file can contain one or more SQL statements terminated by either a semicolon or the keyword GO. The following is an example of two SQL SELECT statements.

SELECT productDescription, supplierName
FROM suppliers A, productTable B
WHERE A.SupplierNumber = B.SupplierNumber;

SELECT supplierName, Country
FROM suppliers;

Example: 'C:\work\sql_file.sql'

Data Types: char | string

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 = executeSQLScript(conn,scriptfile,'DataReturnFormat','numeric','ErrorHandling','store') returns query results as a numeric matrix in the Data field of the results structure array and stores any error message in the Message field of results.

DataReturnFormat — Data return format

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

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

• 'table'

• 'cellarray'

• 'numeric'

• 'structure'

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

The 'DataReturnFormat' name-value pair argument specifies the data type of the Data field in the results structure array.

Example: 'DataReturnFormat','structure' returns a structure array that contains query results stored in structures.

ErrorHandling — Error handling

'report' (default) | 'store'

Error handling, specified as the comma-separated pair consisting of 'ErrorHandling' and one of these values:

• 'report' — When an SQL statement fails to execute, stop execution of the remaining SQL statements in the SQL script file and display an error message at the command line.

• 'store' — When an SQL statement fails to execute, store an error message in the Message field of the results structure array.

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

Example: 'ErrorHandling','report' displays an error message at the command line.

Output Arguments

results — Query results

structure array

Query results from executed SQL statements in the SQL script file, returned as a structure array with these fields.

Field Name	Field Data Type	Field Description
SQLQuery	character vector	Stores the SQL statement or statements executed in the SQL script file.
Data	table (default) cell array numeric matrix structure	Stores the results of executed SQL SELECT statements. The 'DataReturnFormat' name-value pair argument specifies the data type of the Data field. For non-SELECT SQL statements, the Data field is an empty double, which means the executed SQL query has no results.
Message	character vector	Stores an error message for the respective SQL statement that fails to execute. The Message field contains an error message only if you specify the 'ErrorHandling' name-value pair argument with the value 'store'.

The number of elements in the structure array is equal to the number of SQL statements in the SQL script file. results(M) contains the results from executing the Mth SQL statement in the SQL script file. If the SQL statement returns query results, then the results are stored in results(M).Data.

For details about accessing structure arrays, see Structure Arrays.

Limitations

• Use the executeSQLScript function to import data into MATLAB^®, especially if you have long and complex SQL queries that are difficult to convert into MATLAB character vectors or string scalars. The executeSQLScript function does not support SQL scripts containing continuous PL/SQL blocks with BEGIN and END, such as stored procedure definitions or trigger definitions. However, executeSQLScript does support table definitions.

• An SQL script containing either of the following can produce unexpected results:

  • Apostrophes that are not escaped, including those in comments. For example, write the character vector 'Here's the code' as 'Here''s the code'.

  • Nested comments.

• An SQL script containing more than 25,000 characters causes the executeSQLScript function to return an error.

Version History

Introduced in R2019a