database

Connect to database

Syntax

conn = database(datasource,username,password)

conn = database(databasename,username,password,Param1,ParamValue1,...,ParamN,ParamValueN)

conn = database(___,Name,Value)

conn = database(databasename,username,password,driver,url)

Description

conn = database(datasource,username,password) creates a database connection to a data source with a user name and password. The database connection is a connection object. The data source specifies whether the database connection uses an ODBC or JDBC driver. When a database requires authentication, the recommended practice is to store credentials in your MATLAB^® vault using setSecret instead of including them in your code. To connect to the database, specify datasource and retrieve your credentials using the getSecret function.

conn = database(databasename,username,password,Param1,ParamValue1,...,ParamN,ParamValueN) creates a JDBC database connection to a database name with a user name, password, and JDBC driver parameters as specified by multiple name-value arguments.

conn = database(___,Name,Value) specifies options using one or more name-value pair arguments in addition to any of the input argument combinations in previous syntaxes. For example, conn = database(datasource,username,password,'LoginTimeout',5); creates an ODBC or JDBC connection, as specified by the datasource input argument, with a login timeout of 5 seconds.

conn = database(databasename,username,password,driver,url) creates a JDBC database connection specified by the JDBC driver name and database connection URL.

Examples

Connect to MySQL Using ODBC Driver

Connect to a MySQL® database. Then, import data from the database into MATLAB®. Perform simple data analysis, and then close the database connection.

To create a database connection using an ODBC driver, you must configure an ODBC data source.

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

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.

datasource = "MySQL ODBC";
setSecret("usernamemysql");
setSecret("passwordmysql");
conn = database(datasource,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

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

tablename = "inventoryTable";
data = sqlread(conn,tablename);
head(data)

ans=8×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'}
          4            2580        21     {'2013-06-08 14:24:33'}
          5            9000         3     {'2012-09-14 15:00:25'}
          6            4540         8     {'2013-12-25 19:45:00'}
          7            6034        16     {'2014-08-06 08:38:00'}
          8            8350         5     {'2011-06-18 11:45:35'}

Determine the highest product quantity in the table.

max(data.Quantity)

ans = 9000

Close the database connection.

close(conn)

Connect to PostgreSQL Using JDBC Driver URL

Connect to the PostgreSQL database. Then, import data from the database into MATLAB, perform simple data analysis, and then close the database connection. This example assumes that you are connecting to a PostgreSQL 9.4.5 database using the JDBC PostgreSQL Native Driver 8.4.

Connect to the database using the database name and your credentials. Use the JDBC driver org.postgresql.Driver to make the connection.

Before R2024a: setSecret and getSecret are not available. Specify username and password using character vectors or strings.

Use the URL defined by the driver vendor including your server name host, port number, and database name.

databasename = "dbname";
setSecret("usernamepsql");
setSecret("passwordpsql");
driver = "org.postgresql.Driver";
url = "jdbc:postgresql://host:port/dbname";
conn = database(databasename,getSecret("usernamepsql"),getSecret("passwordpsql"),driver,url)

conn = 

  connection with properties:

                  DataSource: 'dbname'
                    UserName: ''
                      Driver: 'org.postgresql.Driver'
                         URL: 'jdbc:postgresql://host: ...'
                     Message: ''
                        Type: 'JDBC Connection Object'
  Database Properties:

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

  Catalog and Schema Information:

              DefaultCatalog: 'catalog'
                    Catalogs: {'catalog'}
                     Schemas: {'schema1', 'schema2', 'schema3' ... and 1 more}

  Database and Driver Information:

         DatabaseProductName: 'PostgreSQL'
      DatabaseProductVersion: '9.4.5'
                  DriverName: 'PostgreSQL Native Driver'
               DriverVersion: 'PostgreSQL 8.4 JDBC4 (bui ...'

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 select function. Display the data.

selectquery = "SELECT * FROM inventoryTable";
data = select(conn,selectquery)

ans = 

    productnumber    quantity    price         inventorydate     
    _____________    ________    _____    _______________________

     1               1700        14.5     '2014-09-23 09:38:34.0'
     2               1200         9.3     '2014-07-08 22:50:45.0'
     3                356        17.2     '2014-05-14 07:14:28.0'
     ...

Determine the highest quantity in the table.

max(data.quantity)

ans = 

    9000

Close the database connection.

close(conn)

Connect to MySQL Using ODBC Driver with Additional Options

Connect to the MySQL® database using an ODBC driver. Then, import data from the database into MATLAB®, perform simple data analysis, and then close the database connection. The example assumes that you are connecting to the MySQL database version 5.7.22 and MySQL ODBC 5.3 ANSI driver.

Create a database connection to a MySQL database and a login timeout of 5 seconds.

Before R2024a: setSecret and getSecret are not available. Specify username and password using character vectors or strings.

databasename = "toystore_doc";
setSecret("usernamemysql");
setSecret("passwordmysql");
conn = database(databasename,getSecret("usernamemysql"),getSecret("passwordmysql"),'Vendor','MySQL', ...
    'Server','dbtb01','PortNumber',3306,'LoginTimeout',5)

conn = 
  connection with properties:

                  DataSource: 'toystore_doc'
                    UserName: ''
                      Driver: 'com.mysql.cj.jdbc.Driver'
                         URL: 'jdbc:mysql://dbtb01:3306/ ...'
                     Message: ''
                        Type: 'JDBC Connection Object'
  Database Properties:

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

  Catalog and Schema Information:

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

  Database and Driver Information:

         DatabaseProductName: 'MySQL'
      DatabaseProductVersion: '5.7.22'
                  DriverName: 'MySQL Connector/J'
               DriverVersion: 'mysql-connector-java-8.0. ...'

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 select function. Display the first three rows of data.

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

max(data.Quantity)

ans = 9000

Close the database connection.

close(conn)

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

databasename — JDBC database name

character vector | string scalar

JDBC database name, specified as a character vector or string scalar. Specify the name of your database to create a database connection using a JDBC driver.

The name differs for different database systems. For example, databasename is the SID or the service name when you are connecting to an Oracle^® database. Or, databasename is the catalog name when you are connecting to a MySQL^® database.

For details about your database name, contact your database administrator or refer to your database documentation.

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

Param1,ParamValue1,...,ParamN,ParamValueN — JDBC driver parameters

name-value pair arguments

JDBC driver parameters, specified as multiple name-value pair arguments. A Param argument is a character vector or string scalar that specifies the name of a JDBC driver parameter. A ParamValue argument is a character vector, string scalar, or numeric scalar that specifies the value of the JDBC driver parameter.

Param Valid Values	Param Value Description	ParamValue Valid Values
"Vendor"	Database vendor	'MySQL' 'Oracle' 'Microsoft SQL Server' 'PostgreSQL' If you are connecting to a database system not listed here, use the driver and url syntax.
"Server"	Database server name or address	character vector string scalar 'localhost' (default)
"PortNumber"	Server port number where the server is listening	Numeric scalar
"AuthType"	Authentication type (required only for Microsoft® SQL Server®)	'Server' — Microsoft SQL Server authentication 'Windows' — Windows® authentication
"DriverType"	Driver type (required only for Oracle)	'thin' — Thin driver 'oci' — Windows authentication

Tip:

When creating a JDBC connection using the JDBC driver parameters, you can omit the following:

• 'Server' parameter when connecting to a database locally

• 'PortNumber' parameter when connecting to a database server listening on the default port (except for Oracle connections)

Example: 'Vendor','Microsoft SQL Server','Server','dbtb04','AuthType','Windows','PortNumber',54317 connects to a Microsoft SQL Server database using a JDBC driver on a machine named dbtb04 with Windows authentication and using port number 54317.

Example: 'Vendor','MySQL','Server','remotehost' connects to a MySQL database using a JDBC driver on a machine named remotehost.

driver — JDBC driver name

character vector | string scalar

JDBC driver name, specified as a character vector or string scalar that refers to the name of the Java^® driver that implements the java.sql.Driver interface. For details, see JDBC driver name and database connection URL.

Data Types: char | string

url — Database connection URL

character vector | string scalar

Database connection URL, specified as a character vector or string scalar for the vendor-specific URL. This URL is typically constructed using connection properties such as server name, port number, and database name. For details, see JDBC driver name and database connection URL. If you do not know the driver name or the URL, you can use name-value pair arguments to specify individual connection properties.

Data Types: char | string

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: 'LoginTimeOut',5,'ErrorHandling','report' specifies waiting for 5 seconds to connect to a database before throwing an error and displaying any error messages at the command line.

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 the name-value argument consisting of '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 login timeout is unsupported by the database, the value is -1.

Example: 'LoginTimeout',5

Data Types: double

ReadOnly — Read-only database data

'off' (default) | 'on'

Read-only database data, specified as the comma-separated pair consisting of 'ReadOnly' and one of these values:

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

• 'off' — Database data is writable.

Example: 'ReadOnly','on'

ErrorHandling — Error handling

'store' (default) | 'report'

Error handling, specified as the comma-separated pair consisting of 'ErrorHandling' and one of these values:

• 'store' — Store an error message in the Message property of the connection object.

• 'report' — Display an error message at the command line.

DriverManager — 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 at devart.com. The ODBC driver manager manages communication between apps and ODBC drivers. All the drivers that ship with MATLAB depend on unixODBC. If you use your own driver, refer to your driver manual to determine which driver manager to use.

Example: DriverManager=unixODBC

Output Arguments

conn — Database connection

connection object

Database connection, returned as an ODBC connection object or JDBC connection object.

More About

JDBC Driver Name and Database Connection URL

The JDBC driver name and database connection URL take different forms for different databases. For details, consult your database driver documentation.

Database	JDBC Driver Name and Database URL Example Syntax
IBM® Informix®	JDBC driver: com.informix.jdbc.IfxDriver Database URL: jdbc:informix-sqli://161.144.202.206:3000: INFORMIXSERVER=stars
Microsoft SQL Server 2005	JDBC driver: com.microsoft.sqlserver.jdbc.SQLServerDriver Database URL: jdbc:sqlserver://localhost:port;database=databasename
MySQL	JDBC driver: twz1.jdbc.mysql.jdbcMysqlDriver Database URL: jdbc:z1MySQL://natasha:3306/metrics For MySQL Connector 8.0 and later: JDBC driver: com.mysql.cj.jdbc.Driver For previous versions of MySQL Connector: JDBC driver: com.mysql.jdbc.Driver Database URL: jdbc:mysql://devmetrics.mrkps.com/testing To insert or select characters with encodings that are not default, append the value useUnicode=true&characterEncoding=encoding to the URL, where encoding is any valid MySQL character encoding followed by &. For example, useUnicode=true&characterEncoding=utf8&. The trailing & is required.
Oracle oci7 drivers	JDBC driver: oracle.jdbc.driver.OracleDriver Database URL: jdbc:oracle:oci7:@rex
Oracle oci8 drivers	JDBC driver: oracle.jdbc.driver.OracleDriver Database URL: jdbc:oracle:oci8:@111.222.333.44:1521: Database URL: jdbc:oracle:oci8:@frug
Oracle 10 Connections with JDBC (Thin drivers)	JDBC driver: oracle.jdbc.driver.OracleDriver Database URL: jdbc:oracle:thin:
Oracle Thin drivers	JDBC driver: oracle.jdbc.driver.OracleDriver Database URL: jdbc:oracle:thin:@144.212.123.24:1822: Database URL: jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ServerName)(PORT = 1234)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbname) ) )
PostgreSQL	JDBC driver: org.postgresql.Driver Database URL: jdbc:postgresql://host:port/database
PostgreSQL with SSL Connection	JDBC driver: org.postgresql.Driver Database URL: jdbc:postgresql:servername:dbname:ssl= true&sslfactory=org.postgresql.ssl.NonValidatingFactory& The trailing & is required.
Teradata®	JDBC driver: com.teradata.jdbc.TeraDriver Database URL: jdbc:teradata://DatabaseServerName

Tips

• If you specify a data source name in the datasource input argument that appears on both ODBC and JDBC data source lists, then the database function creates an ODBC database connection. In this case, if you must create a JDBC database connection instead, append _JDBC to the name of the data source.

Alternative Functionality

Database Explorer App

The database function connects to a database using the command line. To connect to a database and explore its data in a visual way, use the Database Explorer app.

Version History

Introduced before R2006a

R2020b: database.ODBCConnection syntax has been removed

The database.ODBCConnection syntax has been removed. Use the syntaxes of the database function instead. Some differences between the workflows require updates to your code.

Update Code

In prior releases, you created a connection to a database by using the database.ODBCConnection syntax. For example:

conn = database.ODBCConnection(datasource,username,password);

Now use the database syntax instead.

conn = database(datasource,username,password);