1 2 Previous Next 22 Replies Latest reply: Feb 21, 2013 7:14 AM by TobiasE RSS

    Resource Templates : Strategy for dealing with PUT and POST

    816944
      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-Oracle
          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
            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-Oracle
              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
                Colm,

                That's fantastic, Thank you very much. We will definitely be using this a lot.
                • 6. Re: Resource Templates : Strategy for dealing with PUT and POST
                  816944
                  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-Oracle
                    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
                      Thanks Colm, It is of course working now.
                      • 9. Re: Resource Templates : Strategy for dealing with PUT and POST
                        user_Frédéric
                        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
                          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
                            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
                              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
                                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
                                  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