database
Connect to database
Syntax
Description
creates a database connection to a data source with a user name and password. The
database connection is a conn
= database(datasource
,username
,password
)connection
object. The data source
specifies whether the database connection uses an ODBC or JDBC driver. When a
database requires authentication, the recommended practice is to store credentials
in your MATLAB® vault using setSecret
instead of including them in your code. To connect to the
database, specify datasource
and retrieve your credentials using
the getSecret
function.
creates a JDBC database connection to a database name with a user name, password,
and JDBC driver parameters as specified by multiple name-value arguments.conn
= database(databasename
,username
,password
,Param1,ParamValue1,...,ParamN,ParamValueN
)
specifies options using one or more name-value pair arguments in addition to any of
the input argument combinations in previous syntaxes. For example, conn
= database(___,Name,Value
)conn =
database(datasource,username,password,'LoginTimeout',5);
creates an
ODBC or JDBC connection, as specified by the datasource
input
argument, with a login timeout of 5 seconds.
Examples
Connect to MySQL Using ODBC Driver
Connect to a MySQL® database. Then, import data from the database into MATLAB®. Perform simple data analysis, and then close the database connection.
To create a database connection using an ODBC driver, you must configure an ODBC data source.
This example assumes that you are connecting to a MySQL Version 5.7.22 database using the MySQL Driver 5.3.
Create a database connection to the ODBC data source MySQL
ODBC
.
Before R2024a:
setSecret
and getSecret
are not
available. Specify username and password using character vectors or
strings.
datasource = "MySQL ODBC"; setSecret("usernamemysql"); setSecret("passwordmysql"); conn = database(datasource,getSecret("usernamemysql"),getSecret("passwordmysql"))
conn = connection with properties: DataSource: 'MySQL ODBC' UserName: '' Message: '' Type: 'ODBC Connection Object' Database Properties: AutoCommit: 'on' ReadOnly: 'off' LoginTimeout: 0 MaxDatabaseConnections: 0 Catalog and Schema Information: DefaultCatalog: 'toystore_doc' Catalogs: {'information_schema', 'detsdb', 'mysql' ... and 4 more} Schemas: {} Database and Driver Information: DatabaseProductName: 'MySQL' DatabaseProductVersion: '5.7.22' DriverName: 'myodbc5a.dll' DriverVersion: '05.03.0014'
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 configurationCatalog and Schema Information
-- Names of catalogs and schemas in the databaseDatabase and Driver Information
-- Names and versions of the database and driver
Import all data from the table inventoryTable
into
MATLAB® using the sqlread
function. Display the first
eight rows of data.
tablename = "inventoryTable";
data = sqlread(conn,tablename);
head(data)
ans=8×4 table
productNumber Quantity Price inventoryDate
_____________ ________ _____ _______________________
1 1700 14.5 {'2014-09-23 09:38:34'}
2 1200 9 {'2014-07-08 22:50:45'}
3 356 17 {'2014-05-14 07:14:28'}
4 2580 21 {'2013-06-08 14:24:33'}
5 9000 3 {'2012-09-14 15:00:25'}
6 4540 8 {'2013-12-25 19:45:00'}
7 6034 16 {'2014-08-06 08:38:00'}
8 8350 5 {'2011-06-18 11:45:35'}
Determine the highest product quantity in the table.
max(data.Quantity)
ans = 9000
Close the database connection.
close(conn)
Connect to PostgreSQL Using JDBC Driver URL
Connect to the PostgreSQL database. Then, import data from the database into MATLAB, perform simple data analysis, and then close the database connection. This example 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 and your credentials. Use
the JDBC driver org.postgresql.Driver
to make the
connection.
Before R2024a:
setSecret
and getSecret
are not
available. Specify username and password using character vectors or
strings.
Use the URL defined by the driver vendor including your server name
host
, port number, and database name.
databasename = "dbname"; setSecret("usernamepsql"); setSecret("passwordpsql"); driver = "org.postgresql.Driver"; url = "jdbc:postgresql://host:port/dbname"; conn = database(databasename,getSecret("usernamepsql"),getSecret("passwordpsql"),driver,url)
conn = connection with properties: DataSource: 'dbname' 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 configurationCatalog and Schema Information
— Names of catalogs and schemas in the databaseDatabase 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)
Connect to MySQL Using ODBC Driver with Additional Options
Connect to the MySQL® database using an ODBC driver. Then, import data from the database into MATLAB®, perform simple data analysis, and then close the database connection. The example assumes that you are connecting to the MySQL database version 5.7.22 and MySQL ODBC 5.3 ANSI driver.
Create a database connection to a MySQL database and a login timeout of 5 seconds.
Before R2024a:
setSecret
and getSecret
are not
available. Specify username and password using character vectors or
strings.
databasename = "toystore_doc"; setSecret("usernamemysql"); setSecret("passwordmysql"); conn = database(databasename,getSecret("usernamemysql"),getSecret("passwordmysql"),'Vendor','MySQL', ... 'Server','dbtb01','PortNumber',3306,'LoginTimeout',5)
conn = connection with properties: DataSource: 'toystore_doc' UserName: '' Driver: 'com.mysql.cj.jdbc.Driver' URL: 'jdbc:mysql://dbtb01:3306/ ...' Message: '' Type: 'JDBC Connection Object' Database Properties: AutoCommit: 'on' ReadOnly: 'off' LoginTimeout: 5 MaxDatabaseConnections: 0 Catalog and Schema Information: DefaultCatalog: 'toystore_doc' Catalogs: {'detsdb', 'information_schema', 'mysql' ... and 4 more} Schemas: {} Database and Driver Information: DatabaseProductName: 'MySQL' DatabaseProductVersion: '5.7.22' DriverName: 'MySQL Connector/J' DriverVersion: 'mysql-connector-java-8.0. ...'
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 configurationCatalog and Schema Information
— Names of catalogs and schemas in the databaseDatabase 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);
head(data,3)
ans=3×4 table
productNumber Quantity Price inventoryDate
_____________ ________ _____ _______________________
1 1700 14.5 {'2014-09-23 09:38:34'}
2 1200 9 {'2014-07-08 22:50:45'}
3 356 17 {'2014-05-14 07:14:28'}
Determine the highest quantity in the table.
max(data.Quantity)
ans = 9000
Close the database connection.
close(conn)
Input Arguments
datasource
— Data source name
character vector | string scalar
Data source name, specified as a character vector or string scalar. Specify the name of an existing data source.
Example: "myDataSource"
Data Types: char
| string
databasename
— JDBC database name
character vector | string scalar
JDBC database name, specified as a character vector or string scalar. Specify the name of your database to create a database connection using a JDBC driver.
The name differs for different database systems. For example,
databasename
is the SID or the service name when you
are connecting to an Oracle® database. Or, databasename
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
username
— User name
character vector | string scalar
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
— Password
character vector | string scalar
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
Param1,ParamValue1,...,ParamN,ParamValueN
— JDBC driver parameters
name-value pair arguments
JDBC driver parameters, specified as multiple name-value pair arguments. A
Param
argument is a character vector or string scalar
that specifies the name of a JDBC driver parameter. A
ParamValue
argument is a character vector, string
scalar, or numeric scalar that specifies the value of the JDBC driver
parameter.
Param Valid Values | Param Value Description | ParamValue Valid Values |
---|---|---|
"Vendor" | Database vendor |
If you are connecting to a database system not
listed here, use the |
"Server" | Database server name or address |
|
"PortNumber" | Server port number where the server is listening | Numeric scalar |
"AuthType" | Authentication type (required only for Microsoft® SQL Server®) |
|
"DriverType" | Driver type (required only for Oracle) |
|
Tip:
When creating a JDBC connection using the JDBC driver parameters, you can omit the following:
'Server'
parameter when connecting to a database locally'PortNumber'
parameter when connecting to a database server listening on the default port (except for Oracle connections)
Example: 'Vendor','Microsoft SQL
Server','Server','dbtb04','AuthType','Windows','PortNumber',54317
connects to a Microsoft
SQL Server database using a JDBC driver on a machine named
dbtb04
with Windows authentication and using port number
54317
.
Example: 'Vendor','MySQL','Server','remotehost'
connects
to a MySQL database using a JDBC driver on a machine named
remotehost
.
driver
— JDBC driver name
character vector | string scalar
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
url
— Database connection URL
character vector | string scalar
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 Arguments
Specify optional pairs of arguments as
Name1=Value1,...,NameN=ValueN
, where Name
is
the argument name and Value
is the corresponding value.
Name-value arguments must appear after other arguments, but the order of the
pairs does not matter.
Before R2021a, use commas to separate each name and value, and enclose
Name
in quotes.
Example: 'LoginTimeOut',5,'ErrorHandling','report'
specifies waiting for 5
seconds to connect to a database before throwing an error and displaying any error
messages at the command line.
AutoCommit
— Flag to autocommit transactions
'on'
(default) | 'off'
Flag to autocommit 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.
Example: 'AutoCommit','off'
LoginTimeout
— Login timeout
0
(default) | positive numeric scalar
Login timeout, specified as the name-value argument 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
ReadOnly
— Read-only database data
'off'
(default) | 'on'
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'
ErrorHandling
— Error handling
'store'
(default) | 'report'
Error handling, specified as the comma-separated pair consisting of
'ErrorHandling'
and one of these values:
'store'
— Store an error message in theMessage
property of theconnection
object.'report'
— Display an error message at the command line.
DriverManager
— Driver manager for macOS platform
'unixODBC'
(default) | 'iODBC'
Since R2023b
Driver manager for macOS platform, specified as 'unixODBC'
or
'iODBC'
. For more information, see Configuring an ODBC Driver on Windows, macOS, and Linux at
devart.com
. The ODBC driver manager manages
communication between apps and ODBC drivers. All the drivers that ship
with MATLAB depend on unixODBC
. If you use your own
driver, refer to your driver manual to determine which driver manager to
use.
Example: DriverManager=unixODBC
Output Arguments
conn
— Database connection
connection
object
Database connection, returned as an ODBC connection
object or JDBC connection
object.
More About
JDBC Driver Name and Database Connection URL
The JDBC driver name and database connection URL take different forms for different databases. For details, consult your database driver documentation.
Database | JDBC Driver Name and Database URL Example Syntax |
---|---|
IBM® Informix® | JDBC driver: Database URL: |
Microsoft SQL Server 2005 | JDBC driver: Database URL: |
MySQL | JDBC driver:
Database URL:
For MySQL Connector 8.0 and later: JDBC driver:
For previous versions of MySQL Connector: JDBC
driver:
Database URL:
To
insert or select characters with encodings that are not default,
append the value
The
trailing |
Oracle oci7 drivers | JDBC driver: Database URL: |
Oracle oci8 drivers | JDBC driver: Database URL: Database URL: |
Oracle 10 Connections with JDBC (Thin drivers) | JDBC driver: Database URL: |
Oracle Thin drivers | JDBC driver: Database URL: Database URL: |
PostgreSQL | JDBC driver: Database URL: |
PostgreSQL with SSL Connection | JDBC driver: Database URL: The
trailing |
Teradata® | JDBC driver:
Database URL:
|
Tips
If you specify a data source name in the
datasource
input argument that appears on both ODBC and JDBC data source lists, then thedatabase
function creates an ODBC database connection. In this case, if you must create a JDBC database connection instead, append_JDBC
to the name of the data source.
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.
Version History
Introduced before R2006aR2020b: database.ODBCConnection
syntax has been removed
The database.ODBCConnection
syntax has been removed. Use the
syntaxes of the database
function instead. Some
differences between the workflows require updates to your code.
In prior releases, you created a connection to a database by using the
database.ODBCConnection
syntax. For example:
conn = database.ODBCConnection(datasource,username,password);
Now use the database
syntax instead.
conn = database(datasource,username,password);
See Also
Functions
Apps
Topics
- Setup Requirements for Database Connection
- Connection Options
- Configure Driver and Data Source
- Microsoft SQL Server ODBC for Windows
- Microsoft SQL Server JDBC for Windows
- PostgreSQL JDBC for Windows
- Connect to Database
- Import Data from Database Table Using sqlread Function
- Insert Data into Database Table
- Retrieve Database Metadata
- Database Connection Error Messages
Comando de MATLAB
Ha hecho clic en un enlace que corresponde a este comando de MATLAB:
Ejecute el comando introduciéndolo en la ventana de comandos de MATLAB. Los navegadores web no admiten comandos de MATLAB.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list:
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)