Query Appery.io using Oracle APEX

Wed Nov 26, 2014 4:29 am

Thanks Yurii. I have another question with with PUT method.

First, I used the following code with (GET) method successfully to copy data from collection "Outlet_Details" where the column "Oracle_Flag" = Y to Oracle DB.

pre ....
-- service's input parameters

-- preparing Request...
l_http_request := UTL_HTTP.begin_request('https://api.appery.io/rest/1/db/collections/Outlet_Details?where=%7B%22Oracle_Flag%22%3A%22Y%22%7D'
, 'GET'
, 'HTTP/1.1');

-- ...set header's attributes
UTL_HTTP.set_header(l_http_request, 'X-Appery-Database-Id', '53f2dac5e4b02cca64021dbe');
--UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_param_list));

-- ...set input parameters
-- UTL_HTTP.write_text(l_http_request, l_param_list);

-- get Response and obtain received value
l_http_response := UTL_HTTP.get_response(l_http_request);

UTL_HTTP.read_text(l_http_response, l_response_text);

DBMS_OUTPUT.put_line(l_response_text);
l_list := json_list(l_response_text);
.../pre

No I tried to use the similar way with (PUT) method in order to update one column in the collection "Outlet_Details" where Oracle_Flag = Y, and update it to "N"

pre ...
-- service's input parameters
l_param_list := 'Oracle_Flag=N';

-- preparing Request...
l_http_request := UTL_HTTP.begin_request ('https://api.appery.io/rest/1/db/collections/Outlet_Details?where=%7B%22Oracle_Flag%22%3A%22Y%22%7D'
, 'PUT'
, 'HTTP/1.1');

-- ...set header's attributes
UTL_HTTP.set_header(l_http_request, 'X-Appery-Database-Id', '53f2dac5e4b02cca64021dbe');
UTL_HTTP.set_header(l_http_request, 'Content-Type', 'application/json');

-- ...set input parameters
UTL_HTTP.write_text(l_http_request, l_param_list);

-- get Response and obtain received value
l_http_response := UTL_HTTP.get_response(l_http_request);

UTL_HTTP.read_text(l_http_response, l_response_text);

DBMS_OUTPUT.put_line(l_response_text);

-- finalizing
UTL_HTTP.end_response(l_http_response);
.../pre

I get the error: "code":"DBSP007","description":"Serialization error"

Are the way I'm using Appery collection parameteres correct?

Thanks,

Wed Nov 26, 2014 1:38 pm

1) Request Content Type should be JSON instead urlEncoded
2) Format for updating collection row slightly differs from getting it from DB
curl -X PUT \
-H "X-Appery-Database-Id: DB_ID" \
[-H "X-Appery-Session-Token: "] \
-H "Content-Type: application/json" \
-d '{"":""}' \
{database_url}/COLLECTION_NAME/ < object_id

Thu Nov 27, 2014 10:38 am

Hi Egor,

Do you I should replace

preUTL_HTTP.set_header(l_http_request, 'Content-Type', 'application/json');/pre

with

preUTL_HTTP.set_header(l_http_request, 'Content-Type', JSON);/pre

What else do I need to change?

Thanks,

Thu Nov 27, 2014 10:34 pm

Hi Hawk,

Nope, you have correct content type.

You should pass all request parameters (where and operations) in payload request part as JSON. So there is no ability to pass parameters through the URL(like in GET request).

See details here: http://prntscr.com/5au3m5/direct

Unforturnalty i don't know how to do it with this "UTL_HTTP" please read documentation.

Regards.

Mon Dec 29, 2014 10:24 pm

Hi Hawk,

I guess you have some wrong symbols in your base64.. You need to escape them to use in JSON string.

More details here: http://stackoverflow.com/questions/30...

Did you try to pass empty value in base64 image?(just to test it)

Regards.

Wed Dec 31, 2014 2:30 am

Correct, I found there were newlines which after I removed it works fine.

Thanks Yurii. Happy new year!

Wed Dec 31, 2014 5:16 am

Hi Hawk,

Thanks for this update.

Glad it works now.

Regards.

Fri Jan 09, 2015 5:34 am

Hi Hawk,

Here is CURL to update certain user:

pre

curl -X PUT
-H "X-Appery-Database-Id: 544a5cdfe4b03d005b6233b9"
-H "X-Appery-Session-Token: c6771e2f-44ae-42ea-9946-5fa320578233"
-d "{"email":"stacy@somemail.com"}"
https://api.appery.io/rest/1/db/users/54539efee4b05c4ac3b9737f

/pre

More details here: http://devcenter.appery.io/documentat...

So i can see following inconsistent in your code:

Wrong url. Should be:

pre

//Where "54539efee4b05c4ac3b9737f" is user id that you want to update.
https://api.appery.io/rest/1/db/users/54539efee4b05c4ac3b9737f

/pre
You can not use "where" clause for this collection. (this collection does not support it).
Instead of "set" parameter - you should use appropriate parameter that you want to update.

Regards.

Fri Jan 16, 2015 9:11 am

Hi Yurii,

Thanks for the clarification. I have the following questions:

1) In point 3, you mentioned that I should use appropriate parameter in stead of "set". What is the appropriate parameter here. May you give example?

2) Since I cannot use "where", and I must use this format:
prehttps://api.appery.io/rest/1/db/users/54539efee4b05c4ac3b9737f/pre

Does that mean I can only update by the _id ?? What If I want to update the record based on some other field (e.g. username)? Is that possible? If yes, How?

3) Do I need to provide session-token? How to retrieve the value of the session-token?

-------------------------------------------------------------------

Aside from this, I need to use 'DELETE' method to delete all records in normal collection (not users). However, my request works only when I provide _id value:

preurl VARCHAR2(4000) := 'https://api.appery.io/rest/1/db/collections/Test_Member_Points_Details/54b6437fe4b04a26676ce472/pre

Is there anyway to delete without providing _id (e.g. using wildcard)? Or the only way to use 'GET' first to collect all_id's and then use 'DELETE' ?

Many thanks,

Tue Jan 20, 2015 1:48 am

Hi, Will I get any update on this here or should I post new thread?