Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Re: DBI fails to return an error code

by mje (Deacon)
on Oct 05, 2011 at 10:00 UTC ( #929745=note: print w/ replies, xml ) Need Help??


in reply to DBI fails to return an error code

Are you by any chance the same person that posted on stackoverflow at http://stackoverflow.com/questions/7432944/undefined-dbierrstr-using-dbi-perl-and-mysql-on-windows-7-x64/7434259 and who posted to dbi-users mailing list at http://www.mail-archive.com/dbi-users@perl.org/msg34081.html and reported https://rt.cpan.org/Public/Bug/Display.html?id=71047?

If so I have tried to help on some of those forums but you need to come up with something demonstrable that anyone else can run. The problem is that every pseudocode version you've posted works fine for me so I can only assume a) you have different versions or b) I've misinterpreted your pseudocode.

Write a small self contained real example that fails, post it along with your Perl, DBD::mysql and DBI versions and show the exact output of the script which demonstrates $DBI::errstr is not defined. Then if we cannot reproduce it we can help you debug it or perhaps trace it but we need to see a real life full example.


Comment on Re: DBI fails to return an error code
Re^2: DBI fails to return an error code
by djten (Novice) on Oct 09, 2011 at 02:21 UTC

    No, that was not me that created any of those other posts. Interesting though, it looks like that person is having the same issue. Actually, that person is having the reverse of my problem. It's my Windows 7 Pro SP1 (32bit) installation that has the DBI error values working as expected, and my Vista Home Premium SP2 laptop that is not.

    In any event, you raise a good point, I should have included some explicit examples along with my initial rambling.

    Keep in mind, however, this is not a matter of one isolated piece of code giving me trouble. On my Win7 desktop computer, all of the DBI error reporting works properly, everywhere throughout the entire project, and on my Vista laptop, all of the DBI reporting FAILS to work throughout the entire project.

    Here's some sample code. I will use the snippet I purposely messed up, that I referenced in my reply to the previous post.

    my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost", $dbuid, $dbpw) || +db_err("connecting to database", $sql, $DBI::err, $DBI::errstr); my $sql = "SELECT DCredits, Price, Description FROM $dbname.CUSTOMER_P +LAN WHERE Name='$planname'"; my $sqlh = $dbh->prepare($sql) || db_err("preparing statement handle", + $sql, $DBI::err, $DBI::errstr); $sqlh->execute || db_err("executing query", $sql, $DBI::err, $DBI::err +str); while (@row = $sqlh->fetchrow_array()) { ($credits, $price, $descript) = @row; # process the data on each loop here } $sqlh->finish; $dbh->disconnect;

    The actual column is really named just Credits, not DCredits.

    I might not be handling the above code in the best way (feel free to let me know), but I want to catch any possible error at each point of preparation, and pass it to a subroutine db_err. It's actually a method in a little module I made for this project. The simple code for that is as follows.

    sub db_err { my ($pmsg, $psql, $perrnum, $perrstr) = @_; print "<p>There was a problem: $pmsg.</p>\n"; print "<p><i>Attempted command:</i> $psql</p>\n"; print "<p><i>Error:</i> $perrnum - $perrstr</p>\n"; }

    Presenting any database errors that may occur in this way is one of the requirements of the project.

    To duplicate the data, here is a series of MySQL statements.

    ## Table CREATE TABLE `CUSTOMER_PLAN` ( `RowId` int(11) unsigned NOT NULL auto_increment, `Name` varchar(6) default NULL, `Credits` smallint(6) unsigned default NULL, `Price` decimal(8,2) default NULL, `Credit_Value` decimal(3,2) default NULL, `Status` char(1) default NULL, # A=Active, I=Inactive `Display_Order` char(3) default NULL, `Description` varchar(255) default NULL, PRIMARY KEY (RowId), KEY planname (Name), KEY descript (Description(15)), KEY dispord (Display_Order) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; ## Data INSERT INTO `CUSTOMER_PLAN` VALUES(1, 'Free', 0, 0.00, 0.75, 'A', + '001', 'Try us out - receive 3 free recipes.'); INSERT INTO `CUSTOMER_PLAN` VALUES(2, 'Shine', 12, 9.75, 0.75, 'A', + '002', 'Shine with a small introduction to Vibrant Healthy Living.') +; INSERT INTO `CUSTOMER_PLAN` VALUES(3, 'Seeker', 35, 18.75, 0.75, 'A', + '003', '<i>-noun.</i> An adventurous person who is seeking new exper +iences and new information to create a higher level of understanding +and enlightenment.'); INSERT INTO `CUSTOMER_PLAN` VALUES(4, 'Sage', 60, 47.00, 0.75, 'A', + '004', '<i>-noun.</i> A generous, lively person on the journey for t +he possession of wisdom and experience. This person is energetic, vib +rant and famed for health and wisdom.'); INSERT INTO `CUSTOMER_PLAN` VALUES(5, 'Guru', 120, 89.00, 0.75, 'A', + '005', '<i>-noun.</i> A divine teacher. This person has vast knowled +ge and practices vibrant living while sharing with their community to + create a greater understanding and manifestation of truth and beauty +.');

    The versions I am using on both desktop and laptop: ActiveState Perl v5.12.4, Apache v2.2.17, MySQL v5.5.8, DBI v1.616, DBD::mysql v4.018.

    Using the above back in my project, I get the following on my Win7 desktop computer.

    There was a problem: executing query. Attempted command: SELECT DCredits, Price, Description FROM hlr.CUSTOM +ER_PLAN WHERE Name='Free' Error: 1054 - Unknown column 'DCredits' in 'field list' ...

    etc. for the other rows. Clearly passing on what the error was.

    On my Vista laptop, I get the following.

    There was a problem: executing query. Attempted command: SELECT DCredits, Price, Description FROM hlr.CUSTOM +ER_PLAN WHERE Name='Free' Error: - ...

    etc. No error information at all.

      I installed DBD::mysql 4.018 and DBI 1.616 and ran the following based on your posting (note I had to invent $planname and substitute $dbname. Also I took out the fetch as it cannot work:

      use strict; use DBI; my $h = DBI->connect('dbi:mysql:database=test', "xxx", "yyy"); eval { $h->do(q/drop table CUSTOMER_PLAN/); }; $h->do(<<EOT); CREATE TABLE `CUSTOMER_PLAN` ( `RowId` int(11) unsigned NOT NULL auto_increment, `Name` varchar(6) default NULL, `Credits` smallint(6) unsigned default NULL, `Price` decimal(8,2) default NULL, `Credit_Value` decimal(3,2) default NULL, `Status` char(1) default NULL, # A=Active, I=Inactive `Display_Order` char(3) default NULL, `Description` varchar(255) default NULL, PRIMARY KEY (RowId), KEY planname (Name), KEY descript (Description(15)), KEY dispord (Display_Order) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; EOT $h->do(q/INSERT INTO `CUSTOMER_PLAN` VALUES(1, 'Free', 0, 0.00, 0 +.75, 'A', '001', 'Try us out - receive 3 free recipes.')/); $h->do(q/INSERT INTO `CUSTOMER_PLAN` VALUES(2, 'Shine', 12, 9.75, 0 +.75, 'A', '002', 'Shine with a small introduction to Vibrant Healthy +Living.')/); $h->do(q|INSERT INTO `CUSTOMER_PLAN` VALUES(3, 'Seeker', 35, 18.75, 0 +.75, 'A', '003', '<i>-noun.</i> An adventurous person who is seeking +new experiences and new information to create a higher level of under +standing and enlightenment.')|); $h->do(q|INSERT INTO `CUSTOMER_PLAN` VALUES(4, 'Sage', 60, 47.00, 0 +.75, 'A', '004', '<i>-noun.</i> A generous, lively person on the jour +ney for the possession of wisdom and experience. This person is energ +etic, vibrant and famed for health and wisdom.')|); $h->do(q|INSERT INTO `CUSTOMER_PLAN` VALUES(5, 'Guru', 120, 89.00, 0 +.75, 'A', '005', '<i>-noun.</i> A divine teacher. This person has vas +t knowledge and practices vibrant living while sharing with their com +munity to create a greater understanding and manifestation of truth a +nd beauty.')|); sub db_err { my ($pmsg, $psql, $perrnum, $perrstr) = @_; print "<p>There was a problem: $pmsg.</p>\n"; print "<p><i>Attempted command:</i> $psql</p>\n"; print "<p><i>Error:</i> $perrnum - $perrstr</p>\n"; } my $dbname = 'test'; my $planname = 'Free'; my $sql = "SELECT DCredits, Price, Description FROM $dbname.CUSTOMER_P +LAN WHERE Name='$planname'"; my $sqlh = $h->prepare($sql) || db_err("preparing statement handle", $ +sql, $DBI::err, $DBI::errstr); $sqlh->execute || db_err("executing query", $sql, $DBI::err, $DBI::err +str); #####while (@row = $sqlh->fetchrow_array()) #####{ ##### ($credits, $price, $descript) = @row; ##### # process the data on each loop here #####} #####$sqlh->finish; $h->disconnect;

      It fails with:

      DBD::mysql::st execute failed: Unknown column 'DCredits' in 'field lis +t' at /home/martin/930402.pl line 45. <p>There was a problem: executing query.</p> <p><i>Attempted command:</i> SELECT DCredits, Price, Description FROM +test.CUSTOMER_PLAN WHERE Name='Free'</p> <p><i>Error:</i> 1054 - Unknown column 'DCredits' in 'field list'</p>

      When I rerun it with DBI_TRACE=15 I get the following near the end:

      -> prepare for DBD::mysql::db (DBI::db=HASH(0x9f482a0)~0x9f483c0 ' +SELECT DCredits, Price, Description FROM test.CUSTOMER_PLAN WHERE Nam +e='Free'') thr#9dd4008 New 'DBI::st' (for DBD::mysql::st, parent=DBI::db=HASH(0x9f483c0), + id=undef) dbih_setup_handle(DBI::st=HASH(0x9f48180)=>DBI::st=HASH(0x9e50408) +, DBD::mysql::st, 9ece010, Null!) dbih_make_com(DBI::db=HASH(0x9f483c0), 9f3e808, DBD::mysql::st, 25 +2, 0) thr#9dd4008 dbih_setup_attrib(DBI::st=HASH(0x9e50408), Err, DBI::db=HASH(0x9f4 +83c0)) SCALAR(0x9ecdf30) (already defined) dbih_setup_attrib(DBI::st=HASH(0x9e50408), State, DBI::db=HASH(0x9 +f483c0)) SCALAR(0x9ecdfb0) (already defined) dbih_setup_attrib(DBI::st=HASH(0x9e50408), Errstr, DBI::db=HASH(0x +9f483c0)) SCALAR(0x9ecdf70) (already defined) dbih_setup_attrib(DBI::st=HASH(0x9e50408), TraceLevel, DBI::db=HAS +H(0x9f483c0)) 0 (already defined) dbih_setup_attrib(DBI::st=HASH(0x9e50408), FetchHashKeyName, DBI:: +db=HASH(0x9f483c0)) 'NAME' (already defined) dbih_setup_attrib(DBI::st=HASH(0x9e50408), HandleSetErr, DBI::db=H +ASH(0x9f483c0)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0x9e50408), HandleError, DBI::db=HA +SH(0x9f483c0)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0x9e50408), ReadOnly, DBI::db=HASH( +0x9f483c0)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0x9e50408), Profile, DBI::db=HASH(0 +x9f483c0)) undef (not defined) -> dbd_st_prepare MYSQL_VERSION_ID 50083, SQL statement: SELECT DC +redits, Price, Description FROM test.CUSTOMER_PLAN WHERE Name='Free' >- dbd_st_free_result_sets <- dbd_st_free_result_sets RC -1 <- dbd_st_free_result_sets >count_params statement SELECT DCredits, Price, Description FROM test. +CUSTOMER_PLAN WHERE Name='Free' <- dbd_st_prepare <- prepare= DBI::st=HASH(0x9f48180) at /home/martin/930402.pl line + 44 >> execute DISPATCH (DBI::st=HASH(0x9f48180) rc1/1 @1 g0 ima10 +41 pid#19440) at /home/martin/930402.pl line 45 -> execute for DBD::mysql::st (DBI::st=HASH(0x9f48180)~0x9e50408) +thr#9dd4008 -> dbd_st_execute for 09ece030 >- dbd_st_free_result_sets <- dbd_st_free_result_sets RC -1 <- dbd_st_free_result_sets mysql_st_internal_execute MYSQL_VERSION_ID 50083 >parse_params statement SELECT DCredits, Price, Description FROM test. +CUSTOMER_PLAN WHERE Name='Free' --> do_error Unknown column 'DCredits' in 'field list' error 1054 recorded: Unknown + column 'DCredits' in 'field list' <-- do_error IGNORING ERROR errno 0 <- dbd_st_execute returning imp_sth->row_num 18446744073709551614 !! ERROR: 1054 'Unknown column 'DCredits' in 'field list'' (err#0) <- execute= undef at /home/martin/930402.pl line 45 DBD::mysql::st execute failed: Unknown column 'DCredits' in 'field lis +t' at /home/martin/930402.pl line 45. -> $DBI::err (*) FETCH from lasth=HASH <- $DBI::err= 1054 -> $DBI::errstr (&) FETCH from lasth=HASH >> DBD::mysql::st::errstr <- $DBI::errstr= 'Unknown column 'DCredits' in 'field list''

      What does your trace output look like on the failing machine?

        I have come across a solution for this. Which I find a relief, because it means I wasn't really slipping even further into insanity. This time.

        The trace suggestion was a good one, even though the trace output on the two computers did not reveal anything definitive. The trace on my desktop computer showed the error being processed, and the trace on the laptop showed everything processing right through as if it was an error-free transaction. However, it did get me thinking about possible module bugs, since I had ruled out everything else I could think of.

        I noticed that ActivePerl package manager showed that there was already a new version of DBD::MySQL available (4.020). So I updated both computers to that version, and when I ran the identical code on both computers again, they both caught and reported the error successfully and identically. So my laptop will once again be useful when DB-related debugging is necessary.

        I noticed on CPAN, the DBD::MySQL bug report had some recent references to "Undefined $DBI::errstr," and the change log for v4.020 says "Numerous fixes for prepared statements," without much detail beyond that.

        At this point I am going to go forward on the assumption that this was some kind of DBD::MySQL bug, and with v4.020 installed I am good to go.

        Thank you to all who offered your wisdom to help with my problem.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (13)
As of 2014-07-23 16:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (146 votes), past polls