%% Delete Data Using SQL Prepared Statement
% Create an SQL prepared statement to delete data in a Microsoft® SQL Server® database using a JDBC database connection. Use the DELETE 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 rows of data in the database table productTable. The data contains rows for product numbers 16 through 20, which you will delete later in this example.

tablename = "productTable";
data = sqlread(conn,tablename)

%
% Create an SQL prepared statement for deleting data using the JDBC database connection. The question marks in the DELETE SQL statement indicate it is an SQL prepared statement. This statement deletes data in the database table productTable for a specified range of product numbers.

query = strcat("DELETE FROM productTable ", ...
    "WHERE productNumber > ? AND productNumber < ?");
pstmt = databasePreparedStatement(conn,query)

% 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 range of product numbers between 15 and 21 (exclusive). The bindParamValues function updates the values in the ParameterValues property of the pstmt object.

selection = [1 2];
values = {15,21};
pstmt = bindParamValues(pstmt,selection,values)

%
% Delete data in the database using the bound parameter values. Execute the SQL DELETE statement using the execute function.

execute(conn,pstmt)

%
% Display data in the database table productTable. The rows with product numbers 16 through 20 are no longer in the table.

tablename = "productTable";
data = sqlread(conn,tablename)

%
% Close the SQL prepared statement and database connection.

close(pstmt)
close(conn)

% Copyright 2019 The MathWorks, Inc.