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