executeSQLScript

Execute SQL script on database

Description

example

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.

example

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

collapse all

Connect to a Microsoft® SQL Server® database. Then, run two SQL SELECT statements from a SQL script file, 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.

To find the SQL script file, navigate to \toolbox\database\dbdemos\compare_sales.sql in your MATLAB® root folder. Copy and paste the path into your current folder.

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)

Connect to a Microsoft® SQL Server® database. Then, run two SQL SELECT statements from a SQL script file. 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.

To find the SQL script file, navigate to \toolbox\database\dbdemos\compare_sales.sql in your MATLAB® root folder. Copy and paste the path into your current folder.

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

collapse all

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

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

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.

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

collapse all

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

Field NameField Data TypeField 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 Access Data in Structure Array (MATLAB).

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.

Introduced in R2019a