Documentation

This is machine translation

Translated by Microsoft
Mouseover text to see original. Click the button below to return to the English verison of the page.

Note: This page has been translated by MathWorks. Please click here
To view all translated materals including this page, select Japan from the country navigator on the bottom of this page.

database

Connect to database

Syntax

conn = database(datasource,username,password)
conn = database(datasource,username,password,driver,url)
conn = database(___,Name,Value)

Description

example

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

example

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

example

conn = database(___,Name,Value) includes any of the input argument combinations in the previous syntaxes and adds options that you specify by one or more Name,Value pair arguments.

To specify optional database properties for ODBC or JDBC drivers, use the ODBC and JDBC connection options. For example, conn = database(datasource,username,password,'LoginTimeout',5); creates an ODBC connection with a login timeout of 5 seconds.

To create a JDBC connection without using the database connection URL, use the JDBC connection options. For example, conn = database(datasource,username,password,'Vendor','MySQL','Server','remotehost'); creates a JDBC connection to a MySQL® database.

Note

The database.ODBCConnection syntax will be removed in a future release. Use the database syntax instead.

Examples

collapse all

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

The code 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: 'MS SQL Server Auth'
                    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', 'INFORMATION_SCHEMA', 'sys'}

  Database and Driver Information:

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

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 = 

    productNumber    Quantity    Price    inventoryDate
    _____________    ________    _____    _____________

    1                1700        15       '2014-09-23' 
    2                1200         9       '2014-07-08' 
    3                 356        17       '2014-05-14' 

Determine the highest quantity in the table.

max(data.Quantity)
ans =

        9000

Close the database connection.

close(conn)

First, connect to the PostgreSQL database. Then, import data from the database into MATLAB® and perform simple data analysis. Close the database connection. The code 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.

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

conn = database(datasource,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)

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

The code 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.

datasource = 'toy_store';
conn = database(datasource,'','','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 10 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 = 

    productNumber    Quantity    Price    inventoryDate
    _____________    ________    _____    _____________

    1                1700        15       '2014-09-23' 
    2                1200         9       '2014-07-08' 
    3                 356        17       '2014-05-14' 

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 or database name, specified as a character vector or string scalar. Specify a data source for ODBC connection, and the database name for JDBC connection.

  • For an ODBC driver, datasource is the name you provide for your data source when you create a data source using the Microsoft® ODBC Administrator.

  • For a JDBC driver, datasource is the name of your database.

The name differs for different database systems. For example, datasource is the SID or the service name when you are connecting to an Oracle® database. Or, datasource 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 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 single quotes (' '). You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN.

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

Tip:

When creating a JDBC connection using the JDBC connection options, you can omit:

  • 'Server' name-value pair argument when connecting to a database locally

  • 'PortNumber' name-value pair argument when connecting to a database server listening on the default port (except for Oracle connections)

ODBC and JDBC Connection Options

collapse all

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'

JDBC Connection Options

collapse all

Database vendor, specified as the comma-separated pair consisting of 'Vendor' and one of these values:

  • 'MySQL'

  • 'Oracle'

  • 'Microsoft SQL Server'

  • 'PostgreSQL'

If you are connecting to a database system not listed here, use the driver and url syntax.

Example: 'Vendor','Oracle'

Database server name or address, specified as the comma-separated pair consisting of 'Server' and a character vector or string scalar.

Example: 'Server','remotehost'

Data Types: char | string

Server port number where the server is listening, specified as the comma-separated pair consisting of 'PortNumber' and a numeric scalar.

Example: 'PortNumber',1234

Data Types: double

Authentication type (required only for Microsoft SQL Server®), specified as the comma-separated pair consisting of 'AuthType' and one of these values:

  • 'Server'Microsoft SQL Server authentication

  • 'Windows' — Windows® authentication

Example: 'AuthType','Windows'

Driver type (required only for Oracle), specified as the comma-separated pair consisting of 'DriverType' and one of these values:

  • 'thin' — Thin driver

  • 'oci' — Windows authentication

Example: 'DriverType','thin'

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.

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

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.

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.

Introduced before R2006a

Was this topic helpful?