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

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

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

%
% Close the SQL prepared statement and database connection.

close(pstmt)
close(conn)

% Copyright 2019 The MathWorks, Inc.