As a result of a thread in the dvi-dev list I am proposing a change to
the way DBD::ODBC binds columns.
The changes are:
1. Columns described as SQL_INTEGER will be bound as SQL_C_LONG and
hence retrieved as a C long and the bound scalar will be set using
sv_setiv. This means the bound scalar does not look like a string (e.g.
sv_POK will be false). Previously, integer columns were bound as strings
and if TYPE => SQL_INTEGER and DiscardString was set we would call
sql_type_cast_svpv but a bug meant this was not working properly if the
column was not rebound (i.e., if you bound the column but then just
called execute again).
2. You cannot override the bound column type. There are a number of
reasons for this. The first is the descriptors for the columns are
queried before any column is bound and buffers and lengths are set at
this time. The second is that DBI only defines SQL_xxx values but in
actual fact ODBC requires SQL_C_xxx values passed to SQLBindCol - they
are not the same e.g., there is a SQL_DECIMAL but no equivalent as an
SQL_C_DECIMAL because there is no such C type decimal. Also, take
SQL_NUMERIC, if I actually bind the column as a SQL_C_NUMERIC I get a
structure back which cannot easily be converted to a Perl scalar.
3. You can still override the bind type with SQL_NUMERIC and SQL_DOUBLE
but the column will be bound as a string and then sql_type_cast_svpv
will be called so if you also specified DiscardString and the type
converts the pv will be lost. This isn't a change as such as DBD::ODBC
has done this for ages.
4. If you call bind_col after a column is bound with a different type
(e.g., if you have already called bind_col and execute then rebind it as
a different type before calling execute again and without re-preparing
it) DBD::ODBC will issue a warning saying the bound type cannot be
changed and will ignore the change in type.
I welcome any comments on this.
I will release this change as 1.38_1 soon. Given the problems which
arose from adding support for execute_for_fetch (and making it the
default) I am going to be extra careful with this. If you use DBD::ODBC
you are strongly advised to test this. The subversion trunk for
DBD::ODBC will be up to date with this change later tomorrow.