Tuesday, April 08, 2008

MySQL Proxy Recipes - Returning a non dataset result

Working with MySQL Proxy, besides returning a dataset and an error you could also return a status of successfully executed query, without a dataset being involved. For example, every data modification query (INSERT, UPDATE, DELETE, CREATE ..., DROP ...) returns such a result.
The procedure is similar to returning an error. You must return a different response type and fill in the appropriate fields.
function affected_rows (rows, id)
proxy.response.type = proxy.MYSQLD_PACKET_OK
proxy.response.affected_rows = rows
proxy.response.insert_id = id
return proxy.PROXY_SEND_RESULT
If you call the above function with
 return affected_rows(3000,20)
your client will receive
Query OK, 3000 rows affected (0.01 sec)
Notice that you can't check the LAST_INSERT_ID with MySQL command line client, but you must use an API call from your favorite language to access that value.

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.

No comments: