odbc
Syntax
Description
creates a database connection to an ODBC data source with a user name and password. The
database connection conn
= odbc(datasource
,username
,password
)conn
is returned as an ODBC connection
object.
specifies options using one or more name-value arguments. For example,
conn
= odbc(datasource
,username
,password
,Name,Value
)'LoginTimeout',5
creates an ODBC connection with a login timeout of 5
seconds.
creates a connection to a database using a DSN-less connection string. (DSN is a data source
name.)conn
= odbc(dsnless
)
Examples
Connect to MySQL Using ODBC Database Connection
Connect to a MySQL® database using an ODBC database connection. Then, import data from the database into MATLAB®, perform a simple data analysis, and close the database connection.
This example assumes that you are connecting to a MySQL Version 5.7.22 database using the MySQL ODBC 5.3 Driver.
Create a database connection to a MySQL database. Specify the user name and password.
datasource = "MySQL ODBC"; conn = odbc(datasource,"root","matlab")
conn = connection with properties: DataSource: 'MySQL ODBC' UserName: 'root' 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 fetch
function. Display the first three rows of data.
query = "SELECT * FROM inventoryTable";
data = fetch(conn,query);
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 product quantity in the table.
max(data.Quantity)
ans = 9000
Close the database connection.
close(conn)
Connect to MySQL Using ODBC Database Connection with Additional Options
Connect to a MySQL® database using an ODBC data source and a timeout value. Then, import data from the database into MATLAB®, perform a simple data analysis, and close the database connection.
This example assumes that you are connecting to a MySQL Version 5.7.22 database using the MySQL ODBC 5.3 Driver.
Create a database connection to a MySQL database using an ODBC data source. Specify the user name and password. Also, specify a timeout value of 5 seconds for connecting to the database.
datasource = "MySQL ODBC"; username = "root"; password = "matlab"; conn = odbc(datasource,username,password,'LoginTimeout',5)
conn = connection with properties: DataSource: 'MySQL ODBC' UserName: 'root' Message: '' Type: 'ODBC Connection Object' Database Properties: AutoCommit: 'on' ReadOnly: 'off' LoginTimeout: 5 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 fetch
function. Display the first three rows of data.
query = "SELECT * FROM inventoryTable";
data = fetch(conn,query);
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 product quantity in the table.
max(data.Quantity)
ans = 9000
Close the database connection.
close(conn)
Connect to MySQL Using DSN-Less Connection
Connect to a MySQL® database using a DSN-less database connection. Then, import data from the database into MATLAB®, perform a simple data analysis, and close the database connection.
This example assumes that you are connecting to a MySQL Version 5.7.22 database using the MySQL ODBC 5.3 Driver.
Create a database connection to a MySQL database. Specify the connection string.
dsnless = strcat("Driver={MySQL ODBC 5.3 Ansi Driver}; Server=dbtb01;", ... "Database=toystore_doc; UID=root; PWD=matlab"); conn = odbc(dsnless)
conn = connection with properties: DataSource: '' 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 fetch
function. Display the first three rows of data.
query = "SELECT * FROM inventoryTable";
data = fetch(conn,query);
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 product quantity in the table.
max(data.Quantity)
ans = 9000
Close the database connection.
close(conn)
MySQL ODBC for macOS DSN-Less Connection
This example requires the download and installation of the
MySQL® ODBC driver from MySQL Community
Downloads. Verify the iODBC driver manager is installed in the path
/usr/local/iODBC
. If you need to install the iODBC driver manager,
you can download it from iodbc.org.
Connect to the database using the DSN-less connection string and the iODBC driver manager.
dsnless = "Driver=/usr/local/mysql-connector-odbc-8.0.30-macos12-x86-64bit/lib/libmyodbc8w.so;Server=dbtb04;Port=3306;UID=username;PWD=password;Database=toy_store" conn = odbc(dsnless,DriverManager="iODBC")
conn = connection with properties: DataSource: '' UserName: '' Message: '' Type: 'ODBC Connection Object' Database Properties: AutoCommit: 'on' ReadOnly: 'off' LoginTimeout: 0 MaxDatabaseConnections: 0 Catalog and Schema Information: DefaultCatalog: 'toy_store' Catalogs: {'information_schema', 'mysql', 'performance_schema' ... and 3 more} Schemas: {} Database and Driver Information: DatabaseProductName: 'MySQL' DatabaseProductVersion: '8.0.3-rc-log' DriverName: 'libmyodbc8w.so' DriverVersion: '08.00.0031'
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
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
dsnless
— DSN-less connection
character vector | string scalar
DSN-less connection string, specified as a character vector or string scalar. The connection string is specific to each database and usually contains connection parameters such as the database server name, port number, and database name. For details about the connection parameters of your database, see the database documentation.
This table shows some sample DSN-less connection strings for the Windows® and Linux® platforms. To use these samples, substitute your values for the corresponding connection parameters in the strings. The values might vary based on your database configuration.
Database | DSN-Less Connection String |
---|---|
Microsoft® SQL Server® | Windows — Linux — macOS —
|
MySQL | Windows — Linux — macOS —
|
PostgreSQL | Windows — Linux — macOS —
|
Data Types: char
| string
dm
— 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. The ODBC driver
manager manages communication between apps and ODBC drivers. All drivers that ship with
MATLAB® depend on unixODBC
. If using your own driver, refer to
your driver manual to determine which driver manager to use.
Example: dm = "unixODBC";
odbc(dsnless,DriverManager=dm)
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: conn =
odbc(datasource,username,password,'AutoCommit','off','ReadOnly','off')
creates a
database connection to an ODBC data source with a user name and password, and specifies that
database transactions must be committed to the database manually and the database data is
writeable.
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 a name-value argument consisting of a
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 the database does not support a login timeout, the function sets this value
to –1
.
Example: LoginTimeout=5
Data Types: double
ReadOnly
— Read-only database data
'off'
(default) | 'on'
Read-only database data, specified as one of these values:
'on'
— Database data is read-only.'off'
— Database data is writable.
Example: 'ReadOnly','on'
Limitations
The Linux and macOS platforms do not support the following:
select
functionODBC database connection using the Database Explorer app
MySQL ODBC driver 8.0 and higher
Version History
Introduced in R2021aR2023b: ODBC Support for macOS
Connect to database servers on a Mac using the shipped driver from MathWorks® or a downloaded driver.
Comando MATLAB
Hai fatto clic su un collegamento che corrisponde a questo comando MATLAB:
Esegui il comando inserendolo nella finestra di comando MATLAB. I browser web non supportano i comandi 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)