Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
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
  • 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?
    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 surveying the Monastery: (12)
    As of 2015-07-08 05:33 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









      Results (94 votes), past polls