Solved

MySQL function doesn't return a value

Posted on 2016-09-13
7
152 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

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

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.

Question has a verified solution.

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

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

734 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