Solved

MySQL function doesn't return a value

Posted on 2016-09-13
7
76 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:klheitz
  • 4
  • 2
7 Comments
 
LVL 142

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:klheitz
ID: 41798237
Hi Guy ~ no luck - I get the same results.
0
 
LVL 26

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

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

Author Comment

by:klheitz
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 142

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:klheitz
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Creating and Managing Databases with phpMyAdmin in cPanel.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now