Recently, I have been working on a data pipeline that updates a MySQL database. We have been using Java simply to access the database and I thought I would simplify our toolchain by removing the dependency on Java by updating the database directly from IDL where we already have all the data that is needed to populate the tables.

So I have been adding MySQL bindings to mglib in the last week. I have also added a higher-level interface to the straight wrappers of the MySQL C routines.

As an example of using the bindings, let’s connect to a database running locally on my machine, i.e., host is localhost:

IDL> db = MGdbMySQL()
IDL> db->connect, user='mgalloy', password='passwd', database='testdb'

There is also a CONFIG_FILENAME keyword to ::connect that allows the database details and authentication to be stored in a configuration file instead of passed directly in as keywords.

After connection has been established, we can query for various properties of the connection:

IDL> db->getProperty, proto_info=proto_info, $
IDL> host_info=host_info, $
IDL> server_info=server_info, $
IDL> server_version=server_version
IDL> print, proto_info, format='(%"Proto info: %d")'
Proto info: 10
IDL> print, host_info, format='(%"Host info: %s")'
Host info: Localhost via UNIX socket
IDL> print, server_info, format='(%"Server info: %s")'
Server info: 5.6.24
IDL> print, server_version, format='(%"Server version: %d")'
Server version: 50624

We can also query for the available databases matching a pattern (in SQL patterns, % matches anything):

IDL> database_query = '%'
IDL> databases = db->list_dbs(database_query, n_databases=n_databases)
IDL> print, database_query, $
IDL> n_databases eq 0 ? '' : strjoin(databases, ', '), $
IDL> format='(%"Databases matching ''%s'': %s\n")'
Databases matching '%': information_schema, test, testdb

Or find the tables with names starting with “C”:

IDL> table_query = 'C%'
IDL> tables = db->list_tables(table_query, n_tables=n_tables)
IDL> print, table_query, n_tables eq 0 ? '' : strjoin(tables, ', '), $
IDL> format='(%"Tables matching ''%s'': %s\n")'
Tables matching 'C%': Cars

The ::query method returns an array of structures representing rows matching the selection:

IDL> car_results = db->query('select * from Cars', fields=fields)
IDL> print, fields.name, format='(%"%-3s %-10s %-6s")' & $
IDL> print, car_results, format='(%"%3d %10s %6d")'
Id Name Price
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600

We can handle blobs in our query result. They will be returned as pointers to byte arrays:

IDL> image_results = db->query('select * from Images', fields=fields)
IDL> mg_image, reform(*image_results[0].data, 288, 216), /new_window

To clean up, we should use heap_free on the array of structures we received from the ::query method to free the pointer to the image inside:

IDL> heap_free, image_results

And free the database as well:

IDL> obj_destroy, db

The source code for the MySQL bindings is available in mglib, as well as some examples of using the them.