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.

connection

Relational database connection

Description

Create a database connection using either ODBC or JDBC drivers. For information on which connection option is best in your situation, see Choosing Between ODBC and JDBC Drivers. Connecting to a database using an ODBC driver uses the native ODBC interface. For details, see Connecting to Database Using Native ODBC Interface.

You can use this object to connect to various databases using different drivers that you install and administer. For details, see Connecting to Database.

Creation

Create a connection object using the database function.

Properties

expand all

ODBC and JDBC Connection Properties

This property is read-only.

Data source name for ODBC connection or database name for JDBC connection, specified as a character vector. 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.

The data source name is an empty character vector when the connection is invalid.

Example: 'MS SQL Server'

Data Types: char

This property is read-only.

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

Example: 'username'

Data Types: char

This property is read-only.

Database connection status message, specified as a character vector. The status message is empty when the database connection is successful. Otherwise, this property contains an error message.

Example: 'ODBC Driver Error: [Micro ...'

Data Types: char

This property is read-only.

Database connection type, specified as one of these values:

  • 'JDBC Connection Object' — Database connection is created using a JDBC driver.

  • 'ODBC Connection Object' — Database connection is created using an ODBC driver.

Data Types: char

JDBC Connection Properties

This property is read-only.

JDBC driver, specified as a character vector when connecting to a database using a JDBC driver URL. This property depends on the URL property.

Example: 'com.mysql.jdbc.jdbc2.opti ...'

Data Types: char

This property is read-only.

Database connection URL, specified as a character vector for a vendor-specific string. This property depends on the Driver property.

Example: 'jdbc:mysql://sname:1234/ ...'

Data Types: char

Database Properties

Auto-commit 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.

Data Types: char

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

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

  • 'off' — Database data is writable.

Data Types: char

This property is read-only.

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.

When no login timeout for the connection attempt is specified, the value is 0.

When login timeout is not supported by the database, the value is -1.

Data Types: double

This property is read-only.

Maximum database connections, specified as a positive, numeric scalar.

The value is 0 when there is no upper limit to the maximum number of database connections.

When the maximum number of database connections is not supported by the database, the value is -1.

Data Types: double

Catalog and Schema Information

This property is read-only.

Default catalog name, specified as a character vector.

When a database does not specify a default catalog, the value is an empty character vector ''.

Example: 'catalog'

Data Types: char

This property is read-only.

Catalog names, specified as a cell array of character vectors.

When a database does not contain catalogs, the value is an empty cell array {}.

Example: {'catalog1', 'catalog2'}

Data Types: cell

This property is read-only.

Schema names, specified as a cell array of character vectors.

When a database does not contain schemas, the value is an empty cell array {}.

Example: {'schema1', 'schema2', 'schema3'}

Data Types: cell

Database and Driver Information

This property is read-only.

Database product name, specified as a character vector.

When the database connection is invalid, the value is an empty character vector ''.

Example: 'Microsoft SQL Server'

Data Types: char

This property is read-only.

Database product version, specified as a character vector.

When the database connection is invalid, the value is an empty character vector ''.

Example: '11.00.2100'

Data Types: char

This property is read-only.

Driver name of an ODBC or JDBC driver, specified as a character vector.

When the database connection is invalid, the value is an empty character vector ''.

Example: 'sqlncli11.dll'

Data Types: char

This property is read-only.

Driver version of an ODBC or JDBC driver, specified as a character vector.

When the database connection is invalid, the value is an empty character vector ''.

Example: '11.00.5058'

Data Types: char

Object Functions

closeClose and invalidate database and driver resource utilizer
datainsertExport MATLAB data into database table
fetchImport data into MATLAB workspace from database cursor or from execution of SQL statement
rollbackUndo database changes
updateReplace data in database table with MATLAB data
columnsReturn database table column names
execExecute SQL statement and open cursor
insertAdd MATLAB data to database tables
runsqlscriptRun SQL script on database
commitMake database changes permanent
fastinsertAdd MATLAB data to database tables
isopenDetermine if database connection or database cursor is open
tablesReturn database table names
selectExecute SQL SELECT statement and import data into MATLAB
runstoredprocedureCall stored procedure with and without input and output arguments

Examples

expand all

First, create an ODBC connection to the MySQL 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 MySQL database version 5.5.46 using the MySQL ODBC 5.3 ANSI Driver.

Connect to the database using the data source name, user name, and password.

datasource = 'dsname';
username = 'username';
password = 'pwd';

conn = database(datasource,username,password)
conn = 

  connection with properties:

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

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

  Catalog and Schema Information:

              DefaultCatalog: 'catalog'
                    Catalogs: {'catalog1', 'catalog2'}
                     Schemas: {}

  Database and Driver Information:

         DatabaseProductName: 'MySQL'
      DatabaseProductVersion: '5.5.46-0+deb7u1'
                  DriverName: 'myodbc5a.dll'
               DriverVersion: '05.03.0004'

conn has an empty Message property, which indicates a successful connection.

The property sections of the connection 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 product quantity from the table.

max(data.Quantity)
ans =

        9000

Close the database connection conn.

close(conn)

First, create a JDBC connection to the Oracle 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 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 using the Oracle JDBC Driver 12.1.0.1.0.

Connect to the database using the data source name, user name, and password.

datasource = 'dsname';
username = 'username';
password = 'pwd';

conn = database(datasource,username,password,'Vendor','Oracle', ...
    'Server','remotehost','DriverType','thin','PortNumber',1234)
conn = 

  connection with properties:

                  DataSource: 'datasource'
                    UserName: 'username'
                      Driver: 'oracle.jdbc.pool.OracleDa ...'
                         URL: 'jdbc:oracle:thin:@(DESCRI ...'
                     Message: ''
                        Type: 'JDBC Connection Object'
  Database Properties:

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

  Catalog and Schema Information:

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

  Database and Driver Information:

         DatabaseProductName: 'Oracle'
      DatabaseProductVersion: 'Oracle Database 12c Enter ...'
                  DriverName: 'Oracle JDBC driver'
               DriverVersion: '12.1.0.1.0'

conn has an empty Message property, which indicates a successful connection.

The property sections of the connection 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 product quantity from the table.

max(data.Quantity)
ans =

        9000

Close the database connection conn.

close(conn)

Alternative Functionality

A connection object is one of the two available database connection types. The other creates a sqlite object that connects to a SQLite database file using the MATLAB interface to SQLite without installing or administering a database or driver. For details, see Working with MATLAB Interface to SQLite.

Introduced before R2006a

Was this topic helpful?