database

Connect to database

collapse all in page

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

example

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.

example

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 pair arguments.

example

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.

example

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

Examples

collapse all

Open Live Script

Connect to a Microsoft® SQL Server® 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 a JDBC driver, you must configure a JDBC data source. For more information, see the configureJDBCDataSource function.

This example assumes that you are connecting to a Microsoft SQL Server Version 11.00.2100 database using the Microsoft SQL Server Driver 11.00.5058.

Create a database connection to the JDBC data source MSSQLServerJDBCAuth. This data source configures a JDBC driver to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password. 

datasource = "MSSQLServerJDBCAuth";
username = "";
password = "";
conn = database(datasource,username,password)
conn = 
  connection with properties:

                  DataSource: 'MSSQLServerJDBCAuth'
                    UserName: ''
                      Driver: 'com.microsoft.sqlserver.j ...'
                         URL: 'jdbc:sqlserver://dbtb04:5 ...'
                     Message: ''
                        Type: 'JDBC Connection Object'
  Database Properties:

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

  Catalog and Schema Information:

              DefaultCatalog: 'toy_store'
                    Catalogs: {'master', 'model', 'msdb' ... and 2 more}
                     Schemas: {'db_accessadmin', 'db_backupoperator', 'db_datareader' ... and 14 more}

  Database and Driver Information:

         DatabaseProductName: 'Microsoft SQL Server'
      DatabaseProductVersion: '11.00.2100'
                  DriverName: 'Microsoft JDBC Driver 4.0 ...'
               DriverVersion: '4.0.2206.100'

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       20      '2014-12-01 08:50:15.0'
          2            1200        9      '2014-07-08 22:50:45.0'
          3             356       17      '2014-05-14 07:14:28.0'
          4            2580       21      '2013-06-08 14:24:33.0'
          5            9000        3      '2012-09-14 15:00:25.0'
          6            4540        8      '2013-12-25 19:45:00.0'
          7            6034       16      '2014-08-06 08:38:00.0'
          8            8350        5      '2011-06-18 11:45:35.0'

Determine the highest product quantity in the table.

max(data.Quantity)
ans = 9000

Close the database connection.

close(conn)
Open Script

Connect to the Microsoft® SQL Server® database. Then, import data from the database into MATLAB®. Perform simple data analysis. Close the database connection.

This example assumes that you are connecting to a Microsoft® SQL Server® Version 11.00.2100 database using the Microsoft® SQL Server® Driver 11.00.5058.

Create a database connection to a Microsoft® SQL Server® database with Windows® authentication. Specify a blank user name and password.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','')

conn = 

  connection with properties:

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

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

  Catalog and Schema Information:

              DefaultCatalog: 'toy_store'
                    Catalogs: {'master', 'msdb', 'tempdb' ... and 1 more}
                     Schemas: {'dbo', 'guest', 'INFORMATION_SCHEMA' ... and 3 more}

  Database and Driver Information:

         DatabaseProductName: 'Microsoft SQL Server'
      DatabaseProductVersion: '11.00.2100'
                  DriverName: 'sqlncli11.dll'
               DriverVersion: '11.00.6518'

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);
data(1:3,:)

ans =

  3×4 table

    productNumber    Quantity    Price          inventoryDate      
    _____________    ________    _____    _________________________

          1            1700       20      '2014-12-01 08:50:15.000'
          2            1200        9      '2014-07-08 22:50:45.000'
          3             356       17      '2014-05-14 07:14:28.000'

Determine the highest product quantity in the table.

max(data.Quantity)

ans =

        9000

Close the database connection.

close(conn)

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, user name, and password. Use the JDBC driver org.postgresql.Driver to make the connection.

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

databasename = 'dbname';
username = 'username';
password = 'pwd';
driver = 'org.postgresql.Driver';
url = 'jdbc:postgresql://host:port/dbname';

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

  connection with properties:

                  DataSource: 'dbname'
                    UserName: '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)
Open Script

Connect to the Microsoft® SQL Server® 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 Microsoft® SQL Server® Version 11.00.2100 database using the Microsoft® SQL Server® JDBC Driver 4.0.2206.100.

Create a database connection to a Microsoft® SQL Server® database with Windows® authentication and a login timeout of 5 seconds. Specify a blank user name and password.

databasename = 'toy_store';
conn = database(databasename,'','','Vendor','Microsoft SQL Server', ...
    'Server','dbtb04','AuthType','Windows','PortNumber',54317, ...
    'LoginTimeout',5)

conn = 

  connection with properties:

                  DataSource: 'toy_store'
                    UserName: ''
                      Driver: 'com.microsoft.sqlserver.j ...'
                         URL: 'jdbc:sqlserver://dbtb04:5 ...'
                     Message: ''
                        Type: 'JDBC Connection Object'
  Database Properties:

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

  Catalog and Schema Information:

              DefaultCatalog: 'toy_store'
                    Catalogs: {'master', 'model', 'msdb' ... and 2 more}
                     Schemas: {'db_accessadmin', 'db_backupoperator', 'db_datareader' ... and 15 more}

  Database and Driver Information:

         DatabaseProductName: 'Microsoft SQL Server'
      DatabaseProductVersion: '11.00.2100'
                  DriverName: 'Microsoft JDBC Driver 4.0 ...'
               DriverVersion: '4.0.2206.100'

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);
data(1:3,:)

ans =

  3×4 table

    productNumber    Quantity    Price         inventoryDate     
    _____________    ________    _____    _______________________

          1            1700       20      '2014-12-01 08:50:15.0'
          2            1200        9      '2014-07-08 22:50:45.0'
          3             356       17      '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)

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

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

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

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 ValuesParam Value DescriptionParamValue 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 listeningNumeric 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.

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

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

Auto-commit transactions, specified as the comma-separated pair consisting of 'AutoCommit' and 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 the comma-separated pair 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

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'

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.

Output Arguments

collapse all

Database connection, returned as a connection object.

More About

collapse all

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.

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

Compatibility Considerations

expand all

Errors starting in R2020b

See Also

Functions

Apps

Topics

Introduced before R2006a

Database Toolbox Documentation

Support