Solved

MySQL function doesn't return a value

Posted on 2016-09-13
7
181 Views
Last Modified: 2016-09-16
Hi Experts
I have written the MySQL function below, and when I invoke it, the action grid reports 'OK'; but the results grid does not display nor does it report a value.

Here is the FN:
***
CREATE DEFINER=`mysql`@`%` FUNCTION `GetBOP`(pID bigint, gameid integer) RETURNS int(11)
    DETERMINISTIC
BEGIN
declare bop int;
set bop=0;
/*set @btgpos := 0;*/
set @pID := pID;
set @gameid := gameid;

SELECT myfield INTO bop from mytable where Playerid = @pID and gameid = @gameid;

/*SELECT @btgpos := `myfield` from mytable where Playerid = @pID and gameid = @gameid;*/

If bop > 9 then
   set bop = 10;
end if;
   
RETURN bop;
END
***
___
To execute - I run this:
select getBOP(8701,24091)

RESULTS: (In Action grid of MySQL Workbench)
Action - select getBOP(8701,24091)  Response - OK

No Results grid displays and no values displayed.

___
To test:  I execute this:
set @pID := 8701;
set @gameid := 24091;
SELECT myfield from mytable where Playerid = @pID and gameid = @gameid;

With this test I get
Result Grid -  Results - 8
Action grid - Action - 1 Row returned

It seems like the function is returning a NULL or boolean or ?? Not sure why I'm not getting a result.
As you can see by the function above, I've tried using @btgpos variable vs. declare variable. This causes Error 1415 (can't return a result..) so I am perhaps using it incorrectly.

I've written a very similar fn that returns numbers as expected and I can not see the difference at all. Only difference is it has only one parameter vs two.

Any advice would be greatly appreciated.
Thanks!
0
Comment
Question by:k heiutz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41797420
any better with this:
select getBOP(8701,24091) bop from dual

Open in new window

0
 

Author Comment

by:k heiutz
ID: 41798237
Hi Guy ~ no luck - I get the same results.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41799864
Try this:
CREATE DEFINER=`mysql`@`%` FUNCTION `GetBOP`(pID bigint, gameid integer) RETURNS int(11)
    DETERMINISTIC
BEGIN
declare bop int;
set bop=0;

SELECT myfield INTO bop from mytable where Playerid = pID and gameid = gameid;

If bop > 9 then
   set bop = 10;
end if;
    
RETURN (bop);

END

Open in new window

0
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 

Author Comment

by:k heiutz
ID: 41800668
Hi Zberteoc ~ shoot sorry. Still getting just OK in action grid, no results grid.
0
 

Author Comment

by:k heiutz
ID: 41800683
Here is another possible clue.
This function ultimately will be called within a larger query as:

getbop(ThisTable.Pid,ThisTable.gid) AS Spot,

When I run the query with this line, it completes in 0.029 seconds and returns 'OK' in the action grid only. Without this line the query takes 3 seconds and returns >2000 records.
So this function also appears to stop the query from executing as well.

Thanks
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 41801042
do you have the same issue when running the query/function on the mysql command line?
if no, the issue would be on the MySQL Workbench
if yes, the issue is on the function side;
please test and inform us about that.

also, I would recommend to have the parameter names renamed to avoid name clashes...
CREATE DEFINER=`mysql`@`%` FUNCTION `GetBOP`(p_pID bigint, p_gameid integer) RETURNS int(11)
    DETERMINISTIC
BEGIN
declare v_bop int;
set v_bop=0;

SELECT t.myfield INTO v_bop from mytable t where t.Playerid = p_pID and t.gameid = p_gameid;

If v_bop > 9 then
   set v_bop = 10;
end if;
    
RETURN (v_bop);

END

Open in new window

0
 

Author Comment

by:k heiutz
ID: 41802210
Hi Guy;
It is working! I tested thru cmd line and got an error
ERRR 1172 (42000): Result consisted of more than one row.

Then I changed the parameter names, referenced the table and variable names as you suggested and it is working 100%.

Thank you so much!
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question