database - Connect to database

Syntax

conn = database(instance,username,password) example

conn = database(instance,username,password,driver,databaseurl) example

conn = database(instance,username,password,Name,Value) example

Description

conn = database(instance,username,password) returns a database connection object for the connection to the ODBC data source set up, instance, via an ODBC driver.

example

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

example

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

Examples

ODBC Connection

Connect to an Oracle database.

Connect to the database with the ODBC data source name, oracle, using the user name, scott, and password, tiger.

conn = database('oracle','scott','tiger')

Oracle JDBC Connection using URL

Connect to an Oracle database via JDBC driver.

Connect to the database, test_db, using the user name, scott, and password, tiger. Use the JDBC driver, oracle.jdbc.driver.OracleDriver, to make the connection. The URL defined by the driver vendor is jdbc:oracle:oci7:.

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

Oracle JDBC Connection Using Name-Value Connection Properties

Connect to an Oracle database via JDBC driver. Specify the vendor and connection options using name-value pair arguments.

Connect to the database, test_db, using the user name, scott, and password, tiger. The Database Server machine name is remotehost and the port number which the server is listening on is 1234.

conn = database('test_db','scott','tiger','Vendor','Oracle',...
          'DriverType','oci','Server','remotehost','PortNumber',1234)

MySQL JDBC Connection on Default Port

Connect to a MySQL database via a JDBC driver. Specify the vendor and connection options using name-value pair arguments.

Connect to the database, test_db, on the machine remotehost. Use the user name, root, and password, matlab.

conn = database('test_db','root','matlab','Vendor','MySQL',...
          'Server','remotehost')

PostGreSQL JDBC Connection to localhost on Default Port

Connect to a local PostGreSQL database via JDBC driver. Specify the vendor and connection options using name-value pair arguments.

Connect to the database,test_db, using the user name, postgres, and password, matlab.

conn = database('test_db','postgres','matlab','Vendor','PostGreSQL')

Microsoft SQL Server Authenticated Database Connection

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

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.

Use the AuthType parameter to establish a Windows Authentication connection.

conn = database('dbName','','', ...
   'Vendor','Microsoft SQL Server','Server','servername',...
   'AuthType','Windows')

Input Arguments

`instance`	Data source set up or database string Data source set up for ODBC connection, or name of database for JDBC connection, specified as a string.
`username`	User name string User name required to access database, specified as a string. If no user name is required, specify empty strings, `''`.
`password`	Password string Password required to access database, specified as a string. If no password is required, specify empty strings, `''`.
`driver`	JDBC driver name string JDBC driver name, specified as a string. This is the name of the Java driver that implements the java.sql.Driver interface.
`databaseurl`	Connection URL string Database connection URL, specified as a string. This is a vendor-specific URL that is typically constructed using connection properties like server name, port number, database name, and so on. 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, where 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 the following strings:

'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) | string

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

Example: 'Server','remotehost'

'PortNumber'

Server port
scalar

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 name-value pair consisting of 'AuthType' and one of the following strings:

'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 on of the following strings:

'thin'
'oci'

Example: 'DriverType','thin'

'URL'

Connection URL
string

Connection URL, specified as the comma-separated pair consisting of 'URL' and a string value. If you specify URL, you might not need to specify any other properties.

Output Arguments

conn

Database connection
Database connection object

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

Instance
UserName
Driver
URL
Constructor
Message
Handle
TimeOut
AutoCommit
Type

Tips

Use logintimeout before database to set the maximum time for a connection attempt.
Alternatively use Visual Query Builder to connect to databases.
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)

Definitions

JDBC Driver Name and Database URL

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` To insert or select characters with nondefault encodings, append the string `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:`
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/`