Databases with MySQL

Accessing raw databases can be messy. An easy interface to databases is provided by the SQL-language. This is a language that is understood by a SQL-server. There are several commercial SQL-servers, e.g. Oracle, which are rather expensive. For our purposes it is, however, sufficient to use a SQL-server that does not require a licensing fee for universities. MySQL is a very fast and well documented server.

Simple programs that interact with a MySQL-database can be written with php. Some examples are given in sections 3.0.10, 3.0.11, and 3.0.12. More complex programs should be written with C. An API for C comes with the MySQL distribution and is documented there.

Using a SQL-server allows you also to physically separate the www-server from the database. You might have several www-servers (in different places) that all access a single SQL-server. If parts of your lab are connected with slow lines this may enhance efficiency.

The following C-fragment connects to a database, executes a single SQL-query (SELECT COUNT(*) FROM users) and stores the result of the query in the variable row.

#define SQL_PASS "secret"
#define SQL_BASE "my_database"
#define SQL_USER "my_user"

MYSQL mysql;
MYSQL_RES *sql_res;
MYSQL_ROW row;

mysql_connect(&mysql,sql_server,SQL_USER,SQL_PASS);
mysql_select_db(&mysql,SQL_BASE));
mysql_query(&mysql,"SELECT COUNT(*) FROM users");
sql_res = mysql_store_result(&mysql);
row = mysql_fetch_row(sql_res));
mysql_free_result(sql_res);

For such a simple query the fragment looks quite impressive. We should note, however, that much more complex queries can be done with almost as little effort. Further, the SQL-server takes full care of opening and closing of files, locking them correctly etc.