commit

Make changes to database permanent

collapse all in page

Syntax

commit(conn)

Description

commit(conn) makes all operations performed on a database since the last COMMIT or ROLLBACK permanent. This function works only when the connection is in transactional mode (AutoCommit is disabled). Because AutoCommit is on by default, set AutoCommit to off before using this function. Use commit after completing operations you want to permanently save.

conn can be any of the following connection objects:

  • MySQL®

  • PostgreSQL®

  • DuckDB™

  • SQLite

  • ODBC

  • JDBC

example

Examples

collapse all

Open Live Script

Use a MySQL® native interface database connection to insert product data from MATLAB® into a new table in a MySQL database. Then, commit the changes to the database.

Create a MySQL native interface database connection to a MySQL database using the data source name, user name, and password. The database contains the tables productTable and suppliers.

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

Allow manual committing of changes to the database by setting the AutoCommit property to off.

conn.AutoCommit = "off";

Create a MATLAB table that contains data for two products. The data is stored in the productTable and suppliers tables.

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 table named toyTable.

tablename = "toyTable";
sqlwrite(conn,tablename,data)

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)
rows=2×5 table
    30    500000    1000    25    "Rubik's Cube"
    40    600000    2000    30      "Doll House"

Commit the changes to the database.

commit(conn)

Close the database connection.

close(conn)
Open Live Script

Use a PostgreSQL native interface database connection to insert product data from MATLAB® into a new table in a PostgreSQL database. Then, commit the changes to the database.

Create a PostgreSQL native interface database connection to a PostgreSQL database using the data source name, user name, and password. The database contains the tables productTable and suppliers.

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);

Allow manual committing of changes to the database by setting the AutoCommit property to off.

conn.AutoCommit = "off";

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 toytable.

tablename = "toytable";
sqlwrite(conn,tablename,data)

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)
rows=2×5 table
    30    500000    1000    25    "Rubik's Cube"
    40    600000    2000    30      "Doll House"

Commit the changes to the database.

commit(conn)

Close the database connection.

close(conn)
Open Live Script

Create a transient, in-memory DuckDB™ database connection by using the duckdb function. Then, create a table by using the execute function.

conn = duckdb();
execute(conn,"CREATE TABLE demo (COL1 INTEGER)");

To manually commit changes, you must set the AutoCommit property to "off".

conn.AutoCommit = "off";

Next, use the execute function to insert data into the table. Then commit the changes to the database by using the commit function.

execute(conn,"INSERT INTO demo VALUES (1)");
commit(conn);

Verify that the changes were made to the database table by using the fetch function to access the table and import the data into MATLAB®. 

data = fetch(conn,"SELECT * FROM demo")
data=table
    COL1
    ____

     1

Close the connection.

close(conn);
Open Live Script

Use the MATLAB® interface to SQLite to insert product data from MATLAB into a new table in an SQLite database. Then, commit the changes to the database.

Create the SQLite connection conn to the existing SQLite database file tutorial.db. The database file contains the table productTable. The SQLite connection is an sqlite object.

dbfile = "tutorial.db";
conn = sqlite(dbfile);

Allow manual committing of changes to the database by setting the AutoCommit property to off.

conn.AutoCommit = "off";

Create a MATLAB table that contains data for two products. The data is stored in the productTable and suppliers tables.

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 table named toyTable.

tablename = "toyTable";
sqlwrite(conn,tablename,data)

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)
rows=2×5 table
    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

         30             5e+05            1000            25         "Rubik's Cube"  
         40             6e+05            2000            30         "Doll House"

Commit the changes to the database.

commit(conn)

Delete the new table to maintain the dataset.

sqlquery = "DROP TABLE toyTable";
execute(conn,sqlquery)

Close the database connection.

close(conn)

Connect to a MySQL database by using an ODBC database connection. Then insert some data and commit the changes to the database.

Create a database connection to the ODBC data source, MySQL ODBC. Before R2024a: setSecret and getSecret are not available. Specify username and password using character vectors or strings.

DataSourceName = "MySQL ODBC";
setSecret("usernamemysql");
setSecret("passwordmysql");
conn = database(DataSourceName,getSecret("usernamemysql"),getSecret("passwordmysql"))
conn = 
  connection with properties:

                  DataSource: 'MySQL ODBC'
                    UserName: ''
                     Message: ''
                        Type: 'ODBC Connection Object'
  Database Properties:

                  AutoCommit: 'on'
                    ReadOnly: 'off'
                LoginTimeout: 0
      MaxDatabaseConnections: 0

  Catalog and Schema Information:

              DefaultCatalog: 'toystore_doc'
                    Catalogs: {'information_schema', 'detsdb', 'mysql' ... and 4 more}
                     Schemas: {}

  Database and Driver Information:

         DatabaseProductName: 'MySQL'
      DatabaseProductVersion: '5.7.22'
                  DriverName: 'myodbc5a.dll'
               DriverVersion: '05.03.0014'

conn has an empty Message property, which indicates a successful connection.

The property sections of the conn object are:

Database Properties -- Information about the database configuration

Catalog and Schema Information -- Names of catalogs and schemas in the database

Database and Driver Information -- Names and versions of the database and driver

Allow manual committing of changes to the database by setting the AutoCommit property to off.

conn.AutoCommit = "off";

Create a MATLAB table that contains data for two products. The data is stored in the productTable and suppliers tables.

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 table named toyTable.

tablename = "toyTable";
sqlwrite(conn,tablename,data)

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)
rows=2×5 table
    30    500000    1000    25    "Rubik's Cube"
    40    600000    2000    30      "Doll House"

Commit the changes to the database.

commit(conn)

Close the database connection.

close(conn)

Input Arguments

collapse all

Database connection, specified as any of the following:

  • MySQL connection object created by using the mysql function.

  • PostgreSQL connection object created by using the postgresql function.

  • DuckDB connection object created by using the duckdb function.

  • SQLite connection object created by using the sqlite function.

  • ODBC connection object created by using the database function.

  • JDBC connection object created by using the database function.

Version History

Introduced in R2020b

expand all

See Also

| | | | | |

Topics