Stored Procedure: Error 500, No function matches the given name and argument types

REST API
1

Hello,

I recently started using DreamFactory and want to call a stored procedure with parameters. However, I am unable to successfully call a procedure and I get the following error:

“message”: “Failed to call database stored procedure.\nCDbCommand failed to execute the SQL statement: SQLSTATE[42883]: Undefined function: 7 ERROR: function get_creches_latlng_distance(unknown, unknown, unknown) does not exist\nLINE 1: SELECT * FROM “get_creches_latlng_distance”($1,$2,$3)\n ^\nHINT: No function matches the given name and argument types. You might need to add explicit type casts.”,
“code”: 500

My stored procedure is defined as follows:

CREATE OR REPLACE FUNCTION "crechesSchema".get_creches_latlng_distance(
latitude0 double precision, longitude0 double precision, distance double precision) 
RETURNS SETOF "crechesSchema"."crecheTable"
LANGUAGE SQL
AS $$
SELECT *
FROM "crechesSchema"."crecheTable"
WHERE
     latitude IS NOT NULL AND
     longitude IS NOT NULL AND
     "crechesSchema".earth_distance(
        "crechesSchema".ll_to_earth(latitude, longitude),
        "crechesSchema".ll_to_earth(latitude0, longitude0)
     ) <= distance
$$;

I am trying to use the Live API for now. I use the callStoredFuncWithParams API for my schema with the following settings:
function_name: crechesSchema.get_creches_latlng_distance (also tried with unqualified name: get_creches_latlng_distance)

body:

{
    "params": [
        {
            "name": "latitude0",
            "value": 3.5
        },
        {
            "name": "longitude0",
            "value": 50.2
        },
        {
            "name": "distance",
            "value": 20000.0
        }
    ]
}

What I am unable to understand is why the function is not found and why the types of the parameters are “unknown” in the error message.

Could someone provide some clues/ideas?

Best Regards,

2

I think you should probably be making this call to _func not _proc.
You can verify this by going to API Docs and doing a GET on _func (set refresh to true.)
It will return all the stored functions it has access to. This will also tell you the proper name to use when calling it.

3

Hello,

Thank you for replying. The problem seems to have corrected itself. It seems to be really unpredictable. I am having to clear the Swagger cache every so often and then wait for arbitrary amount of time for it to work.

While the GET _func call now works and I see the functions defined, I still cannot call the function itself from my application.

When i use POST _func/<function_name> along with the parameters, It works in the API Console but not from my application. I get 403 Forbidden as response now.

I already checked the CORS settings and have everything enabled so:

  • GET/POST/PUT/DELETE/PATCH/MERGE Enabled

I also tried adding separate rules for GET and POST but it doesn’t work in any scenario…

4

That can’t have anything to do with your DB or DreamFactory’s cache of your DB’s _func results. You shouldn’t have any unpredictability as long as you have set ?refresh=true when making your GET calls to list the functions available.

What error message is provided along with the 403 response? What error(s) are logged in your DreamFactory log when the 403 is returned?