MATLAB Answers

How can I use the Database Toolbox to connect to a database using a JDBC driver?

5 views (last 30 days)
I would like to know how to use different types of databases.

Accepted Answer

MathWorks Support Team
MathWorks Support Team on 10 May 2021
Edited: MathWorks Support Team on 10 May 2021
The following describes how to setup JDBC database connections to Microsoft SQL Server, Oracle and MySQL. MATLAB code for a simple test and other related information can be found further below.
________________________________________
Microsoft SQL Server
________________________________________
1. Download the latest JDBC driver for your database:
2. Refer to the documentation for information on how to set up the driver and create a database connection object.
________________________________________
ORACLE
________________________________________
1. Download the latest JDBC driver for your database:
2. Open the file "$matlabroot\toolbox\local\classpath.txt" in the MATLAB Editor by executing the following command:
edit classpath.txt
3. Scroll down to the end of the file and add the full path to the database driver file, for example as follows:
C:\Drivers\Oracle\ojdbc6.jar
4. Save the file and restart MATLAB.
5. Open the database connection as follows:
% Syntax: database('databasename','username','password','driver','databaseurl')
% 111.222.333.444:1521 represents the IP address and port of the server.
conn = database('databasename','scott','tiger','oracle.jdbc.driver.OracleDriver','jdbc:oracle:thin:@111.222.333.444:1521:');
________________________________________
MySQL
________________________________________
1. Download the latest JDBC driver (Connector/J):
2. Open the file "$matlabroot\toolbox\local\classpath.txt" in the MATLAB Editor by executing the following command:
edit classpath.txt
Note: Alternatively, you can also use the function JAVAADDPATH to add the driver to the dynamic Sun Java class path.
3. Scroll down to the end of the file and add the full path to the database driver file, for example as follows:
C:\Drivers\MySQL\mysql-connector-java-5.1.12-bin.jar
4. Save the file and restart MATLAB.
5. Open the database connection as follows:
% Host: localhost, schema: test.
url = 'jdbc:mysql://localhost/test';
conn = database('databasename', 'username', 'password', 'com.mysql.jdbc.Driver', url);
________________________________________
Simple test
________________________________________
After opening the connection as explained above, you can run a simple test to retrieve some data as follows:
% Open DB connection here.
% [...]
% Test the connection.
ping(conn)
if isconnection(conn)
% SQL query to get all fields from Table1.
curs = exec(conn,'SELECT * FROM Table1');
curs = fetch(curs);
data = curs.data; % Actual data.
% Update Table1.
% UPDATE TABLE1 SET Field1=1 WHERE Field2=1
% UPDATE TABLE1 SET Field1=2 WHERE Field2=2
% UPDATE TABLE1 SET Field1=3 WHERE Field2=3
update(conn,'Table1',{'Field1'},{1; 2; 3},{'WHERE Field2=1'; 'WHERE Field2=2'; 'WHERE Field2=3'});
% Update Table1.
% UPDATE TABLE1 SET Field1=1 WHERE Field3='aaa'
% UPDATE TABLE1 SET Field1=2 WHERE Field3='bbb'
% UPDATE TABLE1 SET Field1=3 WHERE Field3='ccc'
update(conn,'Table1',{'Field1'},{1; 2; 3},{'WHERE Field3=''aaa'''; 'WHERE Field3=''bbb'''; 'WHERE Field3=''ccc'''});
% Insert data into Table1.
exec(conn,'INSERT INTO Table1(Field1, Field2, Field3) VALUES(4, 4, ''ddd'')');
% Delete data from Table1
exec(conn,'DELETE FROM Table1 WHERE Field3=''ddd''');
% Add a records using FASTINSERT.
fastinsert(conn,'Table1',{'Field1', 'Field2', 'Field3'}, {1, 1, 'aaa'; 2, 2, 'bbb'; 3, 3, 'ccc'});
% Clean up.
close(conn);
end %if
________________________________________
Related information
________________________________________
About configuring Driver and Data Sources
Working with Visual Query Builder
(Please note Visual Query Builder have been removed since R2017a)
database

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!