?
Solved

MySQL function doesn't return a value

Posted on 2016-09-13
7
Medium Priority
?
228 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

771 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