Connecting to the env database from MATLAB

From Glacsweb Wiki
Revision as of 00:41, 5 February 2014 by Dsy1e13 (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
Just once

Check for the JDBC driver at (typically) C:\Program Files (x86)\MySQL\MySQL Connector J\mysql-connector-java-5.1.28-bin.jar. Might be a more recent version. If it's not there, you can install it by running \\env.ecs.soton.ac.uk\glacsweb-media\software\mysql-connector-java-gpl-5.1.28.msi or by downloading it from http://mysql.com. If you can't access the samba share by name try it using IP. It doesn't matter where the jar file goes, so if you don't have admin privileges put it somewhere in your own file space.

Edit javaclasspath.txt in your MATLAB preferences directory. In MATLAB the command is

    edit(fullfile(prefdir, 'javaclasspath.txt'))

If the file doesn't exist you'll get a dialogue box asking if you want to create it. Say yes.

Add a comment and the location of the driver at the end of the file, e.g.

    # JDBC connector
    C:\Program Files (x86)\MySQL\MySQL Connector J\mysql-connector-java-5.1.28-bin.jar

Save the file and restart MATLAB.

If you want to use the Database Explorer

In MATLAB, give the command

    dexplore

You should get a message to the effect that no data sources have been set up. Click OK.

(If instead, you get an error message saying your configuration file is missing, exit dexplore and give the command

    setdbprefs('JDBCDataSourceFile', '');

then restart dexplore.)

In Database Explorer click New > JDBC. In the dialogue box enter iceland for the Data Source Name, MYSQL for the vendor, env.ecs.soton.ac.uk for the Server Name, leave the port number at the default, enter your userid and password and iceland again for the database. Click Test to check it works, then Save. Accept the default filename, jdbcConfig.mat, but make sure that the file is going somewhere that will be on your MATLAB path when you want to use the database (this should normally be the case for the directory you are working in.)

You should now be able to enter your password in dexplore and see the database tables, preview data etc. The configuration data has been saved so you don't have to do it again.

After that

You can continue to use the Database Explorer, which can import data under manual control.

To get a connection from a program, give the command

    conn = database('iceland', 'USERID', 'PASSWORD', 'Vendor', ...
       'MySQL', 'URL', 'jdbc:mysql://env.ecs.soton.ac.uk/iceland');

(You don't need all the dexplore setup to do this.) The command

    isconnection(conn)

should return 1. Then you can launch into SQL, e.g.

    query = 'select timestamp, temperature from hofn_weather';
    data = fetch(conn, query);

Useful tip: the Database Explorer has a code generation option, which is helpful if it's working but you can't figure out how to get data back to your program.