DBI don't fetch my data...or at least not all records

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

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

Comment on DBI don't fetch my data...or at least not all records Select or Download Code

Replies are listed 'Best First'.
Re: DBI don't fetch my data...or at least not all records by McA (Priest) on Nov 20, 2012 at 16:34 UTC
Hi! I can't look at all the details, but there is something I saw at first sight: You don't have error checking on the prepare call. And if your statement is wrong forever reason (e.g. case of the column names) you probably don't get it. My advice: Do errror checking on every DBI related call yourself or do it just simpler if you don't need your hand on the details of error recovery: `$dbh->{RaiseError} = 1; eval { # do the whole DBI stuff }; if(my $e = $@) { confess("ERROR: What a pitty: Something went wrong: $e"); }` [download] Best regards McA	[reply] [d/l]
Re^2: DBI don't fetch my data...or at least not all records by kurreburre (Acolyte) on Nov 20, 2012 at 18:15 UTC
Thanks for the help. Of course I should have had some more proper error handling my bad. Anyway I got some more info when enhancing the error handlig, but still it doesn't tell me jack. Here comes the maeesage `fetch failed at mungeMarketData.pl line 207, <FILE> line 2494. Bad file descriptorERROR: What a pitty: Something went wrong: fetch f +ailed at mungeMarketData.pl line 207, <FILE> line 2494. at mungeMarketData.pl line 213 main::testSql('DBI::db=HASH(0x3f64958)') called at mungeMarket +Data.pl` [download] bad FILE descriptor? I don't use a file I try to fetch from a db and that works fine with another table in the same db, same user	[reply] [d/l]
Re^3: DBI don't fetch my data...or at least not all records (bug?) by tye (Sage) on Nov 20, 2012 at 18:35 UTC
bad FILE descriptor? I don't use a file It has to connect to the DB somehow. There is almost always a file descriptor under the covers (usually to a socket). But it could also be reporting $! even though there wasn't a failure that set $! ("bad file descriptor" is a common error that gets left behind in $! due to common stuff that happens under the covers). It looks like a bug to me. - tye	[reply]
Re^4: DBI don't fetch my data...or at least not all records (bug?) by kurreburre (Acolyte) on Nov 20, 2012 at 18:43 UTC
Re^5: DBI don't fetch my data...or at least not all records (bug?) by tye (Sage) on Nov 20, 2012 at 18:47 UTC
Re^3: DBI don't fetch my data...or at least not all records by kurreburre (Acolyte) on Nov 20, 2012 at 18:34 UTC
I also made a more "decent" trace and it looks like DBI finds something. The first trace here fails !! info: '' CLEARED by call to prepare method -> prepare for DBD::ODBC::db (DBI::db=HASH(0x3fa28a8)~0x3fa2740 +'select BBTicker, WMRic from FXSpots where id = 218') thr#1639fc8 <- prepare= ( DBI::st=HASH(0x3fa2d58) ) [1 items] at mungeMarket +Data.pl line 199 -> execute for DBD::ODBC::st (DBI::st=HASH(0x3fa2d58)~0x3fa2ad0) + thr#1639fc8 <- execute= ( '0E0' ) [1 items] at mungeMarketData.pl line 200 -> bind_columns in DBD::_::st for DBD::ODBC::st (DBI::st=HASH(0x +3fa2d58)~0x3fa2ad0 SCALAR(0x3a15040) SCALAR(0x3a15070)) thr#1639fc8 <- bind_columns= ( 1 ) [1 items] at mungeMarketData.pl line 201 -> fetch for DBD::ODBC::st (DBI::st=HASH(0x3fa2d58)~0x3fa2ad0) t +hr#1639fc8 <- fetch= ( undef ) [1 items] at mungeMarketData.pl line 202 BBticker = WMRic = -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x3fa2ad0)~INNER) thr +#1639fc8 <- DESTROY= ( undef ) [1 items] at perl5db.pl line 3754 -> DESTROY for DBD::ODBC::db (DBI::db=HASH(0x3fa2740)~INNER) thr +#1639fc8 <- DESTROY= ( undef ) [1 items] at mungeMarketData.pl line 63 -- DBI::END ($@: , $!: ) !! info: '' CLEARED by call to disconnect_all method -> disconnect_all for DBD::ODBC::dr (DBI::dr=HASH(0x3fa20e0)~0x3 +fa2158) thr#1639fc8 <- disconnect_all= ( '' ) [1 items] at DBI.pm line 741 [download] and this trace succeeded !! info: '' CLEARED by call to prepare method -> prepare for DBD::ODBC::db (DBI::db=HASH(0x3ef7a60)~0x3ef78f8 's +elect BBTicker, CRBTicker from Indices where id = 51') thr#299fc8 <- prepare= ( DBI::st=HASH(0x3ef7f10) ) [1 items] at mungeMarketDa +ta.pl line 200 -> execute for DBD::ODBC::st (DBI::st=HASH(0x3ef7f10)~0x3ef7c88) t +hr#299fc8 <- execute= ( -1 ) [1 items] at mungeMarketData.pl line 201 -> bind_columns in DBD::_::st for DBD::ODBC::st (DBI::st=HASH(0x3e +f7f10)~0x3ef7c88 SCALAR(0x3965040) SCALAR(0x3965070)) thr#299fc8 <- bind_columns= ( 1 ) [1 items] at mungeMarketData.pl line 202 -> fetch for DBD::ODBC::st (DBI::st=HASH(0x3ef7f10)~0x3ef7c88) thr +#299fc8 <- fetch= ( [ 'SPGCLV Index' undef ] ) [1 items] row1 at mungeMark +etData.pl line 203 BBticker = SPGCLV Index WMRic = -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x3ef7c88)~INNER) thr#2 +99fc8 <- DESTROY= ( undef ) [1 items] at perl5db.pl line 3754 -> DESTROY for DBD::ODBC::db (DBI::db=HASH(0x3ef78f8)~INNER) thr#2 +99fc8 <- DESTROY= ( undef ) [1 items] at mungeMarketData.pl line 63 -- DBI::END ($@: , $!: ) !! info: '' CLEARED by call to disconnect_all method -> disconnect_all for DBD::ODBC::dr (DBI::dr=HASH(0x3ef7298)~0x3ef +7310) thr#299fc8 <- disconnect_all= ( '' ) [1 items] at DBI.pm line 741 [download] the only differens I can see is in the fetch method where the failed trace has "undef" whereas the working one has my valid value.	[reply] [d/l] [select]
Re^4: DBI don't fetch my data...or at least not all records by choroba (Cardinal) on Nov 21, 2012 at 11:20 UTC
Re: DBI don't fetch my data...or at least not all records by CountZero (Bishop) on Nov 20, 2012 at 20:38 UTC
I don't have an MSSQL database to test, so my answer could be far off the mark, but I noticed in your failing query the fieldname "id" is all lowercase and in the working query it is all uppercase. Could it be that MSSQL is case-sensitive for the fieldnames? CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James My blog: Imperial Deltronics	[reply]
Re^2: DBI don't fetch my data...or at least not all records (case) by tye (Sage) on Nov 21, 2012 at 00:56 UTC
MSSQL can be configured either way on that point. Years ago when I worked with it last, there was a restriction that you could only en-/dis-able the ignoring of letter case completely. So arranging for the ignoring of letter case for database column names also meant that letter case was ignored when comparing any values. - tye	[reply]
Re^2: DBI don't fetch my data...or at least not all records by Anonymous Monk on Nov 20, 2012 at 21:49 UTC
yes it is	[reply]
Re^2: DBI don't fetch my data...or at least not all records by kurreburre (Acolyte) on Nov 21, 2012 at 06:24 UTC
Hi all. Well my SQL instance is case insensitive and I also tested that by doing copy and paste from SQL manager, where I get a result when executing the query. I also tested to create the query "case correct" but still it fails.	[reply]
Re: DBI don't fetch my data...or at least not all records by karlgoethebier (Abbot) on Nov 20, 2012 at 17:46 UTC
Hi, perhaps you should try `'SELECT 'foo'` and see what happens. ( no DUAL in MSSQL...). Regards, Karl. Update: I think it is not a bad idea to check your connection with a simple query. �The Crux of the Biscuit is the Apostrophe�	[reply] [d/l]
Re^2: DBI don't fetch my data...or at least not all records by kurreburre (Acolyte) on Nov 20, 2012 at 18:17 UTC
Hi thanks for trying to help out. Well the connection works fine, as stated bofore if I use the same connection towards another table in the same db, with the same user it works. I have also checked permission on the table and there are no strange permissions and I use trused user on both ODBC and when connection via enteprise manager. But in enterprise manager I get a result	[reply]
Re^3: DBI don't fetch my data...or at least not all records by karlgoethebier (Abbot) on Nov 20, 2012 at 18:41 UTC
Update: Yes, but do you fetch a result if you say `SELECT 'foo'`? Please feel free to provide more information about what you want to do. I then can try to repeat this with my MSSQL... I could not repeat it (i'm using SQL Server Native Client 10.0): `SELECT * FROM FXSpots; ID LHSCurrencyID RHSCurrencyID TenorID BBTicker WMRic C +orrespondingSpotID 6 1 1 1 foo bar 1 7 1 1 1 foo bar 1 8 1 1 1 foo bar 1 9 1 1 1 foo bar 1 10 1 1 1 foo bar 1 11 1 1 1 foo bar 1 12 1 1 1 foo bar 1` [download] #!c:/perl/bin/perl.exe use DBI; my $dbh = DBI->connect("dbi:ODBC:Market_Data_prod", "karl", "secret"); my ( $bbt, $wmr ); my $sql = 'select BBTicker, WMRic from FXSpots where id = 10'; # i can + get all.. 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 = "); # ...you missed $wmr print("BBticker = $bbt WMRic = $wmr"); __END__ C:\Dokumente und Einstellungen\karl\Desktop\market_data>market_data.pl BBticker = foo WMRic = bar [download] Sorry that i could not help, Regards, Karl �The Crux of the Biscuit is the Apostrophe�	[reply] [d/l] [select]
Re^4: DBI don't fetch my data...or at least not all records by kurreburre (Acolyte) on Nov 20, 2012 at 18:50 UTC
Re^4: DBI don't fetch my data...or at least not all records by Anonymous Monk on Nov 21, 2012 at 09:25 UTC
Re^5: DBI don't fetch my data...or at least not all records by karlgoethebier (Abbot) on Nov 21, 2012 at 09:51 UTC
Re: DBI don't fetch my data...or at least not all records by mhearse (Chaplain) on Nov 21, 2012 at 01:35 UTC
This has nothing to do with your question, but I thought I'd add it. It's regarding the coding of your sql queries. I use heredocs. It's not really necessary for a short query, but comes in handy for complex ones. Makes code very easy to follow. `my $sql = <<EOQ; select BBTicker, WMRic from FXSpots where id = 218' EOQ` [download]	[reply] [d/l]
Re: DBI don't fetch my data...or at least not all records by Anonymous Monk on Nov 21, 2012 at 07:18 UTC
Hi again all You guys might now have seen, but I posted my tables as reply to a comment also my db is not case sensitive. I Cheers Kurre	[reply]

Back to Seekers of Perl Wisdom