mysql - Stored procedure not updating table as expected -



mysql - Stored procedure not updating table as expected -

i running stored procedure on mysql database , says runs fine, no errors come back, when check table it's supposed have updated nil has changed.

when manually run each individual part of procedure works fine, expect. selects run fine.

i'm new in dealing stored procedures i'm not sure how debug this. can output different stages it's @ can create sure gets update query? can check results of queries it's running?

i googled issue didn't find helpful. manual had info on how set procedure up, not how debug when wasn't working (unless missed something).

this whole procedure:

delimiter // create procedure questionstatistics(in quizid int(11)) begin declare bdone int; declare qqid int; declare totalanswers int; declare totalcorrect int; declare totalvalue int; declare curs cursor select qqid quizquestions qqquizid = quizid; declare go on handler not found set bdone = 1; open curs; set bdone = 0; repeat fetch curs qqid; select count(*) quizanswers qaquizquestionid = qqid , qaismarked = 1 totalanswers; select count(*) quizanswers qaquizquestionid = qqid , qaiscorrect = 1 totalcorrect; select sum(qavalue) quizanswers qaquizquestionid = qqid , qaismarked = 1 totalvalue; update quizquestions set qqaveragepoints = round(totalvalue / totalanswers, 2), qqpercentagecorrect = round(100 * totalcorrect / totalanswers) qqid = qqid; until bdone end repeat; close curs; end// delimiter ;

i'd appreciate if point me in right direction on how debug this, or if spot issue.

you can debug procedure using debugger feature in dbforge studio mysql (try trial version).

i suggest optimize queries, e.g. utilize 1 select query instead of 3 ones -

select count(if(qaismarked = 1, 1, null)), count(if(qaiscorrect = 1 , 1, null)), sum(if(qaismarked = 1, qavalue, 0) totalanswers, totalcorrect, totalvalue quizanswers qaquizquestionid = qqid;

to avoid errors - not utilize same names parameters, variables , object names columns.

also, seek avoid using cursors @ all, write 1 complex update query.

mysql stored-procedures

Comments

Post a Comment

Popular posts from this blog

model view controller - MVC Rails Planning -

model view controller - MVC Rails Planning - i'm starting rails , i'm building app stores films info, user can watch film , give grade on web application. i have coded cinema model, controller , views. now, have 2 questions: should need create model , controller grading? in case, should create or should wait , create first users model , controller? yes, on model , controller grades. that'd feasible way maintain track of who's graded what. it's create first, i'd start users, since grades depend on both films , users. ruby-on-rails model-view-controller
Read more

ruby on rails - Devise Logout Error in RoR -

ruby on rails - Devise Logout Error in RoR - while using devise in ror, encountering unusual error when trying log out url different root url. displays me : actioncontroller::invalidauthenticitytoken in devise::sessionscontroller#destroy" for illustration if rooting path after log in '/structure', , seek log out '/general' error, while logging out '/structure' fine. any help highly appreciated. ruby-on-rails ruby devise
Read more

html - Submenu setup with jquery and effect 'fold' -

html - Submenu setup with jquery and effect 'fold' - i want open submenu jquery effect fold, problem if user "hover effect" fast menu remain open, how can avoid this, jquery code is: $('ul.mainmenu li').hover( function() { $(this).children('ul').show('fold', 570); }, function() { $(this).children('ul').hide('fold', 500); } ); my jsfiddle link is: http://jsfiddle.net/9wkbf/ updated fiddle the reason behind problem is, first event $(this).children('ul').show('fold', 570); queued , until completes, sec animation not start. the next snippet can workaround $('ul.mainmenu > li').hover( function() { $(this).children('ul').show('fold', 570); }, function() { $('ul:not(.mainmenu)').hide('fold', 500); } ); *important note : work current scenario. jquery html css
Read more