This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Feb 21, 2013 5:14 AM by TobiasE RSS

Resource Templates : Strategy for dealing with PUT and POST

816944 Newbie
Currently Being Moderated
Hi,

I'd like some advice on best practice for dealing with inserts and updates through Resource
Templates.

I see it that I have 2 options:
(a) Take my data in via URI parameters OR
(b) in the content body as application/json

With option (a), It goes like this:

update my_table set
col1 = {uri param1},
col2 = {uri param2}
etc. etc.

With approach (b),I am converting the json blob to a varchar2 and
using pl/json to parse it.
This works fine, Until the 4000 varchar2 limit is reached.

I will be receiving data as application/json in the content body by default
so option (b) would suit me best, but it seems messy to do it within
the resource template. I could offload to a database procedure for processing
but that's another layer in the system to maintain.


I'd be interested in others experiences with this and how you approached it / best
practice for doing this.


Kudos to whoever came up with the resource template idea in Oracle and putting it in the Apex listener, I'm surprised
that it hasn't gained more attention. I hope that a full production version is released soon.
  • 1. Re: Resource Templates : Strategy for dealing with PUT and POST
    Colm Divilly Journeyer
    Currently Being Moderated
    Thanks for the positive feedback, unfortunately I don't have a straightforward answer to your query regarding how to handle JSON payloads for create/update operations.

    As you say, you can pass parameters directly into the pl/sql block via URI parameters, but this will only work for small amounts of data, or you will encounter URI path length problems.

    You can also pass data via HTTP Request Headers, this is typically a good pattern when your data actually consists of one major part (a document, image, whatever) and some meta-data describing the major part, you would pass the major part as the content body, and the meta data as HTTP headers. For example the Atompub protocol uses the Slug header to suggest a name and/or title for a media resource ^[1]^.

    Alternatively you can submit a content body of type: application/x-www-form-urlencoded (a HTML Form) and each field in the form will be automatically converted to a parameter for the PL/SQL block just as a URI parameter would be.

    The challenge with handling JSON data is that there can be huge variability in the structure of the JSON data, it might be a single object, it might be an array of objects, it might be a deeply nested graph of objects and arrays. So similar to XML you need a proper JSON parser to process a JSON payload properly (or convert the JSON to some XML dialect and use the native DB XML capabilities to further process the data).

    Out of interest what structure does your JSON follow, and what data in your relational model does it reflect, a single row of a table, many rows of a single table, a conflation of data from several different tables?

    *[1]* http://tools.ietf.org/html/rfc5023#section-9.7

    Edited by: Colm Divilly on Feb 22, 2011 7:58 AM
  • 2. Re: Resource Templates : Strategy for dealing with PUT and POST
    816944 Newbie
    Currently Being Moderated
    Thanks for the reply Colm,

    We will never be passing in nested JSON objects, We will always only be dealing
    with single tables,and will more often than not only ever pass in single rows, We are always in control of the format
    of the JSON coming in, so I don't see a problem with the structure of the incoming JSON actually.

    It would be*awesome* if there was some kind of inbuilt functionality to make available to the pl/sql block the content of the JSON payload
    without using pl/json or similar to do it, even if it only provided this for single json objects. I'd see this as important, if not
    more important than providing functionality for application/x-www-form-urlencoded payloads for the reason that we will
    not always be calling the resource templates from a browser, but would like to publish an API to our system.
  • 3. Re: Resource Templates : Strategy for dealing with PUT and POST
    Colm Divilly Journeyer
    Currently Being Moderated
    In APEX Listener 1.1 the PL/SQL Hander will automatically convert JSON properties to implicit parameters. Note this will only work for simple JSON objects, arrays or nested object are not supported.

    For example say you have the following Resource Template:

    URI Template: demo
    HTTP Method: POST
    PL/SQL Block:
    begin
     insert into scott.json_demo values(:title,:description);
    end;
    which uses the following table (under schema: scott):
    create table json_demo ( title varchar2(20), description varchar2(1000) );
    grant all on json_demo to apex_public_user;
    now doing a POST like the following:
    POST /apex/demo HTTP/1.1
    Content-Type: application/json
    Content-Length: 97
    
    {
     "title": "APEX Listener 1.1",
     "description": "APEX Listener 1.1 is now available for download"
    }
    will cause the values of the "title" and "description" JSON properties to be bound to the :title and :description parameters automatically.
  • 4. Re: Resource Templates : Strategy for dealing with PUT and POST
    816944 Newbie
    Currently Being Moderated
    Colm,

    That's fantastic, Thank you very much. We will definitely be using this a lot.
  • 5. Re: Resource Templates : Strategy for dealing with PUT and POST
    trent Expert
    Currently Being Moderated
    This looks quite nice :-)
  • 6. Re: Resource Templates : Strategy for dealing with PUT and POST
    816944 Newbie
    Currently Being Moderated
    Having trouble with this, I haven't gotten it to work.

    The JSON I'm posting is valid but the following is always thrown when passing data. :

    "The server encountered an internal error () that prevented it from fulfilling this request.
    java.util.regex.PatternSyntaxException: Illegal repetition near index 1

    \:{"title:thetitle","description:thedescription"}\b^"

    As you can see, the JSON is valid, just looks like something is not being escaped properly.
    Passing empty json,i.e {} works fine and creates a row on the table with null values.


    I'm posting like so:
    curl -X POST http://localhost:8080/apex/demo_json -H 'application/json' -d '{"title":"thetitle","description":"thedescription"}'


    Resource Template Config:

    <?xml version="1.0" encoding="UTF-8"?>
    <template xmlns="http://xmlns.oracle.com/apex/resource-template" pattern="demo_json">
    <etag/>
    <handler accept="application/json" method="post" type="plsql/block">
    <content><![CDATA[begin
    insert into json_demo values(:title,:description);
    end;
    ]]></content>
    </template>


    Appreciate if you could take a look, probably something simple.
  • 7. Re: Resource Templates : Strategy for dealing with PUT and POST
    Colm Divilly Journeyer
    Currently Being Moderated
    curl -X POST http://localhost:8080/apex/demo_json -H 'application/json' -d '{"title":"thetitle","description":"thedescription"}'
    should be:
    curl -X POST http://localhost:8080/apex/demo_json -H 'Content-Type: application/json' -d '{"title":"thetitle","description":"thedescription"}'
    ;)
  • 8. Re: Resource Templates : Strategy for dealing with PUT and POST
    816944 Newbie
    Currently Being Moderated
    Thanks Colm, It is of course working now.
  • 9. Re: Resource Templates : Strategy for dealing with PUT and POST
    user_Frédéric Newbie
    Currently Being Moderated
    Hi,
    I reproduce exactly your example but unfortunately I got an error 500 when I try to post the same body content:

    POST http://sirius:8080/apex/demo
    Content-Length: 97
    Content-Type: application/json
    {
    "title": "APEX Listener 1.1",
    "description": "APEX Listener 1.1 is now available for download"
    }


    Here is my ressource template
    <?xml version="1.0" encoding="UTF-8"?>
    <template xmlns="http://xmlns.oracle.com/apex/resource-template" pattern="demo">
    <etag/>
    <handler method="post" type="plsql/block">
    <content><![CDATA[declare
    begin
    -- :status :=insert_json_demo(:title,:description);
    insert into theosusr.json_demo values(:title,:description);
    end;
    ]]></content>
    </handler>
    </template>


    What could be the source of the error ?
    I turn in round for hours, many thanks for any help

    Frédéric
  • 10. Re: Resource Templates : Strategy for dealing with PUT and POST
    816944 Newbie
    Currently Being Moderated
    That looks fine,
    The Oracle error will be in the console output if you are running the listener in standalone mode,
    check this. You most likely have a simple oracle error.

    Post the error and we'll see what's wrong.
  • 11. Re: Resource Templates : Strategy for dealing with PUT and POST
    user_Frédéric Newbie
    Currently Being Moderated
    Hi
    I configure a standalone APEX listener with the command java -Dapex.home=E:\i3\apex_standalone -Dapex.port=8989 -Dapex.images=E:/i3/apex_standalone/i -jar E:\i3\apex_standalone\apex.war and it's work for APEX login.
    But how can I configure password for adminlistener to access /resourceTemplates or /listenerAdmin. Under Glassfish it's easy done.

    I thanks you for your expertise

    Best regards,
    Frédéric
  • 12. Re: Resource Templates : Strategy for dealing with PUT and POST
    816944 Newbie
    Currently Being Moderated
    You will be prompted for a passwords when you run the listener for the first time.

    Delete the files
    E:\i3\apex_standalone\apex-config.xml
    and
    E:\i3\apex_standalone\credentials

    Then run the listener again, enter the password you want to set for the adminlistener and managerlistener users.
  • 13. Re: Resource Templates : Strategy for dealing with PUT and POST
    user_Frédéric Newbie
    Currently Being Moderated
    Files deleted and it works perfectly!
    With standalone apex listener I could start my debug:
    In the console I see that my implicit parameters were not understood.
    I declared the two parameters of the PLSQL bloc (title and description) as IN parameters and the POST worked!

    A big thank you for your help, I feel less alone in my little corner of France

    Frédéric
  • 14. Re: Resource Templates : Strategy for dealing with PUT and POST
    jhkn Newbie
    Currently Being Moderated
    I would like to know if it is possible to disable this functionality in Apex Listener 1.1.3.243.11.40
    "In APEX Listener 1.1 the PL/SQL Hander will automatically convert JSON properties to implicit parameters. Note this will only work for simple JSON objects, arrays or nested object are not supported."

    The reason is that i have to pass the following JSON object

    PUT http://127.0.0.1:8080/apex/customer/1?source=Unknown HTTP/1.1
    Accept-Encoding: gzip,deflate
    Content-Type: application/json
    Content-Length: 923
    Host: 172.30.48.71:8080
    Connection: Keep-Alive
    User-Agent: Apache-HttpClient/4.1.1 (java 1.5)

    {
         "customer": {
              "address": [{
                   "city": "Breda",
                   "country": "Netherlands",
                   "address_id": 1,
                   "house_number": "1",
                   "house_number_suffix": "",
                   "street": "bredelaan",
                   "zipcode": "1234AA"
              },
              {
                   "city": "Breda",
                   "country": "Netherlands",
                   "address_id": 2,
                   "house_number": "2",
                   "house_number_suffix": "",
                   "street": "bredelaan",
                   "zipcode": "1234AB"
              }],
              "birthdate": "1950-01-01 00:00:00",
              "customerCards": "",
              "cards": [{
                   "barcode": "4004764007967",
                   "customerHash": "ABCDABCDABCDABCDABCDABCDABCDABCD",
                   "id": 1,
                   "name": "P. Marker",
                   "used": "Active"
              }],
              "customerOptOuts": [{
                   "mailingType": "",
                   "optedOut": "2012-11-07 16:35:53"
              }],
              "email": "r.marker@test.nl",
              "hash": "ABCDABCDABCDABCDABCDABCDABCDABCD",
              "id": 1,
              "initials": "R.",
              "phone": "",
              "prefix": "",
              "gender": "Male",
              "surname": "Marker"
         }
    }

    With the current functionality i'am stuck with the error
    400 - Bad Request
    json Expected a value but got: START_OBJECT

    I would like to bypass this automatic parsing of "application/json" data and just use the bind variables :contentType and :body to pass the data to the database.
    When I change the content_type to text/plain it is working correctly. But an other party is sending the data and I would like to use the "application/json" content type.

    Is this possible?

    Joost
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points