Invalid precision value (SQL-HY104) with DBD-ODBC 1.23

DBD::ODBC has a new rt from Bruce Tanner at Invalid precision value (SQL-HY104) with DBD-ODBC 1.23. Thanks Bruce, one of the few people who keep testing DBD::ODBC, I only wish there were more - see Help me. I have to be honest and admit this rt was reported 2 weeks ago now and I just have not got around to reproducing it. I have been really busy with other things and regret not looking at this sooner as it is rare to get someone like Bruce who stays on top of DBD::ODBC versions and tests each one. The main problem is it is just so difficult to reproduce with so many versions of the MS SQL Server driver and MS ODBC Driver Manager. DBD::ODBC already has a large number of workarounds for bugs in the MS SQL Server driver and the native client driver and I'm reaching the point where I'm oscillating between fixing the latest issue and breaking an older issue again :-( Anyway, I'm putting this in my blog entry so Bruce and anyone else can hold me to task if I don't sort this one out. If I haven't done so in a week feel free to nag me and tell me I've broken a promise.

Comments

Progress with rt49760

I've spent quite a bit of time on this today and although I've come up with one workaround and one DBD::ODBC code change I'm not altogether happy with this. The main problem underlying Bruce's problem is SQLDescribeParam fails. The SQL Server ODBC Driver does not support SQLDescribeParam too well. When you issue SQL with parameter markers and call SQLDescribeParam it attempts to parse the SQL and rearrange it into a select on columns to obtain the information about the columns that are bound. This breaks down in 2 ways a) the parsing and rearranging code is flawed and sometimes does not work b) sometimes the parameter markers are not associated with a particular column.

What is happening in this case is we issue a SQL select statement with a parameter marker and call SQLDescribeParam. DBD::ODBC notices SQLDescribeParam fails (because the driver failed to come up with equivalent SQL to issue a select on real columns in real tables for the parameter marker) and falls back on the default bind type of SQL_WVARCHAR (in the unicode build) and SQL_VARCHAR (in the non-unicode build) but some of the other information SQLDescribeParam it wants to bind the parameter properly is also not available e.g., column size. DBD::ODBC also contains workarounds to attempt to support varXXX(max) columns which are commonly used in MS SQL Server now but recently introduced (and difficult to handle and spot). These columns require the column_size in SQLBindParameter calls to be set to 0. DBD::ODBC then mistakenly decides the parameters are varXXX(max) types and binds them with a column_size of 0 - hence the invalid precision error.

The main problems DBD::ODBC has are a) if the driver supports SQLDescribeParam but fails and b) if the columns are varXXX(MAX) types they are difficult to detect. I've provided 2 workarounds for Bruce which are a) specifically set a bind type by calling bind_param e.g.,:

use DBI qw(:sql_types);
.
.
$request->bind_param(1, 1099, { TYPE => SQL_VARCHAR});
$request->execute;

or change DBD::ODBC's dbdimp.c to uncomment the commented lines in this:

if (phs->param_size == 0) {
  if ((imp_dbh->driver_type == DT_SQL_SERVER_NATIVE_CLIENT) ||
     ((strcmp(imp_dbh->odbc_dbms_name, "Microsoft SQL Server") == 0) &&
     (phs->sql_type == SQL_WVARCHAR) &&
     (phs->requested_type == 0) /*&&
     (phs->describe_param_status == SQL_SUCCESS)*/
)) {
    column_size = 0;
  }
}

However, as Bruce does not need to upgrade right now I'm going to sit on this a bit and see if I can find a better way of detecting varXXX(MAX) columns.