Tuesday, March 25, 2008

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.

1 comment:

Unknown said...

I had to change the line

return make_dataset
to
return proxy.global.make_dataset

in order to have it working (unless I missed some nesting code)