Varaibles types in MATLAB and SQL

Dear all,

I have a question regarding the type of varible that I have in MATLAB and the new type when I export (from MATLAB to SQL) to SQL.

I have a vector in MATLAB with 2 position

v = [1  2.2 ]

I am using the toolbox Database to export. However, the vector that appear in SQL

v = [1.0000000000000000 2.2000000000000000].

I know that I can correct it in SQL but I would like to know if it is possible to define it in MATLAB.

Thank you for your attentino and have a good week.

Kind regarding,

Yogan Felipe Sganzerla

Answer 1

[Piyush Kumar]

Hi,

To control the number of decimal places when exporting data from MATLAB to a SQL database, you can use MATLAB's sprintf function with a format specifier and define the SQL table with appropriate precision.

Steps to Achieve the Desired Formatting:

1. Format the Numbers in MATLAB: Use the sprintf function with a format specifier like "%.2f" to ensure the numbers have two decimal places before inserting them into the database.

2. Define SQL Table with Precision: Create the SQL table with columns defined as NUMERIC(5, 2). This ensures that numbers stored in the database have two decimal places.

CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY,
    marks NUMERIC(5, 2),
    avg_marks NUMERIC(5, 2)
);

3. Construct and Execute the SQL Query in MATLAB: Here is an example of how to format the numbers and execute the SQL query using MATLAB:

v = [1 2.2];
sqlquery = sprintf('INSERT INTO test.student (marks, avg_marks) VALUES (%.2f, %.2f)', v(1), v(2));
disp(sqlquery);

% Connect to the database
conn = database('', 'username', 'password', 'Vendor', 'MySQL', 'Server', 'localhost', 'PortNumber', 3306);

% Check if the connection is successful
if isopen(conn)
    % Execute the SQL query
    exec(conn, sqlquery);
    close(conn);
else
    disp('Failed to connect to the database.');
    disp(conn.Message);
end

• Replace 'username', and 'password' with your actual database credentials.

By following these steps, the values stored in the database will be "1.00" and "2.20" for the marks and avg_marks columns, respectively.