database - Connect to database

Alternatives

Connect to databases using Visual Query Builder. For more information, see Using Visual Query Builder.

Syntax

conn = database('datasourcename','username','password') conn = database('databasename','username',...'password','driver','databaseurl')

Description

conn = database('datasourcename','username','password') connects a MATLAB session to a database via an ODBC driver and assigns the returned connection object to conn. The arguments passed to this function are the ODBC data source, datasourcename, and the username and password required to connect. If a user name or password are not required, specify empty strings for these arguments.

conn = database('databasename','username',... 'password','driver','databaseurl') connects a MATLAB session to a database via a JDBC driver and assigns the returned connection object to conn. The arguments passed to this function are the name of the database databasename and the username and password required to connect. If a user name or password are not required, specify empty strings for these arguments. driver is the name of the JDBC driver and databaseurl is a JDBC URL object of the form jdbc:subprotocol:subname. subprotocol is a database type, such as Oracle. subname may contain other information used by driver, such as the location of the database and/or a port number. subname may take the form //hostname:port/databasename.

Note The JDBC driver is sometimes referred to as the class that implements the Sun Java SQL driver for your database.

If database establishes a database connection, it returns information about the connection object, as shown in the following example:

       Instance: 'SampleDB'
       UserName: ''
         Driver: []
            URL: []
    Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
        Message: []
         Handle: [1x1 sun.jdbc.odbc.JdbcOdbcConnection]
        TimeOut: 0
     AutoCommit: 'off'
           Type: 'Database Object'

Examples

Example 1 — Establish an ODBC Connection

Connect to an ODBC data source called Pricing, specifying user name mike, and password bravo:

conn = database('Pricing', 'mike', 'bravo');

Example 2 — Establish an ODBC Connection Without a User Name and Password

Connect to an ODBC data source SampleDB, where a user name and password are not required to access the database:

conn = database('SampleDB','','');

Example 3 — Establish a Dynamic Database Source Name (DSN) Connection

Connect to a database that is not defined in the Windows ODBC Data Sources pane, in this example Microsoft Access, by following the steps below:

% Specify the location of the database on disk.
dbpath = ...
   [matlabroot '\toolbox\database\dbdemos\tutorial.mdb']

% Create the connection URL.
conurl = [['jdbc:odbc:Driver={Microsoft Access Driver '...
     '(*.mdb)};DBQ='] dbpath]

% Connect to the database.
con = database('','','','sun.jdbc.odbc.JdbcOdbcDriver', ...
       conurl)

% Fetch data from the database.
e = exec(con,'SELECT ALL January FROM salesVolume');
e = fetch(e);
data = e.Data

% Close the connection.
close(con)

Example 4 — Establish a JDBC Connection

The JDBC driver name and database URL take different forms for different databases, as shown in the following table.

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` JDBC Driver: `com.mysql.jdbc.Driver` Database URL: `jdbc:mysql://devmetrics.mrkps.com/testing`
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:`
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.jdbc.SybDriver` Database URL: `jdbc:sybase:Tds:yourhostname:yourportnumber/`

Connect to the Oracle Server

Establish a JDBC connection to the Oracle database:

conn = database('oracle','scott','tiger',...
	'oracle.jdbc.driver.OracleDriver','jdbc:oracle:oci7:');

Input Argument Descriptions
`oracle` The database to which you connect
`scott` and `tiger` The required user name and password
`oracle.jdbc.driver.OracleDriver` The oci7 JDBC driver name
`jdbc:oracle:oci7` The URL that specifies the location of the database server

Connect to Microsoft SQL Server 2005

Establish a JDBC connection to the Microsoft SQL Server 2005 database:

conn = database('dbName','user','password', ...
   'com.microsoft.sqlserver.jdbc.SQLServerDriver', ...
   'jdbc:sqlserver://servername:portnumber;database=dbName')

Input Argument Descriptions
`dbName` The database to which you connect
`user` and `password` The required user name and password
`com.microsoft.sqlserver.jdbc.SQLServerDriver` The JDBC driver name
`jdbc:sqlserver://servername:portnumber;database=dbName` The URL that specifies the location of the database server

Connect to MySQL Server 5.1

Establish a JDBC connection to the MySQL server database:

conn = database('dbName', 'user', 'password', ...
   'com.mysql.jdbc.Driver', ...
   'jdbc:mysql://servername:portnumber/dbName');

Input Argument Descriptions
`dbName` The database to which you connect
`user` and `password` The required user name and password
`com.mysql.jdbc.Driver` The JDBC driver name
`jdbc:mysql://servername:portnumber/dbName` The URL that specifies the location of the database server

Configure MATLAB to Use JDBC Drivers for Authenticated Database Connection

To connect to a SQL server database with integrated Windows authentication using a JDBC driver, do the following:

Close MATLAB if it is running.
Insert the path to the database driver JAR file in the classpath.txt file. The classpath.txt file is located at:
```
$MATLABROOT\toolbox\local\classpath.txt
```
The updated path entry should now include the full path to the driver. For example:
```
C:\DB_Drivers\sqljdbc_2.0\enu\sqljdbc4.jar
```
Insert the path to the folder containing sqljdbc_auth.dll in the librarypath.txt file. The librarypath.txt file is located at:
```
$MATLABROOT\toolbox\local\librarypath.txt
```
The path entry should not include the file name sqljdbc_auth.dll:
```
C:\DB_Drivers\sqljdbc_2.0\enu\auth\x64
```
The sqljdbc_auth.dll file is installed in the following location:
```
<installation>\sqljdbc_<version>\<language>\auth\<arch>
```
where <installation> is the installation directory of the SQL server driver.
If you are running a 32-bit Java Virtual Machine (JVM), use the sqljdbc_auth.dll file in the x86 folder, even if the operating system is the x64 version. If you are running a 64-bit JVM on a x64 processor, use the sqljdbc_auth.dll file in the x64 folder. If you are running a 64-bit JVM on a IA-64 processor, use the sqljdbc_auth.dll file in the IA64 folder.
Start MATLAB.

Append the string integratedSecurity=true; to the database URL:

conn = database('dbName','','', ...
   'com.microsoft.sqlserver.jdbc.SQLServerDriver', ...
   ['jdbc:sqlserver://servername:portnumber; '...
    'database=dbName;'] 'integratedSecurity=true;')