DBI/ODBC error: "Optional feature not implemented (SQL-HYC00)"

[HelenCr]

Dear esteemed PerlMonks

Using DBI/ODBC with MS SQL Server database, I am encountering a strange and annoying problem.

I am running a program composed of several modules. Here is a "distilled" excerpt of the issue:
(Admittedly, the substitutions here seem a bit involved and long-winded, but that's because this is a part of a larger system, where "arr_insert" is a utility subroutine):

use strict;
use warnings;
use 5.014;
use List::MoreUtils qw{ firstidx first_index};
use DBI;
use DBI qw(:sql_types);
# ...
my $dbh = DBI->connect('dbi:ODBC:dsn='.$parms{dsn}, $user, $auth,     
+                    {RaiseError => 1, AutoCommit => 1, ShowErrorState
+ment => 1} );
# ...
# ...
my $boats_t;
# ...
# ...
$boats_t ->{SQL_types_ref} = {License => 'SQL_VARCHAR',  Make => 'SQL_
+VARCHAR',
    OwnerID => 'SQL_BIGINT', YearModel => 'SQL_DATETIME',};
my $boats = { tab_obj => $boats_t, 
            arrays => {License => [], Make => [], OwnerID => [], YearM
+odel => [], }, };
# ...
# tuple arrays are populated
# ...
my $sta = $boats->{tab_obj} -> arr_insert( tup_str => $boats ->{arrays
+} };
# ...
# ...

sub arr_insert {    #1 insert an array into a table using DBI execute_
+array        ----------- sub arr_insert
    # call: $sta = $db_table_obj -> arr_insert( tup_str => \%tuples_st
+ructure);
    #    $tup_str = { ID => [id1..idn], col_2 => [c1_2..c2n], .. col_N
+ => [cN_1..cN_n]};
    # taken from: http://search.cpan.org/~timb/DBI-1.627/DBI.pm#execut
+e_array, and see:
    # www.perlmonks.org/?node=DBI%20recipes
    
    my $self = shift;
    my %parms = @_;
    my ($i, $j, $sql_type, $sql_const);
    my $tup_str = $parms{tup_str};
    my @columns = keys %$tup_str;
    my %SQL_types = %{ $self->{SQL_types_ref}};
    for $i (keys %SQL_types) {
        $j = first_index {$_ eq $i} @columns;
        $sql_type = "DBI::$SQL_types{$i}";
        $sql_const = &{ \&$sql_type}();    # see http://www.perlmonks.
+org/?node_id=654158
        $sth->bind_param ($j+1, undef, {TYPE => $sql_const} );
        # see http://www.nntp.perl.org/group/perl.dbi.users/2002/12/ms
+g15911.html
    }
# ...
# ...
@tup_vals = map { $tup_str->{$_} } @columns;
$tuples = $sth->execute_array(  { ArrayTupleStatus => \@tuple_status }
+, @tup_vals);
# ...
# ...
} # end sub sub arr_insert

I am running this on two systems:
System A: Windows 7, Perl v5.16.3 "built for MSWin32-x64-multi-thread", DBI module v 1.625, DBD::ODBC v 1.43,
Microsoft MS SQL Server 10.0.2531 (SQL 2008 SP1). On this system, the program runs fine, with no problems.

System B: WinXP SP3, Perl v5.16.3 "built for MSWin32-x86-multi-thread", DBI module v 1.625, DBD::ODBC v 1.43,
Microsoft MS SQL Server 10.0.4000 (SQL 2008 SP2).
On this system, (exact same program, same database tables) it fails with:

DBD::ODBC::st bind_param failed: [Microsoft][ODBC SQL Server Driver]
        Optional feature not implemented (SQL-HYC00)
    [for Statement "INSERT INTO Boats (License, YearModel, OwnerID, Ma
+ke) VALUES (?, ?, ?, ?)"
                    with ParamValues: 1=undef, 2=undef, 3=undef, 4=und
+ef]

Important notes:
1. It crashes at the bind_param statement, not at the execute_array call (doesn't reach it yet);
2. Note that it's ok to call bind_param with undef values, for defining SQL types for execute_array.

Your help and suggestions will be appreciated.

Many TIA

Helen

[mje]

Just so you know, I am the current maintainer of DBD::ODBC. The optional feature not implemented error can occur for multiple reasons and although you think it happens when calling bind_param, in actual fact, bind_param ends up as multiple ODBC API calls. The first thing to do would be to work out where in those calls it failed. Since you have a recent DBI and DBD::ODBC you can do

set DBI_TRACE=DBD=x.log
perl myscript.pl

and we can look at the last few hundred lines of the log to see what really is happening. If we need to go deeper you can enable ODBC tracing from your ODBC Administrator but I wouldn't right now.

However, having said all that, it is generally unwise to tell DBD::ODBC how to bind input parameters especially when using columns like bigints and date/time/timestamps. The reality of it is, that SQL Server knows what the types of the columns are, it can usually (although not always) describe them via SQLDescribeParam and it can convert them internally and in Perl all your data is string data anyway.

Also read Why am I getting errors with bound parameters?

[HelenCr]

Dear mje

Nice meeting you, and thank you for your thoughtful post.

I went ahead and got rid of the SQL type binding, and the program ran along happily.

But this is a workaround.

"...it can usually (although not always) describe them via SQLDescribeParam and it can convert them internally..."
The reason I added the SQL typing in the first place, was because I've encountered a situation where Perl didn't know that the incoming insert was a string, and thought it was an integer (that happens when you have a subroutine in one package, doing the execute_array, and the original call is outside the package).

"...in Perl all your data is string data anyway..."
I have several cases where the table index is an integer, not a string.

Referring to your suggestion:
set DBI_TRACE=DBD=x.log
where is that command entered? at the command line DOS window? and is x.log the filename?
I'll use it next time I hit a DBI/DBD::ODBC error.

Many TIA

Helen

[mje]

I'm not saying there are no situations where a type should be specified (in fact the faq I pointed to you at illustrates one).

You look to be using two different ODBC drivers (or at least two different versions). You could find one needs the overriding and the other does not. We cannot tell that without seeing the log.

The biggest problem with SQLDescribeParam (what DBD::ODBC uses to find the parmeter types out) is that the ODBC driver has to rearrange your SQL to find the column types and sometimes it can get this wrong i.e., some sql statements can work, and some can fail. DBD::ODBC tries very hard to get around this.

My point about all your data is a string, is that as far as DBD::ODBC is concerned it is a string - it is bound with a SQL_VARCHAR or SQL_WVARCHAR. The binding type you pass to bind_param overrides the type passed to SQLBindParameter telling the driver what to convert it to - there is a subtle difference.

If you can reproduce the issue you found with I've encountered a situation where Perl didn't know that the incoming insert was a string, and thought it was an integer I would love to see it as that doesn't sound at all right to me.

I assumed you were running on windows so the set command would be issued in a command shell (followed by running your perl program) and would tell DBD::ODBC to send all trace output to x.log. If you are on Unix it would be "export DBI_TRACE=DBD=x.log". I'd very much like to see your trace output for the original problem you outlined (with the parameter types as you first had them).

[Anonymous Monk]

INFO: "Optional Feature Not Implemented" Error Message

[HelenCr]

Thank you for your post, AM.

I was familiar with that web page, but it was not clear to me how to apply what it says, to this case.
It says:  "...SQL Server does not support the adDBDate datatype. To correct this problem, change the datatype of the @theDate parameter to adDBTimeStamp."

How should this be interpreted in relevance to my case?

Many TIA

Helen

[Anonymous Monk]

How should this be interpreted in relevance to my case?

I would examine the values of sql_type/sql_const and what your dbd/odbc drivers say about those types -- one of them is not what is expected/accepted