== MATLAB database connectivity for Mac (and also Linux, it is basically the same) == 

This page has instructions for how to connect MATLAB to the ICE-D MySQL database on a *nix system. 

Basically, this has two parts. 

First, configure the database connectivity aspects of MATLAB. Once you have done this, MATLAB can function as a MySQL client just as HeidiSQL, Sequel Pro, MySQL workbench, etc., with the added benefit that you can include database queries in MATLAB scripts. 

Second, connect MATLAB to the remote database. The main difference between using a typical MySQL client and MATLAB is that MATLAB doesn't internally handle the case where you are connecting through a database using an SSH tunnel. Thus, you have to configure the SSH tunnel externally to MATLAB and then point MATLAB to it. 

== Configuring MATLAB database toolbox ==

The first step here is to install the MATLAB 'Database Toolbox'. How to do this depends on your personal or institutional licensing setup for MATLAB. If you have an individual license, you will have to buy the toolbox. If you are working through a university license, typically it is included but not installed, so you have to figure out how to download and install it on your machine. Precisely how to do this varies with the version of MATLAB that you are using. 

The second step is to locate and open the MATLAB help documentation for the database toolbox. This is also available online at mathworks.com. This will include instructions for how to install a 'JDBC driver' for MySQL. The online version of this as of 20220526 is [[https://www.mathworks.com/help/database/ug/configure-driver-and-data-source.html|here]]. Most likely, this will point you to [[https://dev.mysql.com/downloads/connector/j/|this page]], where you will download the 'platform independent' driver file. 

Once you have downloaded the JDBC driver (once it is unzipped/untarred it is a '.jar' file) and stored it somewhere on your computer, you need to tell MATLAB where to find it. This procedure varies with MATLAB version. On current MATLAB (R2022), the procedure is [[https://www.mathworks.com/help/database/ug/mysql-jdbc-for-macos.html|here]], although so far you don't have enough information to fill out all the boxes in the 'create data source' dialog. On older versions, this procedure involves adding to the MATLAB 'javaclasspath' variable to tell MATLAB where to locate the file. 

== Setting up SSH tunnel ==

Unlike other GUI MySQL clients, the setup dialog in the MATLAB 'database explorer' doesn't have an internal ability to connect through an SSH tunnel. Thus, you have to set up the SSH tunnel externally. To do this, open a Terminal window. You are going to run a terminal command to start the tunnel. Suppose the following: 

  * The ICE-D MySQL server is located at 123.456.789.10 (this is a made-up address, not the real one)
  * The SSH tunnel host has the domain name ssh.tunnel.host.org (also made-up, not the real one)
  * your login to the SSH tunnel host is 'iced' (this is the real one)

Then issue the following command in a terminal window:

''ssh -f iced@ssh.tunnel.host.org -L 12345:123.456.789.10:3306 -N &''

What this does is connect port 3306 (the default MySQL port) on the remote MySQL server, through the SSH tunnel host, to localhost port 12345 on the local machine. Thus, it will appear to a database client (such as MATLAB in this case) that the database is located at localhost:12345. The '&' tells the command to run in the background and return the command line. 

The port number 12345 is (obviously) arbitrary. You can use anything that is not already in use on your machine. If a port is in use, your computer will behave oddly. Mostly, high-numbered (5 digits) ports are OK. 

Note that this won't work as I have written it here because the SSH host and database IP address don't exist. You'll need to put in the correct values. If you have already been using a different MySQL client, you know what these are. If not, ask Greg or Joe. 

One additional item of importance is that when you are done with this,  you will have to shut down the SSH process that is keeping the tunnel open. If you don't, it will eventually die next time your computer goes to sleep, but it is bad practice to leave zombie processes all over the place. And, in addition, if you have multiple alive and dead processes that are trying to use the same localhost port, that will eventually cause trouble. Shutting it off is very UNIX-y. First, you have to identify the process that is running SSH. To do this, type in the terminal:

''ps aux | grep 3306''

This will generate a bunch of information that will look something like this:

<code>
balcs             7966   0.0  0.0  4268344    728 s001  S+   11:01PM   0:00.00 grep 3306
balcs             7652   0.0  0.0  4352368    852   ??  Ss   10:51PM   0:00.01 ssh -f iced@ssh.tunnel.host.org -L 12345:3123.446.789.10:3306 -N -v
</code>

The second line tells you that the SSH process is process 7652 (everything that is running on a *nix system has its own process number). You can shut it down by typing:

''kill 7652''

Careful not to mistype and start killing off other random processes.

== Connecting MATLAB to the database through the SSH tunnel == 

Now that you have an ssh tunnel open to the database, you need to point MATLAB to it. On current MATLAB, it appears the easiest way to do this is to use the 'JDBC data source configuration' dialog, which can be opened from the 'Database explorer' as indicated in the MATLAB help described above, or can be invoked from the MATLAB command line by typing 'configureJDBCDataSource.' What this does is create a persistent description of a database connection that you can refer to by name from the command line. So, for example, this:

{{:applications:screen_shot_2022-05-26_at_1.57.28_pm.png?400|}}

will create a data source description with the name 'new_ICED_reader_through_tunnel' (the name is arbitrary, you can call it whatever you want) and the correct connection parameters to point to the local end of the SSH tunnel. The 'Driver location' box will be wherever you put the .jar driver file you installed earlier. 

If you click 'Test' you'll be asked for a username and password. Use the default read-only login of 'reader' and 'beryllium-10'. Ideally, you'll get a 'connection successful.'

Now having created a data source description, you can use it in MATLAB code. Close all the 'database explorer' windows. Now at the MATLAB command line, try this:

''dbc = database('new_ICED_reader_through_tunnel','reader','beryllium-10')''

This should return a bunch of information about the database connection. You now have a live connection to the database that can be accessed by additional MATLAB commands. Try:

''query_result = fetch(dbc,'select name,lat_DD,lon_DD from base_sample where name = "10-MPS-001-COU"')''

This should return:


<code>
query_result =

  1×3 table

           name           lat_DD     lon_DD 
    __________________    _______    _______

    {'10-MPS-001-COU'}    -83.285    -57.975
</code>

What you have just done is send an SQL command to the database and obtain a result as a MATLAB variable that you can now work with in MATLAB. 

Finally, close the database connection:

''close(dbc)''

If everything is working as intended, the 'new_ICED_reader_through_tunnel' data source description should be persistent through MATLAB sessions on the same computer. Of course, if you include this in MATLAB code and you are trying to run it on a different computer, you will have to use a data source description that exists on the new computer. Thus, when writing code that uses database connections that you're going to be giving to someone else, put the database connection commands someplace obvious where they can be easily changed. 

To learn more about this, look at the MATLAB documentation for the commands 'database', 'fetch', and 'table'. The MATLAB 'table' is kind of a weird construct that has to be deconstructed to be useable as numerical data.