Tired of bugs in Microsoft's SQL Server native client ODBC Driver

Yet more time today spent tracking down another bug in Microsoft's SQL Server native client ODBC driver reported in RT for DBD::ODBC.

RT 22018 error (ignore the initial problem as the reporter tagged a new issue onto the end of a different issue).

The real problem (and I've seen it before) is that Microsoft's SQL Server ODBC drivers (the original or the new native client one) don't support the ODBC API SQLDescribeParam too well and DBD::ODBC really needs SQLDescibeParam to work (especially for the columns other than SQL_VARCHAR).

What does SQLDescribeParam do? Well, if you execute a parameterized query such as "select * from my_table where column1 = ? and column2 = ?" you can call SQLDescribeParam to find information about the parameters (2 in this case). It will tell you things like the parameter type, size, decimal digits, if it is nullable etc and the parameter type and size are especially useful to DBD::ODBC. The problem with the SQL Server ODBC drivers is that in order to support SQLDescribeParam they attempt to rearrange your SQL to do a select on the columns which allows it to find out about the parameters e.g., with the above SQL it ends up doing something like "select column1, column2 from my_table" then it can look at the column types described in the row descriptor. This mostly works fine when your parameters are in the where clause but goes horribly wrong otherwise. I can only imagine their analysis of your SQL and rearrangement is poor but I wish they would fix it.

Take the example in the RT:

SELECT TOP 1001 userid FROM test_me_i_am_broken WHERE (CHARINDEX(?, login_name) > 0 OR CHARINDEX(?,realname) > 0 AND disabledtext = ''

The parameters are not in the where clause so the driver makes a hash of coming up with a rearranged SQL statement for the columns and ends up reporting the parameters as SQL_INTEGERs.

Of course, when DBD::ODBC is told the parameters are SQL_INTEGER it believes it as it has no reason not to but this often ends up with an error (Invalid character value for cast specification (SQL-22018) in this case) in the execute as the parameters are bound incorrectly. There is a workaround but few people seem to like it and it is to specifically override the parameter type from your perl script like this:

my $p = 'fred';
my $s = prepare(q/select * from my_table where column1 = ?/);
$s->bind_param(1, $p, {TYPE => SQL_VARCHAR});

I'm starting to get this reported more frequently now and have resisted trying to work around it in the vain hope Microsoft would fix it but it appears I am fighting a losing battle. There may be something clever I can do I have not thought of yet but unless I come with something better I am considering adding a connection or statement flag to say ignore SQLDescribeParam and bind everything as var chars. This will be easier for most people who hit this as they can continue to pass the parameters to DBI's execute method without explicitly binding them (as the reporter of this RT was doing).

All other suggestions welcome.