1 2 Previous Next 17 Replies Latest reply: Mar 20, 2013 7:34 AM by Greg Jarmiolowski RSS

    how to post form data + blob to a web service?

    991742
      Hi,
      I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

      I created this stored procedure to get a blob from the db and post it to a web service.
      How do you stream the file? The files will typically be binary ones so that why I'm using the blob; but utl_http wants varchar2 or clob.
      How do I convert a blob to a clob?
      Can I combine the v_params with the blob/clob?

      Thanks,

      Here's the code
      function add_doc
      (request_id_in in number, lin_id_in in varchar2, file_id_in in number)
      return varchar2 is
      
          t_http_req     UTL_HTTP.req;
          t_http_resp    UTL_HTTP.resp;
          t_request_body VARCHAR2(30000);
          t_respond      VARCHAR2(30000);
          t_message      VARCHAR2(30000);
          t_start_pos    INTEGER := 1;
          t_output       VARCHAR2(2000);
          svc_username   VARCHAR2(200) DEFAULT 'LisaWebSvc';
          svc_password   VARCHAR2(200) DEFAULT 'tsarevna ego foundry smallish frugal draughty cromulent';
          counter1       NUMBER;
          t_file_name    varchar2(2000);
          req_body_blob  BLOB;
          temp_blob      BLOB;
          t_clob         clob;
          req_length     binary_integer;
          buffer           varchar2(2000);
          amount         pls_integer := 2000;
          offset        pls_integer := 1;
          vc1 varchar2(2000);
          sid            VARCHAR2(10000) DEFAULT NULL;
          
          host           VARCHAR2(30) DEFAULT NULL;
          v_params       VARCHAR2(4001) DEFAULT NULL;
          sql_error      NUMBER         := SQLCODE;
          sql_errmessage VARCHAR2(2000) := SQLERRM ;
          tf             BOOLEAN DEFAULT true;
              
          
      
      begin
      
           
          -- get the uploaded file;
          select FILE_NAME, BLOB_CONTENT into t_file_name, req_body_blob FROM UPLOADED_FILES WHERE record_number = file_id_in;
      
      -- request that exceptions are raised for error Status Codes
          UTL_HTTP.set_response_error_check(ENABLE => TRUE);
          -- allow testing for exceptions like UTL_HTTP.Http_Server_Error
          UTL_HTTP.set_detailed_excp_support(ENABLE => TRUE);
          
          t_http_req:= UTL_HTTP.begin_request( 'https://'||host||':8443/addDoc', 'POST', 'HTTP/1.1');
       
          v_params := 'svc_username='||svc_username||'&svc_password='||utl_url.escape(svc_password,tf)||'&folderID=1004943&fileName='||t_file_name||'&userName='||l_id_in||'&inputFile=';
          UTL_HTTP.set_header(r => t_http_req, name => 'user-agent', value => 'mozilla/4.0');   
        
           /*Describe in the request-header what kind of data is sent*/ -- multipart/form-data
       
          UTL_HTTP.set_header(t_http_req, 'Content-Type', 'application/x-www-form-urlencoded');
       
          /*Describe in the request-header the length of the data*/
       
          --UTL_HTTP.set_header(t_http_req, 'Content-Length', LENGTH(v_params));   
      
      -- if the file is greater than 32K then chunk the request. We may have to convert to RAW format...
      --    req_length := DBMS_LOB.getlength(req_body_blob);
          
          -- combine the v_params and the file data into a blob and then write it out to the service
          --dbms_lob.append(temp_blob, utl_raw.cast_to_raw(v_params)); 
      --    dbms_lob.writeappend(temp_blob, length(v_params), utl_raw.cast_to_raw(v_params));
      --    dbms_lob.append(temp_blob, req_body_blob);
      
          t_clob := t_clob || v_params;
          t_clob := t_clob || flex_ws_api.blob2clobbase64(temp_blob);
          
          req_length := DBMS_LOB.getlength(t_clob);
            
          if req_length <= 32767 then
      
              UTL_HTTP.set_header(t_http_req, 'Content-Length', req_length);   
              UTL_HTTP.write_text(t_http_req, t_clob);
      
          else
       
              UTL_HTTP.set_header(t_http_req, 'Transfer-Encoding', 'chunked');     
              
              while (offset < req_length)
                LOOP
                  DBMS_LOB.read(t_clob, amount, offset, buffer);
                  UTL_HTTP.write_text(t_http_req, buffer);
                  offset := offset + amount;
                END LOOP;
      
          END IF;
       
           /*make the actual request to the webservice and catch the response in a variable*/
          t_http_resp:= UTL_HTTP.get_response(t_http_req);
       
          BEGIN
            utl_http.read_text(t_http_resp, t_message);
            UTL_HTTP.end_response (t_http_resp);
          EXCEPTION
          WHEN utl_http.end_of_body THEN
            NULL;
            UTL_HTTP.end_response (t_http_resp);
          END;
       
          num1 := instr(t_message,'<objID>');
          num2 := instr(t_message,'</objID>');
       
          vc1 := substr(t_message,num1+7,num2-num1-7);  
          
          RETURN T_MESSAGE;
      
      end add_doc;
        • 1. Re: how to post form data + blob to a web service?
          Billy~Verreynne
          user9952602 wrote:

          How do I convert a blob to a clob?
          For a web service? Base 64 encoding. See http://en.wikipedia.org/wiki/Base64 for basic details. Sample code in message {message:id=10364104}.

          Make sure that the Mime header for this is send correctly, prior to streaming the encoded binary data as text.
          • 2. Re: how to post form data + blob to a web service?
            991742
            Thanks Bill,
            I guess I'm on the right track then because I am base64 encoding the blob to clob but the file data isn't appearing (the rest of the form data is)
            The only question now is how do I send the mime header?
            I'm currently sending 'application/x-www-form-urlencoded' as the content type
            Do I send another set header call with the specific mime type of the document?

            Thanks,
            • 3. Re: how to post form data + blob to a web service?
              Billy~Verreynne
              I would use Firefox and a HTTP header spy plug-in (there is a couple I think) - and do a small file upload of a binary file to the web service.

              The plug-in will display complete send and receive headers. The send header for the POST would be the one of interest - as that is what one will need to duplicate using UTL_HTTP.

              I always find it easier working with actual live/raw data (be that SOAP or HTTP) as that allows me to see exactly what my code needs to generate. The RFCs are also very useful - but are a lot easier to understand when having an actual copy of the data structure and format, transmitted.

              As for Mime headers. Never really had to look in any detail at Mime headers and formatting via HTTP. But as Mime itself is a specification, I assume that these will closely match to that send via SMTP - except for name-values passed (e.g. HTTP does not need e-mail name-values such as e-mail subject and instead have form fields).

              I have posted a basic Mime payload, for a base64 encoded GIF, in {message:id=4402801}. Sending this via HTTP should be similar.
              • 4. Re: how to post form data + blob to a web service?
                991742
                thanks Bill,
                I cobbled together this snippet; the file is nowhere to be found on the other end. but all the other data comes through
                I'm not certain but I suspect the binary file is becoming corrupt being converted to base64 clob; but again I don't really know what the problem is.
                Does the receiving Java web service need to convert the file data to some other format?
                Maybe you can see what's wrong with the code:
                Thanks,
                    t_http_req:= UTL_HTTP.begin_request( 'https://'||host||':8443/addDoc', 'POST', 'HTTP/1.1');
                
                    UTL_HTTP.set_header(r => t_http_req, name => 'user-agent', value => 'mozilla/4.0');   
                  
                     /*Describe in the request-header what kind of data is sent*/ -- 'multipart/mixed; boundary="gc0p4Jq0M2Yt08jU534c0p"'
                 
                  --  UTL_HTTP.set_header(t_http_req, 'Content-Type', 'application/x-www-form-urlencoded');
                    UTL_HTTP.set_header(t_http_req, 'Content-Type', 'multipart/form-data; boundary="SECBOUND"');
                --    UTL_HTTP.set_header(t_http_req, 'Transfer-Encoding', 'chunked');
                    
                    -- create the mixed multipart message -- create the body first and then add the size to the front afterwards -- folderID hardcoded for now
                    t_multimsg := 
                 --        crlf
                         '--SECBOUND' || crlf
                        || 'content-disposition: form-data; name="svc_username" ' || crlf
                        || 'content-type: plain/text ' || crlf
                        || crlf 
                        || svc_username || crlf
                        || '--SECBOUND' || crlf 
                        || 'content-disposition: form-data; name="svc_password" ' || crlf
                 --       || 'content-type: plain/text ' || crlf
                        || crlf
                        || svc_password || crlf
                        || '--SECBOUND' || crlf
                        || 'content-disposition: form-data; name="folderID" ' || crlf
                        || crlf
                        || 1004943 || crlf        
                        || '--SECBOUND' || crlf
                        || 'content-disposition: form-data; name="fileName" ' || crlf
                        || crlf
                        || t_file_name  || crlf
                        || '--SECBOUND' || crlf
                        || 'content-disposition: form-data; name="userName" ' || crlf
                        || crlf
                        || lincoln_id_in || crlf
                        || '--SECBOUND' || crlf
                        || 'content-disposition: attachment; name="inputFile"; filename="' || t_file_name || '" ' || crlf
                        || 'Content-Type: ' || t_mime_type || ' name="' || t_file_name || '" ' || crlf
                        || 'Content-Transfer-Encoding: base64' || crlf
                        || crlf;
                      --  || insert blob data here       
                
                    dbms_lob.createtemporary(t_clob, true);
                    dbms_lob.createtemporary(temp_blob, true);
                
                    t_clob := t_clob || t_multimsg;
                    t_clob := t_clob || flex_ws_api.blob2clobbase64(req_body_blob) || crlf || crlf || '--SECBOUND--';
                
                     req_length := DBMS_LOB.getlength(t_clob);
                    
                    -- add content-length to the front of the body
                --    UTL_HTTP.set_header(t_http_req, 'Content-Length', req_length); 
                 
                     t_clob := 'POST ' || '//addDoc HTTP/1.1' || crlf || ' Host: ' || host || crlf || ' Content-Length: ' || req_length || crlf || ' Content-Transfer-Encoding: 7bit ' || t_clob;
                   
                    if req_length <= 32767 then
                
                        UTL_HTTP.set_header(t_http_req, 'Content-Length', req_length);   
                        UTL_HTTP.write_text(t_http_req, t_clob);
                
                    else
                
                        UTL_HTTP.set_header(t_http_req, 'Transfer-Encoding', 'chunked'); 
                        
                        while (offset < req_length)
                          LOOP
                            DBMS_LOB.read(t_clob, amount, offset, buffer);
                            UTL_HTTP.write_text(t_http_req, buffer);
                            offset := offset + amount;
                          END LOOP;
                
                    END IF;
                 
                     /*make the actual request to the webservice and catch the response in a variable*/
                    t_http_resp:= UTL_HTTP.get_response(t_http_req);
                 
                • 5. Re: how to post form data + blob to a web service?
                  Greg Jarmiolowski
                  On this line:

                  || 'Content-Type: ' || t_mime_type || ' name="' || t_file_name || '" ' || crlf

                  I don't think that name should go there and if it does it needs a semi-colon separating it from the t_mime_type value.
                  • 6. Re: how to post form data + blob to a web service?
                    Billy~Verreynne
                    If you were using UTL_TCP, you would have had to do the entire HTTP header. This is not necessary with UTL_HTTP.

                    So the following line does not make sense:
                     t_clob := 'POST ' || '//addDoc HTTP/1.1' || crlf || ' Host: ' || host || crlf
                    This is handled by the UTL_HTTP.begin_request() call. This call creates the basic HTTP header - to which you can add using UTL_HTTP.set_header(). When the header is completed, the UTL_HTTP.write_text() call is used to provide the request body.

                    The first call to UTL_HTTP.write_text() closes the header. Subsequent UTL_HTTP.set_header() calls should fail according to how I read the docs.

                    Have a look at the documentation that details the HTTP Requests Subprograms.
                    • 7. Re: how to post form data + blob to a web service?
                      991742
                      Greg: Thanks, I added the semi colon.
                      Bill: I removed the header and it still (almost) works;

                      I still have the same issue; the file never makes it through. Should I be using UTL_FILE with the POST?
                      And I've been pulled off this task. The team I'm supported decided to use something else...
                      Oh well,
                      Thanks for all your support, I appreciate it.
                      • 8. Re: how to post form data + blob to a web service?
                        Greg Jarmiolowski
                        I'm troubleshooting a similar process. I'm going to run it through Fiddler so I can see what really comes out of UTL_HTTP.
                        • 9. Re: how to post form data + blob to a web service?
                          991742
                          Hey Good News!
                          I got it working!
                          Since I couldn't debug the situation accurately, I created a simple html form to upload the document to my service and I got a 413 web transport error (file too large)
                          Which as it turns out it was actually an IIS problem.
                          Specifically, it was the UploadReadAheadSize which increases the max buffer size for an incoming SSL preload request
                          The default value was set at 200k. We increased it to 50 Meg (at least) and my stored procedure worked perfectly.
                          My code I posted earlier should work or it's close enough for you to figure out.
                          Thanks again to all for your support
                          • 10. Re: how to post form data + blob to a web service?
                            Billy~Verreynne
                            Appreciate the feedback (we seldom get that). :-)

                            Yeah, having a server config error contributing to a problem like this, obfuscates the error and make it difficult to figure out what exactly is causing the error.

                            I prefer what I call sanity tests. Take the problem code and reduce it to its bare minimum to achieve what is intended. Strip all additional complexities (like mass payload size) from it. Then get this simplified version to work. And once this work, it serves as the baseline (sanity) test - which one can add complexities too. Makes isolating problems caused by additional complexities a lot easier.
                            • 11. Re: how to post form data + blob to a web service?
                              Greg Jarmiolowski
                              Good news. I'm still stuck with mine and I have zero access to the server where I send the file.

                              I'm wondering if the character set of the database has a role. But without seeing what the server does with my post it's like playing darts blindfolded. Plus I get a 200 response telling me it received it.
                              • 12. Re: how to post form data + blob to a web service?
                                991742
                                Ok, I was a little premature; the file IS getting sent, however the bytes are all jumbled in the file.
                                So, again, I'm taking a binary (blob) file, converting to base64 encoded CLOB and then writing that out.
                                I'm guessing that I need to write_raw?
                                How do I get an uncorrupted file on the other end?
                                • 13. Re: how to post form data + blob to a web service?
                                  Greg Jarmiolowski
                                  This seems to be my issue. My next test is to convert the blob to a file using the same method I write it to the utl_http stream. Maybe utl_raw.convert to switch character sets?
                                  • 14. Re: how to post form data + blob to a web service?
                                    Billy~Verreynne
                                    Greg Jarmiolowski wrote:

                                    But without seeing what the server does with my post it's like playing darts blindfolded.
                                    Been in the exact same boat - and in that case, you make your own server. Using Apex for example. I would create an Apex app with an upload page (anonymous access, no authentication). Once this page works, I would call it from my manual upload PL/SQL code.

                                    So one database process (file upload client) talks to another database process (web file upload service) via http. Makes diagnosing issues a lot easier than trying to hit something blindfolded.
                                    1 2 Previous Next