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.

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

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

execute(conn,'create_table')

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:

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)