Solved

MySQL function doesn't return a value

Posted on 2016-09-13
7
86 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL: Updating SubQuery Match Faster 9 51
Problem with Simple PHP/mySQL Query 3 67
join tables 4 55
updating the date data 12 30
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
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…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

772 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