Mostly due to the thread in dbi-dev at DBD::ODBC fetch is returning string for integer (unfortunately some of it was off list) and further comments and rt at Changes in binding columns in DBD::ODBC and DiscardString with SQL_INTEGER not working properly I have made significant changes to the binding of columns in a result-set for DBD::ODBC.
The main changes are:
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).
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.
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.
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 is now up to date with this change.
Trackback URL for this post: