%% Update Data Using SQL Prepared Statement
% Create an SQL prepared statement to update data in a Microsoft® SQL Server® database using a JDBC database connection. Use the UPDATE 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 first few rows of data in the database table inventoryTable.

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

%
% Create an SQL prepared statement for updating data using the JDBC database connection. The question marks in the UPDATE SQL statement indicate it is an SQL prepared statement. This statement updates data in the database table inventoryTable.

query = strcat("UPDATE inventoryTable SET Quantity = ? ", ...
    "WHERE 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 quantity and product number. The bindParamValues function updates the values in the ParameterValues property of the pstmt object.

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

%
% Update data in the database using the bound parameter values. Execute the SQL UPDATE statement using the execute function.

execute(conn,pstmt)

%
% Display the updated data in the database table inventoryTable. The first row in the table contains the updated quantity.

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

%
% Close the SQL prepared statement and database connection.

close(pstmt)
close(conn)

% Copyright 2019 The MathWorks, Inc.