Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??
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


In reply to DBI/ODBC error: "Optional feature not implemented (SQL-HYC00)" by HelenCr

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others cooling their heels in the Monastery: (7)
    As of 2014-09-19 10:16 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      How do you remember the number of days in each month?











      Results (135 votes), past polls