Use the Perl API without specifying a SQL datatype the Aster datatypes, as in the example:
$rc = $sth->bind_col($column_number, \$var_to_bind);
Avoid specifying the SQL datatype to bind to the column, as in:
$rc = $sth->bind_col($column_number, \$var_to_bind, $bind_type);
This will work fine when binding boolean and character columns with DBI::SQL_CHAR, but for the other datatypes, this will cause two issues:
- If you specify a numeric SQL type in bind_col, corrupt data may be returned. Numeric types include SQL_INTEGER, SQL_SMALLINT, SQL_DOUBLE, SQL_REAL, SQL_NUMERIC/SQL_DECIMAL, SQL_BIT, SQL_TIME, SQL_TIMESTAMP, SQL_DATE, and SQL_DATETIME.
- If you specify a SQL_VARCHAR type in bind_col, an error may be returned. The error looks like:
- On Unix ODBC: “DBD::ODBC::st fetch failed: [unixODBC][Driver Manager]Invalid application buffer type (SQL-HY003)”
- On Windows ODBC: “DBD::ODBC::st fetch failed: [Microsoft][ODBC Driver Manager] Program type out of range (SQL-HY003)”
This issue has been observed with the following software versions:
- Perl 5.8.9
- DBD-ODBC 1.3.1
- DBI-1.616