Documentation Center

  • Trials
  • Product Updates

runsqlscript

Run SQL script on database

Syntax

  • results = runsqlscript(connect,sqlfilename) example
  • results = runsqlscript(connect,sqlfilename,Name,Value) example

Description

example

results = runsqlscript(connect,sqlfilename) runs the SQL commands in the file sqlfilename on the connected database, and returns a cursor array.

example

results = runsqlscript(connect,sqlfilename,Name,Value) uses additional options specified by one or more Name,Value pairs.

Examples

expand all

Run SQL Script

Run SQL commands from a file on a connected data source.

To get the file of SQL commands, navigate to \toolbox\database\dbdemos\compare_sales.sql in your MATLAB® root folder, or copy and paste the path into your current working directory.

Create the connection object to the data source, dbtoolboxdemo.

conn = database('dbtoolboxdemo','','');

User names and passwords are not required for this connection.

Run the SQL script, compare_sales.sql.

results = runsqlscript(conn,'compare_sales.sql')
results =
 
1x2 array of cursor objects

The SQL script has two queries, and returns two results when executed.

Display the results for the second query.

results(2)
ans =
 
        Attributes: []
              Data: {4x6 cell}
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: [1x309 char]
           Message: ''
              Type: 'Database Cursor Object'
         ResultSet: [1x1 sun.jdbc.odbc.JdbcOdbcResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 sun.jdbc.odbc.JdbcOdbcStatement]
             Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

Display the resultset returned for the second query.

results(2).Data
ans = 

    'Painting Set'    'Terrific Toys'        'London'    [3000]  [2400]  [1800]
    'Victorian Doll'  'Wacky Widgets'        'Adelaide'  [1400]  [1100]  [ 981]
    'Sail Boat'       'Incredible Machines'  'Dublin'    [3000]  [2400]  [1500]
    'Slinky'          'Doll's Galore'        'London'    [3000]  [1500]  [1000]

Get the column names for the data returned by the second query.

names = columnnames(results(2))
names =

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

Close the cursor array and connection.

close(results);
close(conn);

Run SQL Script in Row Increments

Run SQL commands from a file on a connected data source in two-row increments.

To get the file of SQL commands, navigate to \toolbox\database\dbdemos\compare_sales.sql in your MATLAB root folder, or copy and paste the path into your current working directory.

Create the connection object to the data source, dbtoolboxdemo.

conn = database('dbtoolboxdemo','','');

User names and passwords are not required for this connection.

Alternatively, you can use the native ODBC interface for an ODBC connection. For details, see database.

Run the SQL script, compare_sales.sql, specifying two-row increments.

results = runsqlscript(conn,'compare_sales.sql','rowInc',2)
results =
 
1x2 array of cursor objects

The SQL script has two queries, and returns two results when executed.

Display the resultset returned for the second query.

results(2).Data
ans = 

    'Painting Set'      'Terrific Toys'    'London'      [3000]    [2400]    [1800]
    'Victorian Doll'    'Wacky Widgets'    'Adelaide'    [1400]    [1100]    [ 981]

Only the first two rows of the results are returned.

Fetch the next increment of two rows.

res2 = fetch(results(2),2);
res2.Data
ans = 

    'Sail Boat'    'Incredible Machines'    'Dublin'    [3000]    [2400]    [1500]
    'Slinky'       'Doll's Galore'          'London'    [3000]    [1500]    [1000]

Close the cursor arrays and connection.

close(results);
close(res2);
close(conn);

Run SQL Script to Fetch Data in Batches

Run SQL commands from a file on a connected data source with automated batching. Use this method to avoid Java® heap memory issues when the SQL script returns a large amount of data.

To get the file of SQL commands, navigate to \toolbox\database\dbdemos\compare_sales.sql in your MATLAB root folder, or copy and paste the path into your current working directory.

Create the connection object to the data source, dbtoolboxdemo.

conn = database('dbtoolboxdemo','','');

Alternatively, you can use the native ODBC interface for an ODBC connection. For details, see database.

Turn on batching for fetch.

setdbprefs('FetchInBatches', 'yes')

Set appropriate batch size depending on the size of the resultset you expect to fetch. For example, if you expect about a 100,000 rows in the output, a batch size of 10,000 is a good starting point. The larger the FetchBatchSize value, the fewer trips between Java and MATLAB, and the memory consumption is greater for each batch. The optimal value for FetchBatchSize is decided based on several factors like the size per row being retrieved, the Java heap memory value, the driver's default fetch size, and system architecture, and hence, may vary from site to site. For details about estimating a value for FetchBatchSize, see Preference Settings for Large Data Import.

setdbprefs('FetchBatchSize', '2') 

Run the SQL script, compare_sales.sql.

results = runsqlscript(conn, 'compare_sales.sql')
results =
 
1x2 array of cursor objects

Batching occurs internally within fetch, in that it fetches in increments of two rows at a time. The batching preferences are applied to all the queries in the SQL script.

Input Arguments

expand all

connect — Database connectionconnection object

Database connection, specified as a connection object.

sqlfilename — File name of SQL commandsstring

File name of SQL commands to run, specified as a string. The file must be a text file, and can contain comments along with SQL queries. Single line comments must start with --. Multiline comments should be wrapped in /*...*/.

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

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 single quotes (' '). You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN.

Example: 'RowInc',3,'QTimeOut',60 specifies that results be returned in increments of three rows and the query time out in 60 seconds

'rowInc' — Row limit0 implies all rows (default) | positive 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 scalar value. Use rowInc when importing large amounts of data. Retrieving data in increments helps reduce overall retrieval time.

Example: 'rowInc',5

Data Types: double

'QTimeOut' — Query time out0 implies unlimited time (default) | positive scalar

Query time out (in seconds), specified as the comma-separated pair consisting of 'QTimeOut' and a positive scalar value.

Example: 'QTimeOut',180

Data Types: double

Output Arguments

expand all

results — Query resultscursor array

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

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

Limitations

  • Use runsqlscript to import data into MATLAB, especially if the data is the result of long and complex SQL queries that are difficult to convert into MATLAB strings. runsqlscript is not designed to handle SQL scripts containing continuous PL/SQL blocks with BEGIN and END, such as stored procedure definitions, trigger definitions, and so on. However, table definitions do work.

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

    • Apostrophes that are not escaped (including those in comments). For example, the string 'Here's the code' should be written as 'Here''s the code'.

    • Nested comments.

More About

expand all

Batch

One or more SQL statements terminated by either a semicolon or the keyword GO.

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

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

See Also

| |

Was this topic helpful?