Documentation

database

Connect to database

There are three ways to connect to a database. For ODBC drivers, connect to a database using the native ODBC interface or the JDBC/ODBC bridge. For JDBC drivers, connect to a database using a JDBC driver. For details about deciding which connection option is best in your situation, see Choosing Between ODBC and JDBC Drivers. For details about the native ODBC interface, see Connecting to a Database Using the Native ODBC Interface.

    Note:   The JDBC/ODBC bridge functionality will be removed in a future release. To connect to a database, use the native ODBC interface or a JDBC driver instead.

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

A database connection object is one of the two available database connection types. The other creates a SQLite database connection object using the function sqlite. This object lets you connect to a SQLite database file using the MATLAB® Interface to SQLite without installing or administering a database or driver. For details, see Working with the MATLAB Interface to SQLite.

Syntax

  • conn = database.ODBCConnection(instance,username,password)
    example
  • conn = database(instance,username,password)
    example
  • conn = database(instance,username,password,driver,databaseurl)
    example
  • conn = database(instance,username,password,Name,Value)
    example

Description

example

conn = database.ODBCConnection(instance,username,password) returns a database connection object for the connection to the ODBC data source setup instance using the native ODBC interface.

example

conn = database(instance,username,password) returns a database connection object for the connection to the ODBC data source setup instance using the JDBC/ODBC bridge.

example

conn = database(instance,username,password,driver,databaseurl) connects to the database instance using a JDBC driver.

example

conn = database(instance,username,password,Name,Value) connects to the database instance using a JDBC driver with connection properties specified by one or more Name,Value pair arguments.

Examples

collapse all

Connect to Microsoft Access Using the Native ODBC Interface

Connect to the database with the ODBC data source name dbtoolboxdemo using the user name username and password pwd.

conn = database.ODBCConnection('dbtoolboxdemo','username','pwd')
conn = 

  ODBCConnection with properties:

      Instance: 'dbtoolboxdemo'
      UserName: 'username'
       Message: []
        Handle: [1x1 database.internal.ODBCConnectHandle]
       TimeOut: 0
    AutoCommit: 0
          Type: 'ODBCConnection Object'

database.ODBCConnection returns conn as database.ODBCConnection object. conn has an empty Message property, which indicates a successful connection.

Close the database connection conn.

close(conn)

Connect to Microsoft Access Using the JDBC/ODBC Bridge

Connect to the database with the ODBC data source name dbtoolboxdemo using the user name username and password pwd.

conn = database('dbtoolboxdemo','username','pwd')
conn =
 
       Instance: 'dbtoolboxdemo'
       UserName: 'username'
         Driver: []
            URL: []
    Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
        Message: []
         Handle: [1x1 sun.jdbc.odbc.JdbcOdbcConnection]
        TimeOut: 0
     AutoCommit: 'on'
           Type: 'Database Object'

database returns conn as a Database Object. conn has an empty Message property, which indicates a successful connection.

Close the database connection conn.

close(conn)

Connect to Microsoft SQL Server Using Windows Authentication

Connect to a Microsoft® SQL Server® database with integrated Windows® Authentication using a JDBC driver.

Use the AuthType parameter to establish a Windows Authentication connection. For details about how to set up Windows Authentication and find your port number, see Microsoft SQL Server JDBC for Windows.

conn = database('test_db','','',...
   'Vendor','Microsoft SQL Server','Server','servername',...
   'AuthType','Windows','portnumber',123456)
conn =
 
       Instance: 'test_db'
       UserName: ''
         Driver: []
            URL: []
    Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
        Message: []
         Handle: [1x1 com.microsoft.sqlserver.jdbc.SQLServerConnection]
        TimeOut: 0
     AutoCommit: 'on'
           Type: 'Database Object'

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

Close the database connection conn.

close(conn)

Connect to Sybase Using a JDBC Driver URL

Connect to the database dbname using the user name username and password pwd. Use the JDBC driver com.sybase.jdbc4.jdbc.SybDriver to make the connection. Use the URL defined by the driver vendor including your server name, port number, and database name. For details, see Sybase JDBC for Windows.

conn = database('dbname','username','pwd',...
                'com.sybase.jdbc4.jdbc.SybDriver','URL')
conn =
 
       Instance: 'dbname'
       UserName: 'username'
         Driver: 'com.sybase.jdbc4.jdbc.SybDriver'
            URL: 'URL'
    Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
        Message: []
         Handle: [1x1 com.sybase.jdbc4.jdbc.SybConnection]
        TimeOut: 0
     AutoCommit: 'on'
           Type: 'Database Object'

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

Close the database connection conn.

close(conn)

Connect to Oracle Using a JDBC Driver

Connect to an Oracle® database using name-value pair arguments to specify the vendor and connection options.

Connect to the database test_db using the user name username and password pwd. Enter the driver type as thin for a default connection to Oracle. To connect to Oracle with Windows authentication, use oci. The database server machine name is remotehost and the port number that the server is listening on is 1234. For details, see Oracle JDBC for Windows.

conn = database('test_db','username','pwd','Vendor','Oracle',...
          'DriverType','thin','Server','remotehost','PortNumber',1234)
conn =
 
       Instance: 'test_db'
       UserName: 'username'
         Driver: []
            URL: []
    Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
        Message: []
         Handle: [1x1 oracle.jdbc.driver.T4CConnection]
        TimeOut: 0
     AutoCommit: 'on'
           Type: 'Database Object'

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

Close the database connection conn.

close(conn)

Connect to MySQL Using a JDBC Driver

Connect to a MySQL® database using name-value pair arguments to specify the vendor and connection options.

Connect to the database test_db on the machine remotehost. Use the user name username and password pwd. For details, see MySQL JDBC for Windows.

conn = database('test_db','username','pwd','Vendor','MySQL',...
                'Server','remotehost')
conn =
 
       Instance: 'test_db'
       UserName: 'username'
         Driver: []
            URL: []
    Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
        Message: []
         Handle: [1x1 com.mysql.jdbc.JDBC4Connection]
        TimeOut: 0
     AutoCommit: 'on'
           Type: 'Database Object'

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

Close the database connection conn.

close(conn)

Connect to Microsoft Access Using a File DSN

Connect to a Microsoft Access™ database with .accdb format using an OBDC driver.

Specify the location of the database on the disk.

dbpath = ['C:\Data\Matlab\MyDatabase.accdb']; 

Create the connection URL.

url = [['jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='''';DBQ='] dbpath];

Connect to the database MyDatabase.accdb using dpath and url.

conn = database('','','','sun.jdbc.odbc.JdbcOdbcDriver',url); 

Fetch data from the database.

curs = exec(conn,'SELECT ALL January FROM salesVolume');
curs = fetch(curs);
data = curs.Data;

After you finish working with the cursor object, close it.

close(curs)

Close the database connection conn.

close(conn)

Connect to PostgreSQL Using a JDBC Driver

Connect to a PostgreSQL database using name-value pair arguments to specify the vendor and connection options.

Connect to the database test_db using the user name username and password pwd on the machine remotehost. For details, see PostgreSQL JDBC for Windows.

conn = database('test_db','username','pwd','Vendor','PostgreSQL',...
                'Server','remotehost')
conn =
 
       Instance: 'test_db'
       UserName: 'username'
         Driver: []
            URL: []
    Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
        Message: []
         Handle: [1x1 org.postgresql.jdbc4.Jdbc4Connection]
        TimeOut: 0
     AutoCommit: 'on'
           Type: 'Database Object'

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

Close the database connection conn.

close(conn)

Related Examples

Input Arguments

collapse all

instance — Data source setup or database namecharacter vector

Data source setup or database name, specified as a character vector. Specify a data source for ODBC connection, and the database name for JDBC connection. For an ODBC driver, instance is the name you provide for your data source when you create a data source using the Microsoft ODBC Administrator. For a JDBC driver, instance is the name of your database. The name differs for different database systems. For example, instance is the SID or the service name when you are connecting to an Oracle database. Or, instance 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.

username — User namecharacter vector

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

password — Passwordcharacter vector

Password required to access the database, specified as a character vector. If no password is required, specify empty value ''.

driver — JDBC driver namecharacter vector

JDBC driver name, specified as a character vector 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.

databaseurl — Database connection URLcharacter vector

Database connection URL, specified as a character vector 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.

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 on a machine named remotehost.

'Vendor' — Database vendor'MySQL' | 'Oracle' | 'Microsoft SQL Server' | 'PostgreSQL'

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

  • 'MySQL'

  • 'Oracle'

  • 'Microsoft SQL Server'

  • 'PostgreSQL'

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

Example: 'Vendor','Oracle'

'Server' — Database server'localhost' (default) | character vector

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

Example: 'Server','remotehost'

'PortNumber' — Server portscalar

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

Example: 'PortNumber',1234

Data Types: double

'AuthType' — Authentication'Server' (default) | 'Windows'

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

  • 'Server'

  • 'Windows'

Specify 'Windows' for Windows Authentication.

Example: 'AuthType','Windows'

'DriverType' — Driver type'thin' | 'oci'

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

  • 'thin'

  • 'oci'

Specify 'oci' for Windows Authentication.

Example: 'DriverType','thin'

'URL' — Connection URLcharacter vector

Connection URL, specified as the comma-separated pair consisting of 'URL' and a character vector. If you specify URL, do not specify the other name-value pair arguments.

Output Arguments

collapse all

conn — Database connectiondatabase connection object

Database connection, returned as a database connection object. The database connection object has the following properties.

Property

Description

Instance

Data source name when using ODBC or database name when using JDBC

UserName

User name used for database login

Driver

JDBC or JDBC/ODBC driver object used for database connection

URL

Driver vendor-specific string for database connection

Constructor

Internal Java or C++ representation of database connection object

Message

Database connection status message that is empty when a successful connection is established

Handle

Internal Java or C++ representation of database connection object

TimeOut

Number of seconds that the driver waits while trying to establish a database connection before throwing an error

AutoCommit

Set to on to apply updates to the database automatically and set to off to commit updates to the database manually

Type

Database connection object or database.ODBCConnection object

Depending on the database connection, the database connection object properties and property values vary. For details on the differences, see this table.

Database Connection TypeDatabase Connection Object Property Differences

Native ODBC database connection

  • Excludes Driver, URL, and Constructor properties.

  • Type property is equal to database.ODBCConnection object.

  • Handle property is database.internal.ODBCConnectHandle.

  • Instance property contains the data source name.

JDBC/ODBC bridge connection

  • Instance property contains the data source name.

  • Handle property is sun.jdbc.odbc.JdbcOdbcConnection.

JDBC driver connection

Instance property contains the database name.

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, as shown in the following table.

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=... to the URL, where ... is any valid MySQL character encoding. For example, useUnicode=true&characterEncoding=utf8.

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.

Sybase SQL Server® and Sybase® SQL Anywhere®

JDBC driver: com.sybase.jdbc4.jdbc.SybDriver

Database URL: jdbc:sybase:Tds:yourhostname:yourportnumber/

Tips

  • Use logintimeout before database to set the maximum time for a connection attempt.

  • Alternatively, connect to databases using Database Explorer.

  • When making a JDBC connection using name-value connection properties:

    • You can skip the Server parameter when connecting to a database locally.

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

Introduced before R2006a

Was this topic helpful?