Setting up database connection

Last modified by Pasi Kolari on 2024/02/08 11:02

Access to the database

To access the database and the tables inside you need a username and password. They can be found at

  • the internal wiki pages of INAR
  • ecophys group disk

If you have no access to those, send mail to atm-data(at)helsinki.fi

The database can be accessed from University network. Use VPN (uh-vpn-allroute) from outside the university.

Software configuration

Whatever software you are using, you'll probably need to use SQL commands, e.g. select * from table. You can seek help from the MySQL documentation:

Matlab (Windows & Linux)

Platform-independent JDBC interface is the most straightforward way to set up database connections. Get version 5.1.48 here (select platform-independent version from dropdown menu). Install instructions for Matlab:

  • Copy the JAR-file somewhere in your computer
  • Locate file startup.m in Matlab startup folder,  usually this is Documents/MATLAB, Matlab command "userpath" shows the startup folder. If the file does not exist, create new one. If you have no write access to the startup folder, change the folder, see Matlab help for "userpath". 
  • In startup.m, add line that tells Matlab the location of the JAR-file, like this:
    javaaddpath('C:/apps/mysql_java/mysql-connector-java-5.1.48-bin.jar') 

Note that there is (was?) known memory leak in the Java MySQL connector. It may get irritating if you run large number of data retrieval queries looped, better get the data in larger chunks. On the other hand, reading too large datasets leads to problems. If Matlab crashes or throws error when retrieving large datasets, try increasing the amount of RAM allocated to Java:

Preferences>General>Java Heap Memory

512 MB seems to allow downloading up to 7-8 variable years of 1-minute data. 10 variables is too much.

Here is a simple Matlab function to download variables and their metadata from selected days from database 'smear'. Calling the function without input arguments returns all table and variable metadata from the database.

You can also use custom made interface. You can compile it to both Windows and Linux. Here is the Windows version compiled with Matlab2010b and Microsoft VisualC 2010 Express. Here is a bit newer version with support for win64

  • Copy libmysql.dll to %matlabroot%\bin\win32 (or win64). You can get new an proper version from here
  • Copy rest of the files somewhere in Matlab path
R

Install RMySQL package, that's all!

Other software (Windows)

You can download data using several programs like Matlab, R, Excel, Mathematica, Igor .. with the ODBC interface.

  • (Ask helpdesk) Install Windows MySQL ODBC connector, latest version from here
  • Configure the interface
    • Control panel (- System and Security) - Administrative tools - Data Sources (ODBC)  - User DSN (or System DSN) - Add...
    • Select MySQL Unicode driver
    • Write data source name: smear
    • Write TCP/IP server: db5.csc.fi
    • port 3320
    • User and password see below
    • Select the database: smear
    • Test
    • In the programs you refer to the database with the data source name. I have used the database names as data source names.

In Excel you can use

  • Data - Get data from external data - From other sources - From Microsoft Query

One advanced tool for accessing the database is MySQL Workbench. It works without additional drivers.