connection

MySQL native interface database connection

expand all in page

Description

Create a connection to a MySQL® database using the MySQL native interface.

First, install the MySQL Connector/C++ driver, and then configure a MySQL native interface data source using the databaseConnectionOptions function. For details by platform, see MySQL Native Interface for Windows or MySQL Native Interface for macOS. The MySQL native interface is not supported on Linux®.

Creation

Create a connection object by using the mysql function.

Properties

expand all

This property is read-only.

Data source name, specified as a string scalar.

Example: "MySQLDataSource"

Data Types: string

This property is read-only.

Database name, specified as a string scalar.

If you use the 'DatabaseName' name-value pair argument of the mysql function, the mysql function sets the Database property of the connection object to the specified value.

Example: "toystore_doc"

Data Types: string

This property is read-only.

Server name, specified as a string scalar.

If you use the 'Server' name-value pair argument of the mysql function, the mysql function sets the Server property of the connection object to the specified value.

Example: "dbtb00"

Data Types: string

This property is read-only.

User name, specified as a string scalar.

Data Types: string

This property is read-only.

Default catalog, specified as a string scalar.

Example: "toy_store"

Data Types: string

This property is read-only.

Catalogs in database, specified as a string array.

Example: ["information", "mysql"]

Data Types: string

This property is read-only.

Schemas in database, specified as a string array.

Example: ["information_schema", "toys"]

Data Types: string

Auto-commit transactions, specified as one of these values:

  • "on" — Database transactions are automatically committed to the database.

  • "off" — Database transactions must be committed to the database manually.

You can set this property by using dot notation.

This property is read-only.

Login timeout, specified as a positive numeric scalar. The login timeout specifies the number of seconds that the driver waits while trying to connect to a database before throwing an error.

When no login timeout for the connection attempt is specified, the value is 0.

When a login timeout is not supported by the database, the value is -1.

Data Types: double

This property is read-only.

Maximum number of database connections, specified as a positive numeric scalar.

When the database has no upper limit to the maximum number of database connections, the value is 0.

When a maximum number of database connections is not supported by the database, the value is -1.

Data Types: double

This property is read-only.

Database product name, specified as a string scalar.

Example: "MySQL"

Data Types: string

This property is read-only.

Database product version, specified as a string scalar.

Example: "5.7.22"

Data Types: string

This property is read-only.

Driver name of the MySQL driver, specified as a string scalar.

Example: "MySQL Connector/C++"

Data Types: string

This property is read-only.

Driver version of the MySQL driver, specified as a string scalar.

Example: "8.0.15"

Data Types: string

Object Functions

expand all

closeClose MySQL native interface database connection
isopenDetermine if MySQL native interface database connection is open
sqlouterjoinOuter join between two MySQL database tables
sqlinnerjoinInner join between two MySQL database tables
sqlfindFind information about all table types in MySQL database
sqlreadImport data into MATLAB from MySQL database table
fetchImport results of SQL statement in MySQL database into MATLAB
executeSQLScriptExecute SQL script on MySQL database
sqlwriteInsert MATLAB data into MySQL database table
executeExecute SQL statement using MySQL native interface database connection
commitMake changes to MySQL database permanent
rollbackUndo changes to MySQL database

Examples

collapse all

Open Live Script

Create a MySQL® native interface connection to a MySQL database. Then, import data from the database into MATLAB® and perform simple data analysis. Close the database connection.

This example assumes that you are connecting to a MySQL database version 5.7.22 using the MySQL Connector/C++ driver version 8.0.15.

Connect to the database using the data source name, user name, and password.

datasource = "MySQLNative";
username = "root";
password = "matlab";

conn = mysql(datasource,username,password)
conn = 
  connection with properties:

                  DataSource: "MySQLNative"
                    UserName: "root"

  Database Properties:

                  AutoCommit: "on"
                LoginTimeout: 0
      MaxDatabaseConnections: 151

  Catalog and Schema Information:

              DefaultCatalog: "toystore_doc"
                    Catalogs: ["information_schema", "mysql", "performance_schema" ... and 3 more]
                     Schemas: []

  Database and Driver Information:

         DatabaseProductName: "MySQL"
      DatabaseProductVersion: "5.7.22"
                  DriverName: "MySQL Connector/C++"
               DriverVersion: "8.0.15"

The property sections of the connection 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

Import all data from the table inventoryTable into MATLAB using the sqlread function. Display the first three rows of data.

tablename = "inventoryTable";
data = sqlread(conn,tablename);
head(data,3)
ans=3×4 table
    productNumber    Quantity    Price        inventoryDate    
    _____________    ________    _____    _____________________

          1            1700      14.5     "2014-09-23 09:38:34"
          2            1200         9     "2014-07-08 22:50:45"
          3             356        17     "2014-05-14 07:14:28"

Determine the highest product quantity from the table.

max(data.Quantity)
ans = 9000

Close the database connection conn.

close(conn)

See Also

| |

Topics

Introduced in R2020b

Database Toolbox Documentation

Support