Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling

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: +e_array, and see: # 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 +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


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

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!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • 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
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            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?

    What's my password?
    Create A New User
    [LanX]: fun, last time I was in Britain people had problems with my name, just rarely they said "like the children TV star Rolf Harris?", this time most replied instantly "Rolf? like the pedophile Rolf Harris" ?
    LanX is perlophile
    [ambrus]: Corion: read https://metacpan. org/pod/release/ MLEHMANN/AnyEvent- 7.13/lib/AnyEvent. pm#SUPPLYING-YOUR- OWN-EVENT-MODEL- INTERFACE in that case

    How do I use this? | Other CB clients
    Other Users?
    Others chilling in the Monastery: (8)
    As of 2016-12-08 12:01 GMT
    Find Nodes?
      Voting Booth?
      On a regular basis, I'm most likely to spy upon:

      Results (140 votes). Check out past polls.