meta data for this page
  •  

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
applications:connect_matlab_mac [2022/05/26 21:12] – gregbalcoapplications:connect_matlab_mac [2022/05/26 22:24] (current) – gregbalco
Line 1: Line 1:
-= MATLAB database connectivity for Mac (and also Linux, it is basically the same) == +== 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+This page has instructions for how to connect MATLAB to the ICE-D MySQL database on a *nix system. 
  
 Basically, this has two parts.  Basically, this has two parts. 
Line 13: Line 13:
 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 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. +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 (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. +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 == == Setting up SSH tunnel ==
Line 21: Line 21:
 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:  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 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) +  * 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)+  * your login to the SSH tunnel host is 'iced' (this is the real one)
  
 Then issue the following command in a terminal window: Then issue the following command in a terminal window:
Line 29: Line 29:
 ''ssh -f iced@ssh.tunnel.host.org -L 12345:123.456.789.10:3306 -N &'' ''ssh -f iced@ssh.tunnel.host.org -L 12345:123.456.789.10:3306 -N &''
  
-What this does is forward 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. +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. 
  
-Note that this won't work 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. +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. 
  
-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. Even if you do nothing, it will eventually die next time your computer goes to sleep, but it is bad practice to leave zombie processes all over the place. Unfortunately, shutting it off is a bit complicated. First, you have to identify the process that is running SSH. To do this, type in the terminal:+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'' ''ps aux | grep 3306''
Line 39: Line 41:
 This will generate a bunch of information that will look something like this: This will generate a bunch of information that will look something like this:
  
-''balcs             7966   0.0  0.0  4268344    728 s001  S+   11:01PM   0:00.00 grep 3306 +<code> 
-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''+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: 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:
Line 50: Line 54:
 == Connecting MATLAB to the database through the SSH tunnel ==  == 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 that you have already opened. 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:+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|}} {{:applications:screen_shot_2022-05-26_at_1.57.28_pm.png?400|}}
Line 62: Line 66:
 ''dbc = database('new_ICED_reader_through_tunnel','reader','beryllium-10')'' ''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 in a MATLAB script. Try:+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:
  
-''fetch(dbc,'select name,lat_DD,lon_DD from base_sample where name = "10-MPS-001-COU"')''+''query_result = fetch(dbc,'select name,lat_DD,lon_DD from base_sample where name = "10-MPS-001-COU"')''
  
 This should return: This should return:
  
  
-''ans =+<code> 
 +query_result =
  
   1×3 table   1×3 table
Line 77: Line 82:
  
     {'10-MPS-001-COU'}    -83.285    -57.975     {'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.  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. 
Line 85: Line 90:
 ''close(dbc)'' ''close(dbc)''
  
-To learn more about this, look at the MATLAB documentation for the commands 'database' and 'table'. The MATLAB 'table' is kind of a weird construct that has to be deconstructed to be useable as numerical data. +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.