Calling Mysql Sproc return no results


#1

Hi,

I have the following sp in mysql.

CREATE DEFINER=dreamfactory@% PROCEDURE sp_distance_from_clinic(IN service_id int, IN orig_lat float,IN orig_long float)
BEGIN

set @bounding_distance=1;

SELECT
distinct(location_id), latitude, longitude,
((ACOS(SIN(@orig_lat * PI() / 180) * SIN(latitude * PI() / 180)
+ COS(@orig_lat * PI() / 180) *
COS(latitude * PI() / 180) *
COS((@orig_long - longitude)
* PI() / 180)) * 180 / PI()) * 60 * 1.1515)
AS distance
FROM filter_service
WHERE
(
latitude BETWEEN (@orig_lat - @bounding_distance) AND (@orig_lat + @bounding_distance) AND service_id = service_id
AND longitude BETWEEN (@orig_long - @bounding_distance) AND (@orig_long + @bounding_distance) AND service_id = service_id
)
ORDER BY distance ASC
limit 20;
END

Can test that it’s working by calling

CALL dreamfactory.sp_distance_from_clinic(17, -37.869563,144.99604); in the datatabase

When I try to make the call vis the restul IO docs. Here’s my input body…

{
“params”: [
{
“name”: “service_id”,
“param_type”: “IN”,
“value”: 1
},
{
“name”: “orig_lat”,
“param_type”: “IN”,
“value”: -37.869563
},
{
“name”: “orig_long”,
“param_type”: “IN”,
“value”: 144.99604
}
]
}

I’m getting back a 200; but no data.


#2

Fixed. Should not have been using “@” for params.