2. PHPRunner General questions
  3. [SOLVED] Rest API Stored procedure with IN parameters
This topic is locked
[SOLVED]

Rest API Stored procedure with IN parameters

 		6/27/2022 5:58:05 PM
PHPRunner General questions
dageci authorDevClub member

Hello,
I'm traying to enable Rest API in my app to insert some data to my database.
I'm using MySQL stored procedure with IN parameters.

After following the great video https://www.youtube.com/watch?v=MHr67SWh8xU

I was able to create the SP, create the custom SQL View and in Running the SQL I get asked for the parameters and when putting them in I get the result back.
But when I want to use it within url it doesn't pass rhe parameters to the stored procedures.
The API key is ok and privilages are ok, because I get success response.

I think that I'm doing something wrong in the Custom SQL View.

I have only the list view with this:

CALL sp_recordpunch(':{dtpunchdatetime}', :{varcarduid}, ':{vardeviceid}', :{intstatusid});

The url is:

MYWEBPAGE//api/v1.php?apikey=MYAPIKEY&table=sp_recordpunch&action=list&dtpunchdatetime=2022-02-24T17:15:11&varcarduid=2222&vardeviceid=1&intstatusid=1

The response is:

{"data":[{"varcarduid":"0","fullname":null,"varerror":"NO RECORDS"}],"success":true}

I have returned the varcarduid so that I can see what is the parameter value inside the stored procedure. As you can see in url is 2222 and from the results from the SP it is 0

And here is the code for the MySQL SP:


CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_recordpunch`(
IN dtpunchdatetime datetime,
IN varcarduid VARCHAR(16),
IN vardeviceid VARCHAR(50),
IN intstatusid int
)
BEGIN
DECLARE intuserid INTEGER;

SELECT userid
INTO intuserid
FROM rfidcards
WHERE cardactive=1 and carduid=varcarduid;

IF (intuserid > 0) THEN
INSERT INTO punches(punchdatetime, carduid, deviceid, statusid, userid)
VALUES (dtpunchdatetime, varcarduid, vardeviceid, intstatusid, intuserid);

SELECT varcarduid, fullname, NULL As varerror
FROM ams_users
WHERE ID=intuserid;
ELSE
SELECT varcarduid, NULL As fullname , 'NO RECORDS' as varerror;
END IF;

END
``
![img alt](https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=29181&image=1&table=forumtopics)

![img alt](https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=29181&image=2&table=forumtopics)

![img alt](https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=29181&image=3&table=forumtopics)
admin 6/27/2022

If you want to use the URL parameters in your SQL , use this syntax:
:{request.value}

CALL sp_recordpunch(':{request.dtpunchdatetime}', :{request.varcarduid}, ':{request.vardeviceid}', :{request.intstatusid});
C
 		Craig Smith 6/29/2022

Brilliant !!! - this is exactly what I was looking for today.

Thanks for the question and response.
:)