Main Content

odbc

Create ODBC database connection

Description

example

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.

example

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.

example

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

Examples

collapse all

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 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 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)

Input Arguments

collapse all

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

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

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.

DatabaseDSN-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"

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"

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"

Data Types: char | string

Name-Value Pair Arguments

Specify optional comma-separated pairs of Name,Value arguments. Name is the argument name and Value is the corresponding value. Name must appear inside quotes. You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN.

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.

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'

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.

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

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 platform does not support the following:

Introduced in R2021a