Use MySQL with Erlang

Use MySQL with Erlang

If you want to get your manager to try out Erlang, but they are hesitant (probably an understatement) you might be able to get some leverage if you use a relational database rather than mnesia. So, here's a quick sample of how to connect to and query a MySQL database.

First, you'll need to install the MySQL ODBC driver if you haven't already. You can find it at http://dev.mysql.com/downloads/connector/odbc/5.1.html. Then you'll need a database to connect to and a table to query. Here's the SQL script I used to create my test database:

CREATE DATABASE `test`;

CREATE TABLE `test`.`test_table`
(
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(45) NOT NULL,
  `last_name` VARCHAR(45) NOT NULL,
  PRIMARY (`id`)
)
ENGINE = InnoDB;

INSERT INTO `test`.`test_table`
(
  `first_name`,
  `last_name`
)
VALUES
(
  'Matt',
  'Williamson'
),
(
  'Matt',
  'Williamson2'
),
(
  'Matt',
  'Williamson3'
);

And here's the code to connect and query in erlang:

application:start(odbc).
ConnString = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=test; User=root;Password=ace152;Option=3;".
{ok, Conn} = odbc:connect(ConnString, []).
Results = odbc:sql_query(Conn, "SELECT * FROM test_table").

This is what my interactive session looks like:

Erlang (BEAM) emulator 5.5.5 [async-threads:0]

Eshell V5.5.5  (abort with ^G)
1> application:start(odbc).
ok
2> ConnString = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=test;
User=root;Password=ace152;Option=3;".
"Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=test; User=root;Passwo
rd=ace152;Option=3;"
3> {ok, Conn} = odbc:connect(ConnString, []).
{ok,<0.39.0>}
4> Results = odbc:sql_query(Conn, "SELECT * FROM test_table").
{selected,["id","first_name","last_name"],
          [{1,"matt","williamson"},
           {2,"matt","williamson2"},
           {3,"matt","williamson3"}]}
5>

It's that simple. Post a comment if you want more samples or description.