Format for inserting data through a stored procedure - Using the Postman Chrome Extension


#1

I’ve setup a stored procedure in the native MySQL DB that takes one parameter and inserts a new row.

I’ve tested it through the phpMyAdmin portion of the Bitnami installation and I’m able to provide a parameter and have it create the new record.

When I try to test the insert via Postman, passing in json parameters through the “Body” and “raw” options of a post request, I’m getting a “403 forbidden” error.

I’m logged in as a DSP admin. I’m able to access other stored procedures through get calls using Postman.

The url:
http://localhost/rest/db/_proc/my_proc/?app_name=my_app

The body of the json:
{
“params”:[
{
“name”:“my_parameter”,
“param_type”:“IN”,
“value”:“my_value”
}

]

}

I checked this documentation below, am I missing anything on what I’m passing to the DSP? I don’t expect any return values.

https://github.com/dreamfactorysoftware/dsp-core/wiki/SQL-Stored-Procedures


#2

Update:

I checked my logs, here:
/apps/dreamfactory/htdocs/log/web.core.log

And saw this entry. Seemed to be complaining about the Postman chrome extension:

[2015-08-11 21:06:48][app][ERROR ] CORS: unauthorized origin rejected > Source: http://localhost > Origin: chrome-extension://aicmkgpgakddgnaphhhpliifpcfhicfo

I went into the “CORS” section of the DSP admin pages and added this entry for the host:

chrome-extension://aicmkgpgakddgnaphhhpliifpcfhicfo

And granted it access to the post HTTP verb.

This allows me to connect. Now I’m not getting the 403 error.

I do have some more troubleshooting on my stored procedure, but at least I’m getting a response now.

-Brian


#3

Another Update:

I was able to get everything to work. Here’s some notes, in case it’s helpful to someone else.

I am using the “Postman” google chrome extension to send the http:// request to my DSP that I’ve setup a stored procedure on.

I used this site as a reference for setting up the Postman request:
http://stackoverflow.com/questions/26705782/sending-nested-json-object-using-postman

After setting up the URL:
http://localhost/rest/db/_proc/my_proc/?app_name=my_app
The settings for the json object were:
Body, raw, JSON(application/json)

I also used this post as a reference for setting up the stored procedure in the MySQL DB. The post from Harry was key. Notice how it sets up the stored procedure to return a value, based on whether the insert was a success or not:
http://community.dreamfactory.com/t/db-how-could-call-a-db-function/447/11

After I figured out the CORS issue above, I was still getting errors. I changed the input json to look like this and it worked! ** Note: the value for the schema result had to match exactly to the type being returned (i.e. “string”). I had it set to integer and it was failing with an error that didn’t indicate that**

{
“params”:[
{
“name”:“my_parameter”,
“param_type”:“IN”,
“value”:“my_value”
}
],

"schema": {
"result": "string"
},

"wrapper": "proc_result"

}

Hope that helps someone!

-Brian


#4

Thanks for the detailed solution Brian!

I often use jsonlint.com to validate my json. Then I will copy and paste it exactly as is into my REST tool. This prevents issues with json formatting.