execute

Execute SQL statement using relational database connection

Syntax

execute(conn,sqlquery)

execute(conn,pstmt)

Description

execute(conn,sqlquery) executes an SQL query that contains a non-SELECT SQL statement by using the relational database connection.

execute(conn,pstmt) executes an SQL prepared statement that contains a non-SELECT SQL statement by using the relational database connection.

Examples

Execute Non-SELECT SQL Statement

Using a relational database connection, create and execute a non-SELECT SQL statement that deletes a database table.

This example uses a Microsoft® SQL Server® Version 11.00.2100 database and the Microsoft SQL Server Driver 11.00.5058.

Create a 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,'','');

Load patient information into the MATLAB® workspace.

patients = readtable('patients.xls');

Create the patients database table using the patient information.

tablename = 'patients';
sqlwrite(conn,tablename,patients)

Import the data from the patients database table.

data = sqlread(conn,tablename);

Delete the patients database table using the execute function.

sqlquery = ['DROP TABLE ' tablename];
execute(conn,sqlquery)

Close the database connection.

close(conn)

Call Stored Procedure Without Input and Output Arguments

Working with a Microsoft^® SQL Server^® database, run a stored procedure by using the native ODBC database connection conn.

Define a stored procedure named create_table that creates a table named test_table by executing the following code. This procedure has no input or output arguments. The code assumes that you are using a Microsoft SQL Server database.

CREATE PROCEDURE create_table 
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

CREATE TABLE test_table
	 (
		CATEGORY_ID     INTEGER     IDENTITY PRIMARY KEY,
		CATEGORY_DESC   CHAR(50)    NOT NULL
        );
	
END
GO

Connect to the Microsoft SQL Server database. This code assumes that you are connecting to a data source named MS SQL Server with a user name and password.

conn = database('MS SQL Server','username','pwd');

Call the stored procedure create_table.

execute(conn,'create_table')

Insert Data Using SQL Prepared Statement

Create an SQL prepared statement to insert data from MATLAB® into a Microsoft® SQL Server® database using a JDBC database connection. Use the INSERT SQL statement for the SQL query. Execute the SQL prepared statement and display the results.

Create a JDBC database connection to an SQL Server database with Windows® authentication. Specify a blank user name and password.

datasource = 'MSSQLServerJDBCAuth';
conn = database(datasource,'','');

Import data from the database using the sqlread function. Display the last few rows of data in the database table inventoryTable.

tablename = "inventoryTable";
data = sqlread(conn,tablename);
tail(data,3)

ans=3×4 table
    productNumber    Quantity    Price         inventoryDate     
    _____________    ________    _____    _______________________

         11             567         0     {'2012-09-11 00:30:24'}
         12            1278         0     {'2010-10-29 18:17:47'}
         13            1700      14.5     {'2009-05-24 10:58:59'}

Create an SQL prepared statement for inserting data using the JDBC database connection. The question marks in the INSERT SQL statement indicate it is an SQL prepared statement. This statement inserts data from MATLAB into the database table inventoryTable.

query = "INSERT INTO inventoryTable VALUES(?,?,?,?)";
pstmt = databasePreparedStatement(conn,query)

pstmt = 
  SQLPreparedStatement with properties:

           SQLQuery: "INSERT INTO inventoryTable values(?,?,?,?)"
     ParameterCount: 4
     ParameterTypes: ["numeric"    "numeric"    "numeric"    "string"]
    ParameterValues: {[]  []  []  []}

pstmt is an SQLPreparedStatement object with these properties:

• SQLQuery — SQL prepared statement query

• ParameterCount — Parameter count

• ParameterTypes — Parameter types

• ParameterValues — Parameter values

Bind parameter values in the SQL prepared statement. Select all parameters in the SQL prepared statement using their numeric indices. Specify the values to bind for the product number, quantity, price, and inventory date. Match the format of dates in the database. The bindParamValues function updates the values in the ParameterValues property of the pstmt object.

selection = [1 2 3 4];
values = {20,1000,55,"2019-04-25 00:00:00.000"};
pstmt = bindParamValues(pstmt,selection,values)

pstmt = 
  SQLPreparedStatement with properties:

           SQLQuery: "INSERT INTO inventoryTable values(?,?,?,?)"
     ParameterCount: 4
     ParameterTypes: ["numeric"    "numeric"    "numeric"    "string"]
    ParameterValues: {[20]  [1000]  [55]  ["2019-04-25 00:00:00.000"]}

Insert data from MATLAB into the database using the bound parameter values. Execute the SQL INSERT statement using the execute function.

execute(conn,pstmt)

Display the inserted data in the database table inventoryTable. The last row in the table contains the inserted data.

tablename = "inventoryTable";
data = sqlread(conn,tablename);
tail(data,4)

ans=4×4 table
    productNumber    Quantity    Price           inventoryDate       
    _____________    ________    _____    ___________________________

         11             567         0     {'2012-09-11 00:30:24'    }
         12            1278         0     {'2010-10-29 18:17:47'    }
         13            1700      14.5     {'2009-05-24 10:58:59'    }
         20            1000        55     {'2019-04-25 00:00:00.000'}

Close the SQL prepared statement and database connection.

close(pstmt)
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.

sqlquery — SQL statement

character vector | string scalar

SQL statement, specified as a character vector or string scalar. The SQL statement can be any valid non-SELECT SQL statement.

The SQL statement can be a stored procedure that does not return any result sets. For stored procedures that return one or more result sets, use the fetch function. For procedures that return output arguments, use the runstoredprocedure function.

For information about the SQL query language, see the SQL Tutorial.

Example: 'DROP TABLE patients'

Data Types: char | string

pstmt — SQL prepared statement

SQLPreparedStatement object

SQL prepared statement, specified as an SQLPreparedStatement object.

Version History

Introduced in R2018b