DBI gives out wrong data

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

Hi, I have a database query that works fine in the mysql console:

mysql> select Preferences.i_preference_desc, Preference_Desc.name, Pre
+ference_Desc.value as range_value, Preferences.value as value, Prefer
+ence_Desc.def_value from Preferences, Preference_Desc WHERE Preferenc
+es.i_preference_desc=Preference_Desc.i_preference_desc and i_account=
+894;
+-------------------+------------------+------------------------------
+------+-----------------------+-----------+
| i_preference_desc | name             | range_value                  
+      | value                 | def_value |
+-------------------+------------------+------------------------------
+------+-----------------------+-----------+
|                 0 | mode             | voicemail, auto_attendant    
+      | voicemail             | voicemail |
|                 1 | password         |                              
+      | 777                   |           |
|                 2 | password_ask     | yes, no                      
+      | no                    | no        |
|                 3 | prompt_levels    | standard, extended, rapid    
+      | standard              | standard  |
|                 4 | announce_dt      | yes, no                      
+      | no                    | no        |
|                 5 | auto_play        | yes, no                      
+      | no                    | no        |
|                 6 | greetings        | standard, extended, personal,
+ name | personal              | standard  |
|                 7 | fax_file         | multi_png, multi_tiff, pdf, t
+iff   | pdf                   | pdf       |
|                 8 | iso_639_1        | RefB=Languages               
+      | en                    | en        |
|                 9 | ext_email        |                              
+      | robscovell@notonyournelly.com |           |
|                10 | ext_email_action | none, forward, notify, copy  
+      | forward               | none      |
+-------------------+------------------+------------------------------
+------+-----------------------+-----------+
11 rows in set (0.00 sec)
[download]

However, when I use code to do the same thing, I simply get a column of 'no' for 'value'. Here is my code:

$sql="select Preferences.i_preference_desc, Preference_Desc.name, Pref
+erence_Desc.value as range_value, Preferences.value as value, Prefere
+nce_Desc.def_value from Preferences, Preference_Desc WHERE Preference
+s.i_preference_desc=Preference_Desc.i_preference_desc and i_account=?
+";    
my $sth2 = $dbh2->prepare($sql);
$sth2->execute($account);
my $preferences = {};

while (my $row = $sth2->fetchrow_hashref) {
    $preferences->{$row->{name}} = { value => $row->{value}, range_val
+ue => $row->{range_value}, def_value => $row->{def_value} };
    warn $row->{value};
}
$sth2->finish();
[download]

This is the output from the warn line:

no at /home/beechams/new_gui/cu_accounts/voicemail_options.html line 9
+4.
no at /home/beechams/new_gui/cu_accounts/voicemail_options.html line 9
+4.
no at /home/beechams/new_gui/cu_accounts/voicemail_options.html line 9
+4.
no at /home/beechams/new_gui/cu_accounts/voicemail_options.html line 9
+4.
no at /home/beechams/new_gui/cu_accounts/voicemail_options.html line 9
+4.
no at /home/beechams/new_gui/cu_accounts/voicemail_options.html line 9
+4.
no at /home/beechams/new_gui/cu_accounts/voicemail_options.html line 9
+4.
no at /home/beechams/new_gui/cu_accounts/voicemail_options.html line 9
+4.
no at /home/beechams/new_gui/cu_accounts/voicemail_options.html line 9
+4.
no at /home/beechams/new_gui/cu_accounts/voicemail_options.html line 9
+4.
no at /home/beechams/new_gui/cu_accounts/voicemail_options.html line 9
+4.
[download]

Is there a bug in DBI or can't I see the obvious bug in my code ... ? def_value, name etc. all come out fine.

READMORE tags added by Arunbear

Comment on DBI gives out wrong data Select or Download Code

Replies are listed 'Best First'.
Re: DBI gives out wrong data by dragonchild (Archbishop) on Jun 27, 2005 at 13:14 UTC
Isolate the problem. Create a test script that does only the following: connect to the database run that SQL statement pull the data If that works, then the problem isn't in that code. Plus, what's going on with the Mason stuff in your trace? My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply]
Re^2: DBI gives out wrong data by robscov (Initiate) on Jun 27, 2005 at 13:24 UTC
Cheers, Works fine outside its Mason component ... grrrrr ... some under-the-hood stuff ... grrrr ... -> fetchrow_hashref for DBD::mysql::st (DBI::st=HASH(0x8184434)~0x +8184428) 1 -> FETCH for DBD::mysql::st (DBI::st=HASH(0x8184428)~INNER 'NAME') -> dbd_st_FETCH_attrib for 0804d2a8, key NAME 1 <- FETCH= [ 'i_preference_desc' 'name' 'range_value' 'value' 'def_ +value' ] at test.pl line 20 1 -> fetch for DBD::mysql::st (DBI::st=HASH(0x8184428)~INNER) -> dbd_st_fetch for 0804d2a8, chopblanks 0 Storing row 0 (10) in 08184608 Storing row 1 (ext_email_action) in 081845fc Storing row 2 (none, forward, notify, copy) in 081845f0 Storing row 3 (forward) in 081845e4 Storing row 4 (none) in 08184590 <- dbd_st_fetch, 5 cols 1 <- fetch= [ '10' 'ext_email_action' 'none, forward, notify, copy' +'forward' 'none' ] row11 at test.pl line 20 <- fetchrow_hashref= HASH(0x81854e8)5keys row11 at test.pl line 20 forward at ./test.pl line 20. [download]	[reply] [d/l]
Re^3: DBI gives out wrong data by robscov (Initiate) on Jun 27, 2005 at 14:19 UTC
This definitely seems to be a Mason, or possibly mod_perl bug.	[reply]
Re^4: DBI gives out wrong data by robscov (Initiate) on Jun 28, 2005 at 02:39 UTC
Re: DBI gives out wrong data by Fletch (Bishop) on Jun 27, 2005 at 12:49 UTC
Next step to take is to enable tracing on the statement handle (`$sth2->trace( 2 )` somewhere before your `while` loop in this case should do it) and watch exactly what's being returned from mysql under the covers. See `perldoc DBI` for more info on `trace`. -- We're looking for people in ATL	[reply] [d/l] [select]
Re: DBI gives out wrong data by samizdat (Vicar) on Jun 27, 2005 at 12:57 UTC
I have found that some versions of mySQL are sensitive to spaces in the value chunk. Try removing the spaces around the fieldnames. Instead of: `dbtable.field, dbtable.field2, ...` [download] use: `dbtable.field,dbtable.field2,...` [download]	[reply] [d/l] [select]
Re^2: DBI gives out wrong data by robscov (Initiate) on Jun 27, 2005 at 13:08 UTC
Hi, Thanks for your responses. trace gives me the following info (just one row posted). Removing the spaces didn't correct the error. I am baffled by this! Thanks again, Rob -> fetchrow_hashref for DBD::mysql::st (DBI::st=HASH(0xa039770)~0x +a03f904) 1 -> FETCH for DBD::mysql::st (DBI::st=HASH(0xa03f904)~INNER 'NAME') -> dbd_st_FETCH_attrib for 0a02920c, key NAME 1 <- FETCH= [ 'i_preference_desc' 'name' 'range_value' 'value' 'def_ +value' ] at voicemail_options.html line 95 via /usr/local/lib/perl5/s +ite_perl/5.8.2/HTML/Mason/C omponent.pm line 134 1 -> fetch for DBD::mysql::st (DBI::st=HASH(0xa03f904)~INNER) -> dbd_st_fetch for 0a02920c, chopblanks 0 Storing row 0 (10) in 0a02d7ac Storing row 1 (ext_email_action) in 0a02eb44 Storing row 2 (none, forward, notify, copy) in 0a02ebb0 Storing row 3 (no) in 0a02d5c0 Storing row 4 (none) in 0a034e44 <- dbd_st_fetch, 5 cols 1 <- fetch= [ '10' 'ext_email_action' 'none, forward, notify, copy' +'no' 'none' ] row11 at voicemail_options.html line 95 via /usr/local/ +lib/perl5/site_perl/5.8.2/H TML/Mason/Component.pm line 134 <- fetchrow_hashref= HASH(0xa03c740)5keys row11 at voicemail_optio +ns.html line 95 via /usr/local/lib/perl5/site_perl/5.8.2/HTML/Mason/C +omponent.pm line 134 [download]	[reply] [d/l]