1

I'm trying to create a MySQL stored function that returns a "jobMatch" score. I'm new to procedures, functions, triggers, and events. I have a few functions working, one calculates distance using the haversine formula and works fine.

I receive the error:

#2014 - Commands out of sync; you can't run this command now

when calling the function within a query like this:

SELECT uID,jobMatchScore(uID,'43.665600','-79.383000') AS matchScore FROM `JW-USER-PROFILES`;

Basically I would like to call the jobMatchScore function for each userID in the query as eventually I would like to order the result by jobMatchScore.

My jobMatchScore function looks like this:

BEGIN
DECLARE distance INT(4);
DECLARE matchScore INT(4);
DECLARE posRating INT(4);
DECLARE negRating INT(4);
DECLARE totalRatings INT(4);
DECLARE ratingPercent DECIMAL(3,2);
SET distance=0;SET matchScore=0;SET posRating=0;SET negRating=0;SET ratingPercent=0.00;SET totalRatings=0;
SELECT geoDist(uLat,uLon,input_jLat,input_jLon) INTO distance FROM `JW-USER-PROFILES` WHERE uID=input_uID LIMIT 1;
CASE WHEN distance BETWEEN 0 AND 10 THEN SET matchScore=matchScore+100;
WHEN distance BETWEEN 10 AND 20 THEN SET matchScore=matchScore+75;
WHEN distance BETWEEN 20 AND 30 THEN SET matchScore=matchScore+50;
WHEN distance BETWEEN 30 AND 50 THEN SET matchScore=matchScore+25;
WHEN distance >= 50 THEN SET matchScore=matchScore+10;
END CASE;
SELECT COUNT(rID) INTO posRating FROM `JW-USER-RATINGS` WHERE uRating >=3 AND uID=input_uID;
SELECT COUNT(rID) INTO negRating FROM `JW-USER-RATINGS` WHERE uRating < 3 AND uID=input_uID;
SET totalRatings=posRating+negRating;
SET ratingPercent=posRating / totalRatings * 100;
CASE WHEN ratingPercent BETWEEN 90 AND 100 THEN SET matchScore=matchScore+100;
WHEN ratingPercent BETWEEN 80 AND 90 THEN SET matchScore=matchScore+90;
WHEN ratingPercent BETWEEN 70 AND 80 THEN SET matchScore=matchScore+80;
WHEN ratingPercent BETWEEN 60 AND 70 THEN SET matchScore=matchScore+70;
WHEN ratingPercent BETWEEN 50 AND 60 THEN SET matchScore=matchScore+60;
WHEN ratingPercent BETWEEN 40 AND 50 THEN SET matchScore=matchScore+50;
WHEN ratingPercent BETWEEN 30 AND 40 THEN SET matchScore=matchScore+40;
WHEN ratingPercent BETWEEN 20 AND 30 THEN SET matchScore=matchScore+30;
WHEN ratingPercent BETWEEN 10 AND 20 THEN SET matchScore=matchScore+20;
WHEN ratingPercent BETWEEN 0 AND 10 THEN SET matchScore=matchScore+10;
END CASE;
RETURN matchScore;
END

There are some input parameters "input_uID" (integer),"input_jLat" (decimal),"input_jLon" (decimal).

I've done some reading about the error and it seems to be related to a not freed result.

When I hard code a uID into the function call it does return the expected jobMatchScore.

i.e. 

SELECT uID,jobMatchScore(12,'43.665600','-79.383000') AS matchScore FROM `JW-USER-PROFILES`;

Anyone else run into this or know how to resolve the issue or perform this sort of functionality?

gen_Eric
  • 223,194
  • 41
  • 299
  • 337
Ryan Barrett
  • 101
  • 3
  • 9
  • Possible duplicate of http://stackoverflow.com/questions/614671/commands-out-of-sync-you-cant-run-this-command-now – exussum Feb 27 '14 at 17:15
  • Yes the two do seem related - I am not using mysqli yet, and the error occurs within phpmyadmin's SQL window... the function does attempt to query tables whilst the original calling query is still active... – Ryan Barrett Feb 27 '14 at 17:45
  • Seems like it is to do with the current query which calls the jobMatchScore function still being in progress... – Ryan Barrett Feb 27 '14 at 18:59
  • use something else like sqlyog - youll never look back at phpmyadmin – exussum Feb 27 '14 at 19:57
  • the query appears to work when jobMatchScore is used in the ORDER BY clause... "SELECT uID FROM `JW-USER-PROFILES` ORDER BY jobMatchScore(uID,'43.665600','-79.383000') DESC" works ... I'd still like to know what the jobMatchScore AS matchScore is... – Ryan Barrett Feb 27 '14 at 20:05
  • I will try running the query in another tool, however my test php script that runs the desired query also fails, so I don't think it's just the admin tool... – Ryan Barrett Feb 27 '14 at 20:05
  • try adjusting the code as suggested in the other post also – exussum Feb 27 '14 at 20:09
  • Scratch my last post about ORDER BY working, it isn't. I guess the question is how to use the current uID as a parameter for a function within the same query. – Ryan Barrett Feb 27 '14 at 20:21
  • I am not using mysqli nor am I using the feature 'prepared statements'... I'm not sure what you are suggesting I alter the code to. – Ryan Barrett Feb 27 '14 at 20:50
  • it basically comes down to the question how do I use the value of the current rows uID as a parameter in a function call. when I hardcode the value everything works (except all the matchScores are for the hard coded ID) i.e. SELECT uID,jobMatchScore(12,'43.665600','-79.383000') AS matchScore FROM `JW-USER-PROFILES`; – Ryan Barrett Feb 27 '14 at 20:58
  • When I do not hardcode the uID parameter the query fails with the above error, i.e. SELECT uID,jobMatchScore(uID,'43.665600','-79.383000') AS matchScore FROM `JW-USER-PROFILES`; Perhaps what I am trying to do will require some kind of join so I can call jobMatchScore seperately. I have been experimenting with this, in addition to assigning uID to a local variable but neither have produced the desired result. – Ryan Barrett Feb 27 '14 at 20:59
  • So strangely enough, after some messing about my query "SELECT uID,uLat,uLon,jobMatchScore(uID,'43.000000','-71.000000',uLat,uLon) AS matchScore FROM `JW-USER-PROFILES`; now works. This morning when I got up to resume my efforts I no longer got the error mentioned above and was subsequently able to make a few slight changes to my jobMatchScore function and now it all works.. *punches self in face. – Ryan Barrett Feb 28 '14 at 20:57

0 Answers0