execute

Execute SQL statement using relational database connection

Syntax

execute(conn,sqlquery)

Description

example

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

Examples

collapse all

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)

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')

Input Arguments

collapse all

Database connection, specified as a connection object created with the database function.

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

Introduced in R2018b