executeSQLScript - Execute SQL script on MySQL database - MATLAB (original) (raw)

Execute SQL script on MySQL database

Since R2020b

Syntax

Description

[results](#mw%5Fb1e23d08-4cb8-4537-b560-5f4890e6f818) = executeSQLScript([conn](#mw%5F8a2d388a-d3a7-403f-9301-2af0b0e6d6b1%5Fsep%5Fmw%5Fe8a1bdf2-6db9-421c-be5b-85d2f5c29cb1),[scriptfile](#mw%5F5de8dc17-1bd5-45e4-9927-095a0e4c8e85)) 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](#mw%5Fb1e23d08-4cb8-4537-b560-5f4890e6f818) = executeSQLScript([conn](#mw%5F8a2d388a-d3a7-403f-9301-2af0b0e6d6b1%5Fsep%5Fmw%5Fe8a1bdf2-6db9-421c-be5b-85d2f5c29cb1),[scriptfile](#mw%5F5de8dc17-1bd5-45e4-9927-095a0e4c8e85),[Name,Value](#namevaluepairarguments)) 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.

example

Examples

collapse all

Execute SQL Script Using MySQL Native Interface

Connect to a MySQL® 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 MySQL native interface database connection to a MySQL database using the data source name, user name, and password. The database contains the tables productTable and suppliers.

datasource = "MySQLNative"; username = "root"; password = "matlab"; conn = mysql(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.

Close the database connection.

Execute SQL Script and Return Results as Structures

Connect to a MySQL® 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 SQL query retrieves sales of products from US suppliers, and the second retrieves sales of products from foreign suppliers.

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

datasource = "MySQLNative"; username = "root"; password = "matlab"; conn = mysql(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 = struct with fields: productDescription: [4×1 string] supplierName: [4×1 string] city: [4×1 string] Jan_Sales: [4×1 double] Feb_Sales: [4×1 double] Mar_Sales: [4×1 double]

Determine the highest sales amount in January.

Close the database connection.

Input Arguments

collapse all

conn — MySQL® native interface database connection

connection object

MySQL native interface database connection, specified as a connection object. Starting in R2024a, it is recommended that you use setSecret and getSecret to store and retrieve your credentials for databases that require authentication. For more details, refer to this example.

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 SQLSELECT 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 asName1=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 theresults structure array and stores any error message in theMessage 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 theresults 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

collapse all

results — Query results

structure array

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

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 inresults(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. TheexecuteSQLScript 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 theexecuteSQLScript function to return an error.

Version History

Introduced in R2020b