odbc

Create ODBC database connection

Since R2021a

Syntax

conn = odbc(datasource,username,password)

conn = odbc(datasource,username,password,Name,Value)

conn = odbc(dsnless)

conn = odbc(dsnless,DriverManager=dm)

Description

conn = odbc(datasource,username,password) creates a database connection to an ODBC data source with a user name and password. The database connection conn is returned as an ODBC connection object.

conn = odbc(datasource,username,password,Name,Value) specifies options using one or more name-value arguments. For example, 'LoginTimeout',5 creates an ODBC connection with a login timeout of 5 seconds.

conn = odbc(dsnless) creates a connection to a database using a DSN-less connection string. (DSN is a data source name.)

conn = odbc(dsnless,DriverManager=dm) creates a connection to an ODBC data source using a DSN-less connection string. You can set DriverManager to "unixODBC" or "iODBC" for use on macOS platforms.

Examples

Connect to MySQL Using ODBC Database Connection

Connect to a MySQL® database using an ODBC database connection. Then, import data from the database into MATLAB®, perform a simple data analysis, and close the database connection.

This example assumes that you are connecting to a MySQL Version 5.7.22 database using the MySQL ODBC 5.3 Driver.

Create a database connection to a MySQL database. Specify the user name and password.

datasource = "MySQL ODBC";
conn = odbc(datasource,"root","matlab")

conn = 
  connection with properties:

                  DataSource: 'MySQL ODBC'
                    UserName: 'root'
                     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

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

query = "SELECT * FROM inventoryTable";
data = fetch(conn,query);
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 in the table.

max(data.Quantity)

ans = 9000

Close the database connection.

close(conn)

Connect to MySQL Using ODBC Database Connection with Additional Options

Connect to a MySQL® database using an ODBC data source and a timeout value. Then, import data from the database into MATLAB®, perform a simple data analysis, and close the database connection.

This example assumes that you are connecting to a MySQL Version 5.7.22 database using the MySQL ODBC 5.3 Driver.

Create a database connection to a MySQL database using an ODBC data source. Specify the user name and password. Also, specify a timeout value of 5 seconds for connecting to the database.

datasource = "MySQL ODBC";
username = "root";
password = "matlab";
conn = odbc(datasource,username,password,'LoginTimeout',5)

conn = 
  connection with properties:

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

                  AutoCommit: 'on'
                    ReadOnly: 'off'
                LoginTimeout: 5
      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

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

query = "SELECT * FROM inventoryTable";
data = fetch(conn,query);
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 in the table.

max(data.Quantity)

ans = 9000

Close the database connection.

close(conn)

Connect to MySQL Using DSN-Less Connection

Connect to a MySQL® database using a DSN-less database connection. Then, import data from the database into MATLAB®, perform a simple data analysis, and close the database connection.

This example assumes that you are connecting to a MySQL Version 5.7.22 database using the MySQL ODBC 5.3 Driver.

Create a database connection to a MySQL database. Specify the connection string.

dsnless = strcat("Driver={MySQL ODBC 5.3 Ansi Driver}; Server=dbtb01;", ...
   "Database=toystore_doc; UID=root; PWD=matlab");
conn = odbc(dsnless)

conn = 
  connection with properties:

                  DataSource: ''
                    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

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

query = "SELECT * FROM inventoryTable";
data = fetch(conn,query);
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 in the table.

max(data.Quantity)

ans = 9000

Close the database connection.

close(conn)

MySQL ODBC for macOS DSN-Less Connection

This example requires the download and installation of the MySQL^® ODBC driver from MySQL Community Downloads. Verify the iODBC driver manager is installed in the path /usr/local/iODBC. If you need to install the iODBC driver manager, you can download it from iodbc.org.

Connect to the database using the DSN-less connection string and the iODBC driver manager.

dsnless = "Driver=/usr/local/mysql-connector-odbc-8.0.30-macos12-x86-64bit/lib/libmyodbc8w.so;Server=dbtb04;Port=3306;UID=username;PWD=password;Database=toy_store"
conn = odbc(dsnless,DriverManager="iODBC")

conn = 

connection with properties:

 

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

 

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

 

  Catalog and Schema Information:

 

              DefaultCatalog: 'toy_store'
                    Catalogs: {'information_schema', 'mysql', 'performance_schema' ... and 3 more}
                     Schemas: {}

 

  Database and Driver Information:

 

         DatabaseProductName: 'MySQL'
      DatabaseProductVersion: '8.0.3-rc-log'
                  DriverName: 'libmyodbc8w.so'
               DriverVersion: '08.00.0031'

Input Arguments

datasource — Data source name

character vector | string scalar

Data source name, specified as a character vector or string scalar. Specify the name of an existing data source.

Example: "myDataSource"

Data Types: char | string

username — User name

character vector | string scalar

User name required to access the database, specified as a character vector or string scalar. If no user name is required, specify an empty value "".

Data Types: char | string

password — Password

character vector | string scalar

Password required to access the database, specified as a character vector or string scalar. If no password is required, specify an empty value "".

Data Types: char | string

dsnless — DSN-less connection

character vector | string scalar

DSN-less connection string, specified as a character vector or string scalar. The connection string is specific to each database and usually contains connection parameters such as the database server name, port number, and database name. For details about the connection parameters of your database, see the database documentation.

This table shows some sample DSN-less connection strings for the Windows^® and Linux^® platforms. To use these samples, substitute your values for the corresponding connection parameters in the strings. The values might vary based on your database configuration.

Database	DSN-Less Connection String
Microsoft® SQL Server®	Windows — "Driver={SQL Server Native Client 11.0}; Server=localhost\toy_store; Port=1433; Database=toy_store; UID=user; PWD=password" Linux — "Driver={ODBC Driver 17 for SQL Server}; Server=localhost,1433; Database=toy_store; UID=user; PWD=password" macOS — "Driver=/usr/local/Cellar/msodbcsql18/18.1.2.1/lib/libmsodbcsql.18.dylib; Server=localhost,1433;UID=username;PWD=password;Database=toy_store"
MySQL	Windows — "Driver={MySQL ODBC 5.3 Ansi Driver}; Server=localhost; Database=toy_store; UID=user; PWD=password" Linux — "Driver={MySQL ODBC 5.3}; Server=localhost; Database=toy_store; UID=user; PWD=password" macOS — "Driver=/usr/local/mysql-connector-odbc-8.0.30-macos12-x86-64bit/lib/libmyodbc8w.so;Server=dbtb04;Port=3306;UID=username;PWD=password;Database=toy_store"
PostgreSQL	Windows — "Driver={PostgreSQL ANSI(x64)}; Server=localhost; Database=toy_store; UID=user; PWD=password" Linux — "Driver={PostgreSQL ANSI};Servername=localhost;Database=toy_store;UID=user;PWD=password" macOS — "Driver=/usr/local/psqlodbcw.so;Server=localhost;Port=5432;UID=username;PWD=password;Database=toy_store"

Data Types: char | string

dm — Driver manager for macOS platform

"unixODBC" (default) | "iODBC"

Since R2023b

Driver manager for macOS platform, specified as "unixODBC" or "iODBC". For more information, see Configuring an ODBC Driver on Windows, macOS, and LINUX. The ODBC driver manager manages communication between apps and ODBC drivers. All drivers that ship with MATLAB^® depend on unixODBC. If using your own driver, refer to your driver manual to determine which driver manager to use.

Example: dm = "unixODBC"; odbc(dsnless,DriverManager=dm)

Name-Value Arguments

Specify optional pairs of arguments as Name1=Value1,...,NameN=ValueN, where Name is the argument name and Value is the corresponding value. Name-value arguments must appear after other arguments, but the order of the pairs does not matter.

Before R2021a, use commas to separate each name and value, and enclose Name in quotes.

Example: conn = odbc(datasource,username,password,'AutoCommit','off','ReadOnly','off') creates a database connection to an ODBC data source with a user name and password, and specifies that database transactions must be committed to the database manually and the database data is writeable.

AutoCommit — Flag to autocommit transactions

'on' (default) | 'off'

Flag to autocommit 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.

Example: 'AutoCommit','off'

LoginTimeout — Login timeout

0 (default) | positive numeric scalar

Login timeout, specified as a name-value argument consisting of a LoginTimeout and 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.

To specify no login timeout for the connection attempt, set the value to 0.

When the database does not support a login timeout, the function sets this value to –1.

Example: LoginTimeout=5

Data Types: double

ReadOnly — Read-only database data

'off' (default) | 'on'

Read-only database data, specified as one of these values:

• 'on' — Database data is read-only.

• 'off' — Database data is writable.

Example: 'ReadOnly','on'

Limitations

The Linux and macOS platforms do not support the following:

• select function

• ODBC database connection using the Database Explorer app

• MySQL ODBC driver 8.0 and higher

Version History

Introduced in R2021a

R2023b: ODBC Support for macOS

Connect to database servers on a Mac using the shipped driver from MathWorks^® or a downloaded driver.