executeSQLScript

Execute SQL script on PostgreSQL database

Since R2020b

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 results structure array.

Examples

Execute SQL Script Using PostgreSQL Native Interface

Connect to a PostgreSQL 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: the first retrieves sales of products from US suppliers, and the second retrieves sales of products from foreign suppliers.

Create a PostgreSQL native interface database connection to a PostgreSQL database using the data source name, user name, and password. The database contains the tables productTable and suppliers.

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);

Execute the SQL script. The SQL script contains 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=4×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   

Retrieve the variable names in the table.

names = data.Properties.VariableNames

names = 1×6 cell
    {'productdescription'}    {'suppliername'}    {'city'}    {'jan_sales'}    {'feb_sales'}    {'mar_sales'}

Determine the highest sales amount in January.

max(data.jan_sales)

ans = 3000

Close the database connection.

close(conn)

Execute SQL Script and Return Results as Structures

Connect to a PostgreSQL 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: the first retrieves sales of products from US suppliers, and the second retrieves sales of products from foreign suppliers.

Create a PostgreSQL native interface database connection to a PostgreSQL database using the data source name, user name, and password. The database contains the tables productTable and suppliers.

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);

Execute the SQL script. The SQL script contains two SQL queries that retrieve sales data from US and foreign suppliers, respectively. Specify structure as the data return format for 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=4×1 struct array with fields:
    productdescription
    suppliername
    city
    jan_sales
    feb_sales
    mar_sales

Determine the highest sales amount in January.

for i = 1:length(data) 
    jan_sales(i,1) = data(i).jan_sales; 
end
max(jan_sales)

ans = 3000

Close the database connection.

close(conn)

Input Arguments

conn — PostgreSQL native interface database connection

connection object

PostgreSQL native interface database connection, specified as a connection object.

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.auppliernumber = 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 the value using a character vector or string scalar.

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 the value using a character vector or string scalar.

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 table, 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 R2020b