%% Specify Column Types When Inserting Data into New Table
% Use a MySQL® native interface database connection to insert product data from MATLAB® into a new table in a MySQL database. Specify the data types of the columns in the new database table.
%
% Create a MySQL native interface database connection to a MySQL database using the data source name, user name, and password.

datasource = "MySQLNative";
username = "root";
password = "matlab";
conn = mysql(datasource,username,password);

%
% Create a MATLAB table that contains data for two products.

data = table([30;40],[500000;600000],[1000;2000],[25;30], ...
    ["Rubik's Cube";"Doll House"],'VariableNames',["productNumber" ...
    "stockNumber" "supplierNumber" "unitCost" "productDescription"]);

%
% Insert the product data into a new database table named toyTable. Use the 'ColumnType' name-value pair argument and a string array to specify the data types of all the columns in the database table.

tablename = "toyTable";
coltypes = ["numeric" "numeric" "numeric" "numeric" "varchar(255)"];
sqlwrite(conn,tablename,data,'ColumnType',coltypes)

%
% Import the contents of the database table into MATLAB and display the rows. The results contain two rows for the inserted products.

rows = sqlread(conn,tablename)

%
% Close the database connection.

close(conn)

% Copyright 2020 The MathWorks, Inc.