HelenCr has asked for the wisdom of the Perl Monks concerning the following question:
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
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: DBI/ODBC error: "Optional feature not implemented (SQL-HYC00)"
by mje (Curate) on Sep 25, 2013 at 12:31 UTC | |
by HelenCr (Monk) on Sep 25, 2013 at 13:34 UTC | |
by mje (Curate) on Sep 25, 2013 at 14:29 UTC | |
by HelenCr (Monk) on Sep 25, 2013 at 21:39 UTC | |
Re: DBI/ODBC error: "Optional feature not implemented (SQL-HYC00)"
by Anonymous Monk on Sep 25, 2013 at 00:20 UTC | |
by HelenCr (Monk) on Sep 25, 2013 at 13:48 UTC | |
by Anonymous Monk on Sep 26, 2013 at 01:35 UTC |