S h o r t S t o r i e s

// Tales from software development

Archive for May 2013

MySQL Connector/ODBC 3.5.1.30 and CommandBehavior.KeyInfo

leave a comment »

Some time ago I wrote a query tool that connects to databases using ODBC. The tool is intended to be database agnostic and is used on a daily basis against both MySQL and Microsoft SQL Server databases.

However, it’s recently exhibited some odd behaviour when running queries against MySQL – a single row is returned even when the resultset is larger than one row.

Adding a LIMIT clause to the query caused all the rows or at least the number of rows specified to be returned. Further investigation showed that the problem only occurred when version 3.51.1.30 of MySQL Connector/ODBC was installed. Earlier versions don’t show this behaviour and instead all rows in the resultset are returned.

It looked like a bug in 3.51.1.30 so I logged it with the MySQL development team. However, it soon became apparent that the problem is a change in 3.51.1.30 that is intended to correctly handle the CommandBehaviour.KeyInfo option that I was using in the call to ExecuteReader():

OdbcCommand command = new OdbcCommand(sql, this.connection);
command.CommandType = CommandType.Text;
command.CommandTimeout = Settings.Default.CommandTimeout;
OdbcDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);

The intent was to retrieve extended information about the resultset. During development of the application, specifying this option appeared to have provided the additional information when executing queries against Microsoft SQL Server. According to the MySQL technician who dealt with the bug report, this option was implemented to retrieve metadata from Microsoft SQL Server but, as of version 3.51.1.30, MySQL Connector/ODBC is now taking account of it too. Unfortunately, the MySQL developers have decided to handle it in a different way to the Microsoft SQL Server development team. In short, the option adds metadata to the resultset without changing the number or rows returned when executing against a Microsoft SQL Server database but Connector/ODBC will always return only one row when this option is set regardless of how many rows the query would otherwise return.

It’s difficult not to consider this a bug although the MySQL development team don’t agree. If the MySQL development team acknowledge that the option was intended for Microsoft SQL Server but they have now decided to handle it as well, what is the logic behind handling it in a different manner to Microsoft SQL Server ?

Written by Sea Monkey

May 10, 2013 at 8:00 pm

Posted in Debugging, Development

Tagged with , , ,