perlquestion
HelenCr
Dear esteemed PerlMonks<p>Using DBI/ODBC with MS SQL Server database, I am encountering a strange and annoying problem.</p><p>I am running a program composed of several modules. Here is a "distilled" excerpt of the issue:<br>
(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):</p><p><readmore><c>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, ShowErrorStatement => 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 => [], YearModel => [], }, };
# ...
# 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_structure);
# $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#execute_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/msg15911.html
}
# ...
# ...
@tup_vals = map { $tup_str->{$_} } @columns;
$tuples = $sth->execute_array( { ArrayTupleStatus => \@tuple_status }, @tup_vals);
# ...
# ...
} # end sub sub arr_insert</c></readmore></p><p>I am running this on two systems:<br>
System A: Windows 7, Perl v5.16.3 "built for MSWin32-x64-multi-thread", DBI module v 1.625, DBD::ODBC v 1.43,<br>
Microsoft MS SQL Server 10.0.2531 (SQL 2008 SP1).
<u>On this system, the program runs fine, with no problems.</u></p><p>System B: WinXP SP3, Perl v5.16.3 "built for MSWin32-x86-multi-thread", DBI module v 1.625, DBD::ODBC v 1.43,<br>
Microsoft MS SQL Server 10.0.4000 (SQL 2008 SP2).<br>
On this system, (exact same program, same database tables) it fails with:</p><p><c>
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, Make) VALUES (?, ?, ?, ?)"
with ParamValues: 1=undef, 2=undef, 3=undef, 4=undef]</c></p><p>Important notes:<br>1. It crashes at the bind_param statement, not at the execute_array call (doesn't reach it yet);<br>2. Note that it's ok to call bind_param with undef values, for defining SQL types for execute_array.</p><p>Your help and suggestions will be appreciated.</p><p>Many TIA</p><p>Helen</p>