8 Replies Latest reply on Oct 20, 2017 1:27 PM by thatJeffSmith-Oracle

    Looking for Steer on web service

    PJ

      Hi All,

       

      I have been getting a little frustrated with trying to create a simple web service using ORDS.  I just need to get started.  The requirement is quite simple.  I have a simple IOT device that will communicate with the database by sending raw data via a POST request.  The device will normally simply receive an ACK response as a raw payload. Occasionally the response may contain a command as raw data that the firmware interprets.  (The engineers built the device and firmware - not me - I am just trying to do the server side)

       

      So - I just want to handle a simple POST with a raw body.  (I can take care of the raw content in PL/SQL no problem).  I will want to receive and send a response with a raw body.  Related - what is the appropriate mime type for the header - Content-Type?

       

      I simply cannot seem to do it with ORDS.  I want to write my own Web service using UTL_HTTP or whatever - but I am just spinning in circles.  Can someone point me to an example on which I can build out the functionality. I just need to be able to pass the payload back and forth with a POST method, and I can take care of the rest  Is that too much?

       

      Should I be looking to node.js or something else?

       

      thanks in advance.

       

      Paul

        • 1. Re: Looking for Steer on web service
          thatJeffSmith-Oracle

          I have a simple IOT device that will communicate with the db by sending raw data via a POST request. 

          Install and configure ORDS for your database.

           

          Create a restful service module, with a POST handler.

           

          In your plsql/sql block for the handler, you can pull say data with with :body bind variable, or even pull data off the http request headers. Do your DB work. Then you can set response variables to have data sent back down to the IOT device.

           

          We have examples in the docs - there's an image gallery that shows how to send up an image/blob via POST. I, Kris, Colm, and Oracle-Base all have various examples. No need to use UTL_HTTP if you're going to use ORDS.

          • 2. Re: Looking for Steer on web service
            PJ

            Hello Jeff

             

            I have looked at this - but it is not really helping.

             

            In the response I want to set the Content-Length of the raw body and return a raw payload.

             

            I do not see how to set any header values in ORDS for OUT except for X-APEX-FORWARD and X-APEX-STATUS-CODE.

            The bind variables all seem to map to the incoming POST request (which I have been able to handle) - I am stuck with generating the response.  This is how far I have gotten:

             

            HTTP/1.1 200 OK

            Date: Mon, 16 Oct 2017 19:43:53 GMT

            Server: GlassFish Server Open Source Edition  4.1.1

            X-Powered-By: Servlet/3.1 JSP/2.3 (GlassFish Server Open Source Edition  4.1.1  Java/Oracle Corporation/1.7)

            Content-Type: text/html; charset=UTF-8

            Content-Length: 38

            Connection: close

             

            But it is not right.

             

            I want to set the content type, content length and have a body of type raw.  So how do I pass those from the handler - which is a procedure in a package?

             

            The handler can generate all of this but I simply don't see how to pass it along to the response.

             

            Thank you again

            Paul

            • 3. Re: Looking for Steer on web service
              thatJeffSmith-Oracle

              Tim shows how to set the content type on the response here

              https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-http-headers-and-ords-specific-bind-variables

               

              He actually hows how to do a ton of stuff in that post.

               

              I have an example of how to pass back output from a stored procedure in the reponse body here

              https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-http-headers-and-ords-specific-bind-variables

              • 4. Re: Looking for Steer on web service
                PJ

                Hello Tim(?)

                 

                I very much appreciate your assistance - but I am still just not getting it to work.  Here is a test handler function that I am playing around with - once I get all of the pieces working I will write proper code.

                 

                 

                -- Initially test as procedure

                 

                 

                create or replace procedure handle_request (p_body     in blob,

                                                            p_req_len  in  varchar2) IS

                 

                  l_client_system   varchar2(1000);

                  l_body            varchar2(32000);

                  b_body            blob;

                  l_len             pls_integer;

                  l_req_len         varchar2(32);

                  l_res_length         pls_integer;

                  l_req_type        varchar2(128);

                  l_req_host        varchar2(128);

                  blob_res_payload     blob;

                  hex_res_payload     varchar2(256) := 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACCCCCCCCCCCCCCCCCCCCCCCC2509201710055301A10300000100';

                 

                begin

                 

                    -- This is my logger ....

                  PLJ_LG.PUSH('procedure','HANDLE_REQUEST');

                 

                  --l_body := dbms_lob.substr(p_body, 32000, 1 );

                  l_body := utl_raw.cast_to_varchar2(p_body);

                 

                   -- This is the response!

                 

                  --l_req_len := OWA_UTIL.get_cgi_env('CONTENT_LENGTH');

                  l_req_len := p_req_len;

                 

                  l_req_host := OWA_UTIL.get_cgi_env('host');

                  l_req_type := OWA_UTIL.get_cgi_env('content-type');

                 

                  PLJ_LG.D('p_req_host: '||l_req_host);

                  PLJ_LG.D('p_req_type: '||l_req_type);

                  PLJ_LG.D('p_req_len : '||l_req_len);

                 

                  l_len := utl_raw.length(b_body);

                 

                 

                  PLJ_LG.D('Will capture details etc');

                  PLJ_LG.D('body:    '||l_body);

                 

                 

                  insert into post_body(host,

                                content_type,

                                content_length,

                                body,

                                created_date)

                  values (l_req_host,

                          l_req_type,

                          l_req_len,

                          p_body,

                          sysdate);

                 

                  blob_res_payload := hextoraw(hex_res_payload);

                  l_res_length := utl_raw.length(blob_res_payload);

                 

                  OWA_UTIL.mime_header('application/json', FALSE);

                  HTP.p('Content-Type: application/octet-stream');

                  HTP.p('Content-Length: '||l_res_length);

                  OWA_UTIL.http_header_close;

                 

                  htp.p(hex_res_payload);

                 

                  PLJ_LG.POP;

                 

                end;

                /

                 

                Using Curl - this is the response:

                 

                C:\coding-temp\curl-7.55.1-win64-mingw\bin>curl -X POST --Header "Content-Type: application/octet-stream" --data-raw 31303130 -i http://xxxxxxxxxxxxxxxxxx/apex/xxxx/dev/

                HTTP/1.1 200 OK

                Date: Wed, 18 Oct 2017 09:41:55 GMT

                Server: GlassFish Server Open Source Edition  4.1.1

                X-Powered-By: Servlet/3.1 JSP/2.3 (GlassFish Server Open Source Edition  4.1.1  Java/Oracle Corporation/1.7)

                Content-Type: text/plain; charset=UTF-8

                Connection: close

                Transfer-Encoding: chunked

                 

                 

                Content-type: application/json

                Content-Type: application/octet-stream

                Content-Length: 42

                 

                 

                AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACCCCCCCCCCCCCCCCCCCCCCCC2509201710055301A10300000100

                 

                Now some issues - I cannot seem to ovrwrite the headers for the content type.  Also htp.p will not allow me to 'print' a raw string.  In thisexample I have had to use the hex representation.

                 

                Also - what is the Connection: close all about?

                 

                Thanks again - I really do appreciate your assistance

                Paul

                 

                • 5. Re: Looking for Steer on web service
                  PJ

                  Ok - I got the raw data in the body bit - I need to:

                   

                  WPG_DOCLOAD.DOWNLOAD_FILE(blob_res_payload);

                   

                  The only outstanding item is how to set the headers in the response.  If I can do that, then I think I am done.  Any ideas anyone?  The Content Type and Length are the most important.

                   

                  Thanks again

                  Paul

                  • 6. Re: Looking for Steer on web service
                    thatJeffSmith-Oracle

                    If this is an ORDS RESTful service you only need to define the response bind variables, and then assign them values in your pl/sql block.

                     

                    ORDS.DEFINE_PARAMETER(

                          p_module_name        => 'test2',

                          p_pattern            => 'media',

                          p_method             => 'POST',

                          p_name               => 'X-APEX-FORWARD',

                          p_bind_variable_name => 'location',

                          p_source_type        => 'RESPONSE',

                          p_param_type         => 'STRING',

                          p_access_method      => 'OUT',

                          p_comments           => NULL);

                     

                    ORDS.DEFINE_HANDLER(

                          p_module_name    => 'test2',

                          p_pattern        => 'media',

                          p_method         => 'POST',

                          p_source_type    => 'plsql/block',

                          p_items_per_page =>  0,

                          p_mimes_allowed  => '',

                          p_comments       => NULL,

                          p_source         =>

                    'declare

                    image_id integer;

                    begin

                    insert into gallery (title,content_type,image)

                                 values  (:title,:content_type,:body)

                                 returning id into image_id;

                    :status := 201;

                    :LOCATION := IMAGE_ID;

                    end;'

                          );

                    • 7. Re: Looking for Steer on web service
                      PJ

                      Hi Tim (?)

                       

                      Thank you -

                       

                      I think I have all of the bits now.  I will try to come back here and write a summary of how I got it sorted - to give back and help the next person ....

                       

                      HTTP/1.1 200 OK

                      Date: Fri, 20 Oct 2017 13:15:04 GMT

                      Server: GlassFish Server Open Source Edition  4.1.1

                      X-Powered-By: Servlet/3.1 JSP/2.3 (GlassFish Server Open Source Edition  4.1.1  Java/Oracle Corporation/1.7)

                      Conent-Length: 3

                      Content-Type: application/octet-stream

                      Connection: close

                      Transfer-Encoding: chunked

                       

                      Still have a lot of work to do - but I think I have all of the parts now

                       

                      thank you again

                      paul

                      • 8. Re: Looking for Steer on web service
                        thatJeffSmith-Oracle

                        My name is Jeff, but you're welcome.