Dear monks I have a problem which I have been struggeling with for quite a few hours and google has not been kind to me in regarding it. The problem I have is as follows.

* I have and ODBC connection to a MSSQL database with windows trusted login

* I have records in two tables which I want to fetch

* the code looks like this ( note that this is a test code, so I have stripped it from placeholders and all other stuff just to try to get my hands on the record. 

  my ($dbh) = @_;

  my ($bbt, $wmr);

  my $sql = 'select BBTicker, WMRic from FXSpots where id = 218';

  my $sth = $dbh->prepare( $sql );
  $sth->execute() || die "ERROR Unable to execute sql statment $sql...
+" ;   
  $sth->bind_columns(\$bbt, \$wmr);
  $sth->fetch;
    
  print ("BBticker = $bbt WMRic = ");
    
}

[download]

* The record above does not return any value even though when I use "MSSQL enteprise manger" I can fetch it and it's there and I am dbo in that db

* the interesting thing is that when I change the sql statment to fetch a different record to this:

  my $sql = 'select * from InterestRates where ID = 10';

[download]

I get records. Note that I use the same db user the same odbc connection but still I can't fetch my record.

* this is the trace from the above call 

   !! info: '' CLEARED by call to prepare method
    -> prepare for DBD::ODBC::db (DBI::db=HASH(0x37850d0)~0x3784ff8 's
+elect BBTi
cker from FXSpots where id = 218') thr#109fc8
    SQLPrepare select BBTicker from FXSpots where id = 218
    SQLPrepare = 0
    <- prepare= ( DBI::st=HASH(0x3785630) ) [1 items] at mungeMarketDa
+ta.pl line
 199 via  at mungeMarketData.pl line 62
    -> execute for DBD::ODBC::st (DBI::st=HASH(0x3785630)~0x37855d0) t
+hr#109fc8
+dbd_st_execute(3487aa8)
    dbd_st_finish(3487aa8)
    outparams = 0
    !!dbd_error2(err_rc=0, what=st_execute/SQLExecute, handles=(36f498
+0,174900,397b780)
    dbd_describe done_desc=0
    dbd_describe SQLNumResultCols=0 (columns=1)
     now using col 1: type = UNICODE VARCHAR (-9), len = 202, display 
+size = 202, prec = 50, scale = 0
    Bind 1: type = UNICODE CHAR(-8), buf=3422448, buflen=202
    have 1 fields
-dbd_st_execute(3487aa8)=0
    <- execute= ( '0E0' ) [1 items] at mungeMarketData.pl line 200 via
+  at mungeMarketData.pl line 62
    -> bind_columns in DBD::_::st for DBD::ODBC::st (DBI::st=HASH(0x37
+85630)~0x37855d0 SCALAR(0x3780880)) thr#109fc8
1   -> FETCH for DBD::ODBC::st (DBI::st=HASH(0x37855d0)~INNER 'NUM_OF_
+FIELDS') thr#109fc8
1   <- FETCH= ( 1 ) [1 items] at C:/Perl64/lib/DBI.pm line 1870 via  a
+t mungeMarketData.pl line 201
1   -> bind_col in DBD::_::st for DBD::ODBC::st (DBI::st=HASH(0x37855d
+0)~INNER 1 SCALAR(0x3780880) undef) thr#109fc8
1   <- bind_col= ( 1 ) [1 items] at C:/Perl64/lib/DBI.pm line 1881 via
+  at mungeMarketData.pl line 201
    <- bind_columns= ( 1 ) [1 items] at mungeMarketData.pl line 201 vi
+a  at mungeMarketData.pl line 62
    -> fetch for DBD::ODBC::st (DBI::st=HASH(0x3785630)~0x37855d0) thr
+#109fc8    SQLFetch rc 100    dbd_st_finish(3487aa8)
    <- fetch= ( undef ) [1 items] at mungeMarketData.pl line 202 via  
+at mungeMarketData.pl line 62
    <> DESTROY(DBI::st=HASH(0x3785630)) ignored for outer handle (inne
+r DBI::st=HASH(0x37855d0) has ref cnt 1)
    -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x37855d0)~INNER) thr#1
+09fc8
    <- DESTROY= ( undef ) [1 items] at mungeMarketData.pl line 62 via 
+ at mungeMarketData.pl line 62
    dbih_clearcom 0x37855d0 (com 0x384f4a8, type 3) done.

    <> DESTROY(DBI::db=HASH(0x37850d0)) ignored for outer handle (inne
+r DBI::db=HASH(0x3784ff8) has ref cnt 1)
    -> DESTROY for DBD::ODBC::db (DBI::db=HASH(0x3784ff8)~INNER) thr#1
+09fc8
    <- DESTROY= ( undef ) [1 items] at mungeMarketData.pl line 63 via 
+ at mungeMarketData.pl line 63
    dbih_clearcom 0x3784ff8 (com 0x396e1d8, type 2) done.

    -- DBI::END ($@: , $!: )
    !! info: '' CLEARED by call to disconnect_all method
    -> disconnect_all for DBD::ODBC::dr (DBI::dr=HASH(0x3784950)~0x378
+4a10) thr#109fc8
    <- disconnect_all= ( '' ) [1 items] at C:/Perl64/lib/DBI.pm line 7
+40 via  at mungeMarketData.pl line 63
BBticker =  WMRic = !   <> DESTROY(DBI::dr=HASH(0x3784950)) ignored fo
+r outer handle (inner DBI::dr=HASH(0x3784a10) has ref cnt 1)
!   -> DESTROY in DBD::_::common for DBD::ODBC::dr (DBI::dr=HASH(0x378
+4a10)~INNE
R) thr#109fc8
!   <- DESTROY= ( undef ) [1 items] during global destruction
    dbih_clearcom 0x3784a10 (com 0x36f47f8, type 1) done.

[download]

So the quiestion is why does it fail even though I can select it from other sources. Anyone having any idea? And for your knowledge I have copied and pasted the select statment from MS Enterprise manager just for sure and the result is still the same

In reply to DBI don't fetch my data...or at least not all records by Anonymous Monk

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!
  • 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, details, 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, summary, 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.