MySQL Proxy recipes: returning a dataset

How do you return a dataset using MySQL Proxy?
This being a common task, as many that we will see in these pages, it is worth implementing with a function.
When you return a dataset, there are two main cases:

• Returning a single row with just one column. For example, when you need to give the user a simple message;
• Returning one or more rows with several columns. This is much more useful, and covers case like displaying help information, or creating a tabular set of information.

The first case is quite simple:

function simple_dataset (header, message)
proxy.response.type = proxy.MYSQLD_PACKET_OK
proxy.response.resultset = {
    fields = {
        {type = proxy.MYSQL_TYPE_STRING, name = header}
    },
    rows = {
        { message}
    }
}
return proxy.PROXY_SEND_RESULT
end

This function is, as required, quite simple. The dataset has just one column, and the name of the column is user defined.
Calling this function with return simple_dataset('from user', 'this message') will cause the Proxy to return a dataset to the client, as if it was received from the database server.
The client will receive:

+--------------+
| from user    |
+--------------+
| this message |
+--------------+

A multiple column dataset requires some more work. You need to pass an array for the headers, and a two-dimensional array for the values.

function proxy.global.make_dataset (header, dataset)
  proxy.response.type = proxy.MYSQLD_PACKET_OK

  proxy.response.resultset = {
      fields = {},
      rows = {}
  } 
  for i,v in pairs (header) do
      table.insert(
        proxy.response.resultset.fields,
            {type = proxy.MYSQL_TYPE_STRING, name = v})
  end
  for i,v in pairs (dataset) do
      table.insert(proxy.response.resultset.rows, v )
  end
  return proxy.PROXY_SEND_RESULT
end

Using this function is straightforward. For example, to return a simple help for some custom made commands, we can do:

return make_dataset(
  {'command', 'description' },      -- the header
  {                                 -- the rows
    {'FOO', 'removes the database'},
    {'BAR', 'drops all tables'},
    {'FOOBAR', 'makes the server explode'},
  }
)

The result on the client is

+---------+--------------------------+
| command | description              |
+---------+--------------------------+
| FOO     | removes the database     |
| BAR     | drops all tables         |
| FOOBAR  | makes the server explode |
+---------+--------------------------+

If you keep these two functions at hand, your Proxy scripts will become more manageable.

This post is part of a set of recipes that will eventually become a long article.
Want to hear more? Attend MySQL Proxy - The complete tutorial at the MySQL Users Conference 2008.