Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

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

by Anonymous Monk
on Nov 20, 2012 at 15:53 UTC ( #1004744=perlquestion: print w/ replies, xml ) Need Help??
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 = "); }

* 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';

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.

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
Re: DBI don't fetch my data...or at least not all records
by McA (Curate) 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"); }

    Best regards
    McA

      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

      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

        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
        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
        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.
        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        

Re: DBI don't fetch my data...or at least not all records
by karlgoethebier (Curate) 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»

      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

        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
        #!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

        Sorry that i could not help, Regards, Karl

        «The Crux of the Biscuit is the Apostrophe»

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
      yes it is

      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        

      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.
Re: DBI don't fetch my data...or at least not all records
by mhearse (Hermit) 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
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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1004744]
Approved by davies
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (7)
As of 2014-08-31 10:13 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (294 votes), past polls