Brian Becker wrote:
> In the change log for DBD::ODBC 1.16_3 it indicates the following:
>
> "If you specify the bound parameter type in your calls to bind_param
> and run them to an ODBC driver which supports SQLDescribeParam you may
> want to check carefully and probably remove the parameter type from
> the bind_param method call."
>
> If the code is specifying param types throughout tge application what
> is the reason these should be removed? what specifically should be
> "check[ed] carefully"? Is this only related to >400K parameter lengths
> or across the board for all types? With the MSFT SQL parameter order
> issue seems it would be advantageous to let sleeping dogs lie unless
> there are major other reasons for removing the param types.
>
> Some guidance would be appreciated...
>
> Brian
>
>
Brain,
I am not completely confident about that advice and certainly wish I'd
been more specific at the time. Looking back through the svn log and my
notes I believe that came about because DBD::ODBC did not hold separate
values for the discovered parameter type and the application provided
parameter type. This meant that new code added to handle MAX columns and
various workarounds for buggy SQL Server drivers could be prevented from
working when the application overrode the bound parameter type.
This situation still exists for 2 workarounds:
1. If you have the original SQL Server ODBC driver (not the native
client one) and the column is a xxx(MAX) type then SQL Server can report
the size of the parameter is 2147483647 but in fact it is a lot bigger
than that. If you bind more than 400K to this column you would get
(HY000, 0, [Microsoft][ODBC SQL Server Driver]
Warning: Partial insert/update. The insert/update of a text or
image column(s) did not succeed.)
(42000, 7125, [Microsoft][ODBC SQL Server Driver][SQL Server]
The text, ntext, or image pointer value conflicts with the column
name specified.)
The workaround checks many things but includes a check on the parameter
type so if you override it to something other than SQL_LONGVARCHAR or
SQL_LONGVARBINARY the workaround can fail. As you will see from the
following the test is rather involved:
if ((imp_dbh->driver_type == DT_SQL_SERVER) &&
((phs->sql_type == SQL_LONGVARCHAR) ||
(phs->sql_type == SQL_LONGVARBINARY)) &&
(column_size == 2147483647) && (phs->strlen_or_ind < 0) &&
((-phs->strlen_or_ind + SQL_LEN_DATA_AT_EXEC_OFFSET) >= 409600))
The "sql_type" above has already been changed to the application
supplied one (if any). I'm not that inclined to change something this
specific and convoluted right now.
2. straight from the comment I added at the time:
workaround bug in SQL Server ODBC driver where it can describe some
parameters (especially in SQL using sub selects) the wrong way. If this
is a varchar then the column_size must be at least as big as the buffer
size but if SQL Server associated the wrong column with our parameter it
could get a totally different size. Without this a varchar(10) column
can be desribed as a varchar(n) where n is less than 10 and this leads
to data truncation errors - see rt 39841.
if (((imp_dbh->driver_type == DT_SQL_SERVER) ||
(imp_dbh->driver_type == DT_SQL_SERVER_NATIVE_CLIENT)) &&
(phs->sql_type == SQL_VARCHAR) &&
(column_size < buffer_length)) {
column_size = buffer_length;
}
Similarly, the "sql_type" above has already been overridden.
I am reasonably confident that this situation does not exist for
anything other than the above now and I will consider changing that
comment in the change log. As you correctly point out (and I believe it
was you who found the issue in the first place), if you describe the
parameters of a procedure in MS SQL Server then bind them by name in a
different order you can get into a lot of trouble unless you
specifically stipulate the bind type. This comes about because SQL
Server ignores your actual SQL at the time of the describe and simply
returns the procedure parameter types in the order they are declared in
the procedure. More at
http://search.cpan.org/~mjevans/DBD-ODBC-1.21/FAQ#Why_do_I_get_strange_results_with_SQL_Server_and_named_parameters?
If your code is specifying parameter types throughout (even when not
calling procedures using named parameters) then I cannot see any
possible disadvantage (other than 1 and 2 above) from doing so in the
DBD::ODBC code now (other than if you recklessly specify an incompatible
type - one which cannot be converted) and if your code is working now
you cannot be doing that.
Sorry for the confusion and hope this rather long answer helps.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
