runsqlscript

(To be removed) Run SQL script on database

The runsqlscript function will be removed in a future release. Use the executeSQLScript function instead. For details, see Compatibility Considerations.

Syntax

results = runsqlscript(conn,scriptfile)

results = runsqlscript(___,Name,Value)

Description

results = runsqlscript(conn,scriptfile) returns a cursor object array that contains a cursor object for each executed SQL command in the SQL script file scriptfile using the database connection. The runsqlscript function executes all SQL commands in the SQL script file.

results = runsqlscript(___,Name,Value) specifies additional options using one or more name-value pair arguments. For example, 'RowInc',5 returns results from the executed SQL statements in the SQL script file in increments of five rows at a time.

Examples

Run SQL Script

First, connect to the Microsoft® SQL Server® database. Then, run two SQL SELECT statements from a SQL script file. Perform simple sales data analysis. Close the database connection.

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

     []

Run the SQL script. The SQL script has two queries. When the SQL script executes, it returns two cursor objects that contain the imported data from each query in a cursor object array.

scriptfile = 'compare_sales.sql';

results = runsqlscript(conn,scriptfile)

results = 

  1×2 cursor array with properties:

    Data
    RowLimit
    SQLQuery
    Message
    Type
    Statement
    Position

Display the cursor object for the second query.

results(2)

ans = 

  cursor with properties:

         Data: [9×6 table]
     RowLimit: 0
     SQLQuery: 'select      productDescription, supplierName, city, January as Jan_Sales, February as Feb_Sales, March as Mar_Sales from 		suppliers A, 			productTable B, 			salesVolume C where		A.Country	not like		'United States' AND         A.SupplierNumber	= 		B.SupplierNumber AND         B.stocknumber		= 		C.stockNumber'
      Message: []
         Type: 'ODBCCursor Object'
    Statement: [1×1 database.internal.ODBCStatementHandle]

Display the imported data for the second query.

data = results(2).Data

data =

  9×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     
    'Snacks'              'Indian Export'                   'Mumbai'       123         1700          823     

Retrieve the column names for the second query.

names = columnnames(results(2))

names =

    ''productDescription','supplierName','city','Jan_Sales','Feb_Sales','Mar_Sales''

Determine the highest sales amount in January.

max(data.Jan_Sales)

ans =

        6000

Close the cursor object array and database connection.

close(results)
close(conn)

Run SQL Script in Row Increments

First, connect to the Microsoft® SQL Server® database. Then, run two SQL SELECT statements from a SQL script file. Import data in one-row increments. Perform simple sales data analysis. Close the database connection.

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

     []

Run the SQL script and specify one-row increments. The SQL script has two queries. When the SQL script executes, it returns two cursor objects that contain the imported data from each query in a cursor object array.

results = runsqlscript(conn,'compare_sales.sql','RowInc',1)

results = 

  1×2 cursor array with properties:

    Data
    RowLimit
    SQLQuery
    Message
    Type
    Statement
    Position

Display the imported data for the second query.

results(2).Data

ans =

  1×6 table

    productDescription     supplierName         city       Jan_Sales    Feb_Sales    Mar_Sales
    __________________    _______________    __________    _________    _________    _________

    'Victorian Doll'      'Wacky Widgets'    'Adelaide'    1400         1100         981      

Because of the one-row increment specification, only the first row of data is displayed.

Import the next row of data using the fetch function and display it.

curs = fetch(results(2),1);
curs.Data

ans =

  1×6 table

    productDescription     supplierName        city      Jan_Sales    Feb_Sales    Mar_Sales
    __________________    _______________    ________    _________    _________    _________

    'Painting Set'        'Terrific Toys'    'London'    3000         2400         1800     

Determine the highest sales amount among the months of January, February, and March.

data = curs.Data;
max([data.Jan_Sales data.Feb_Sales data.Mar_Sales])

ans =

        3000

Close the cursor object array, cursor object, and database connection.

close(results)
close(curs)
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 — SQL script file name

character vector | string scalar

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

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 = runsqlscript(conn,scriptfile,'RowInc',3,'QTimeOut',60);

'RowInc' — Row limit

0 (default) | numeric scalar

Row limit indicating the number of rows to retrieve at a time, specified as the comma-separated pair consisting of 'RowInc' and a positive numeric scalar. Use this name-value pair argument when importing large amounts of data. Importing data in increments helps reduce overall retrieval time.

By default, the runsqlscript function imports all rows of data from the executed SQL statements. The value 0 specifies to import all rows of data.

Example: 'RowInc',5

Data Types: double

'QTimeOut' — Query timeout

0 (default) | numeric scalar

Query timeout in seconds, specified as the comma-separated pair consisting of 'QTimeOut' and a positive numeric scalar. By default, the runsqlscript function waits an unlimited number of seconds to execute SQL statements in the SQL script file. The value 0 specifies to wait an unlimited amount of time.

Example: 'QTimeOut',180

Data Types: double

Output Arguments

results — Query results

cursor object array

Query results from executing the SQL commands in the SQL script file, returned as a cursor object array. The number of elements in results is equal to the number of batches in the file scriptfile.

results(M) contains the results from executing the Mth batch in the SQL script. If the batch returns a result set, then it is stored in results(M).Data.

Limitations

• Use runsqlscript 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. runsqlscript is not designed to handle SQL scripts containing continuous PL/SQL blocks with BEGIN and END, such as stored procedure definitions or trigger definitions. However, table definitions do work.

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

  • Apostrophes that are not escaped, including the ones 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 runsqlscript to return an error.

More About

Batch

One or more SQL statements terminated by either a semicolon or the keyword GO; for example:

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

SELECT supplierName, Country
FROM suppliers;

Tips

• Any values assigned to RowInc or QTimeOut apply to all queries in the SQL script. For example, if 'RowInc' is set to 5, then all queries in the script return at most five rows in their respective query results.

• You can set preferences for the query results using the setdbprefs function. Preference settings apply to all queries in the SQL script. For example, if the 'DataReturnFormat' is set to numeric, all query results return as numeric matrices.

Compatibility Considerations

runsqlscript function will be removed

Not recommended starting in R2019a

The runsqlscript function will be removed in a future release. Use the executeSQLScript function instead. Some differences between the workflows might require updates to your code.

Update Code

In prior releases, the output argument of the runsqlscript function was a cursor array. For example:

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');
scriptfile = 'compare_sales.sql';
results = runsqlscript(conn,scriptfile)

results = 

  1×2 cursor array with properties:

    Data
    RowLimit
    SQLQuery
    Message
    Type
    Statement
    Position

Now the executeSQLScript function returns a structure array.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');
scriptfile = 'compare_sales.sql';
results = executeSQLScript(conn,scriptfile)

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

You can also change the data return format of the results in the structure array by using the 'DataReturnFormat' name-value pair argument. The executeSQLScript function ignores database preferences set by the setdbprefs function.