How do I connect to the database with the JDBC driver?

How do I access the database with a different type of driver?

Answer 1

[MathWorks Support Team]

In this article, We will show you how to set up a JDBC database that connects to 1. Microsoft SQL server, 2. Oracle, 3. My SQL

1. Microsoft SQL servers

1-1) Download the latest JDBC driver that is appropriate for your database.

https://learn.microsoft.com/ja-jp/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15

1-2) See the documentation of the driver for setting up the driver and creating database connection objects.

2. ORACLE

2-1) Download the latest JDBC driver for your database.

https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html

2-2) Run the following command to open $MATLAB\toolbox\local\classpath.txt in the MATLAB editor.

edit classpath.txt

2-3) Add the full path of the database driver file to the last line.

Example:

C:\Drivers\Oracle\ojdbc6.jar

2-4) Save the file and restart MATLAB.

2-5) Run the following command to connect to the database.

% 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:');

3． MySQL

3-1) Download the latest JDBC driver (Conenector/J) below.

https://dev.mysql.com/downloads/connector/j/

3-2) Run the following command to open the $MATLAB\toolbox\local\classpath.txt in the MATLAB editor.

edit classpath.txt

* If you want to add a dynamic Sun Java class path to the driver, you can use the JAVAADDPATH function.

3-3) Add the full path of the database driver file to the last line.

Example:

C:\Drivers\MySQL\mysql-connector-java-5.1.12-bin.jar

3-4) Save the file and restart MATLAB.

3-5) Run the following command to connect to the database.

% Host: localhost, schema: test.
url = 'jdbc:mysql://localhost/test';
conn = database('databasename', 'username', 'password', 'com.mysql.jdbc.Driver', url);

4. Test

After setting the connection, you can perform connection test by the following script.

% 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

5. Related information

Setting Up Data Sources for Use with JDBC Drivers

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

database

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