MATLAB: How to use the Database Toolbox to connect to a database using a JDBC driver

511964blobclobDatabase Toolbox

I would like to know how to use different types of databases.

Best Answer

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:

http://social.msdn.microsoft.com/Search/en-US?query=SQL%20Server%20JDBC%20Driver%20download∾=3

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:

http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

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):

http://www.mysql.com/downloads/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

https://www.mathworks.com/help/releases/R2017a/database/ug/configuring-driver-and-data-source.html

Working with Visual Query Builder

https://www.mathworks.com/help/releases/R2016b/database/ug/getting-started-with-visual-query-builder.html

(Please note Visual Query Builder have been removed since R2017a)

database

http://www.mathworks.com/access/helpdesk/help/toolbox/database/ug/database.html

Best Answer

Related Question