3 Replies Latest reply: Sep 4, 2013 1:07 AM by Zack.L RSS

    Characterset problem when inserting data through Restful service.

    Zack.L
      Hi all,
      Am running:
      AL 1.1.4 using GlassFish Server Open Source Edition 3.1.2.2 (build 5) on OEL 4.8 in VM box A, locale en_US.UTF-8.
      Oracle database 10.2.0.4 with Apex 4.2.1 on OEL4.8 in VM box B, locale en_US.UTF-8, NLS_CHARACTERSET ZHS16GBK, NLS_LANGUAGE AMERICAN.

      Used the below Resource Template example:
      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;
      and below procedure ( scott's schema ):
      CREATE OR REPLACE
      PROCEDURE post(
          p_url     IN VARCHAR2,
          p_message IN VARCHAR2,
          p_response OUT VARCHAR2)
      IS
        l_end_loop BOOLEAN := false;
        l_http_req utl_http.req;
        l_http_resp utl_http.resp;
        l_buffer CLOB;
        l_data       VARCHAR2(20000);
        value        VARCHAR2(1024);
        C_USER_AGENT CONSTANT VARCHAR2(4000) := 'Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)';
      BEGIN
        -- source: http://awads.net/wp/2005/11/30/http-post-from-inside-oracle/
        -- Ask UTL_HTTP not to raise an exception for 4xx and 5xx status codes,
        -- rather than just returning the text of the error page.
        utl_http.set_response_error_check(false);
        -- Begin the post request
        l_http_req := utl_http.begin_request (p_url, 'POST', utl_http.HTTP_VERSION_1_1);
        -- Set the HTTP request headers
        utl_http.set_header(l_http_req, 'User-Agent', C_USER_AGENT);
        utl_http.set_header(l_http_req, 'content-type', 'application/json;charset=UTF-8');
        utl_http.set_header(l_http_req, 'content-length', LENGTH(p_message));
        -- Write the data to the body of the HTTP request
        utl_http.write_text(l_http_req, p_message);
        -- Process the request and get the response.
        l_http_resp := utl_http.get_response (l_http_req);
        dbms_output.put_line ('status code: ' || l_http_resp.status_code);
        dbms_output.put_line ('reason phrase: ' || l_http_resp.reason_phrase);
        LOOP
          EXIT
        WHEN l_end_loop;
          BEGIN
            utl_http.read_line(l_http_resp, l_buffer, true);
            IF(l_buffer IS NOT NULL AND (LENGTH(l_buffer)>0)) THEN
              l_data    := l_data||l_buffer;
            END IF;
          EXCEPTION
          WHEN utl_http.end_of_body THEN
            l_end_loop := true;
          END;
        END LOOP;
        dbms_output.put_line(l_data);
        p_response:= l_data;
        -- Look for client-side error and report it.
        IF (l_http_resp.status_code >= 400) AND (l_http_resp.status_code <= 499) THEN
          dbms_output.put_line('Check the URL.');
          utl_http.end_response(l_http_resp);
          -- Look for server-side error and report it.
        elsif (l_http_resp.status_code >= 500) AND (l_http_resp.status_code <= 599) THEN
          dbms_output.put_line('Check if the Web site is up.');
          utl_http.end_response(l_http_resp);
          RETURN;
        END IF;
        utl_http.end_response (l_http_resp);
      EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line (sqlerrm);
        raise;
      END;
      and executing in sqldeveloper 3.2.20.09 when connecting directly to box B as scott:
      SET serveroutput ON
      DECLARE
        l_url      VARCHAR2(200)   :='http://MY_IP:8080/apex/demo';
        l_json     VARCHAR2(20000) := '{"title":"thetitle 龙 ","description":"thedescription"}';
        l_response VARCHAR2(30000);
      BEGIN
        post( p_url => l_url, p_message =>l_json, p_response => l_response);
      END;
      which resulted in :
      anonymous block completed
      status code: 200
      reason phrase: OK
      
      TITLE        DESCRIPTION
      thetitle ?  thedescription
      The chinese character 龙 becomes ?.

      Had also include the below to JVM options under server-config which is the running instance:
      -Dfile.encoding=UTF-8
      -Djava.encoding=UTF-8

      with same result.

      But if i do the below insert directly in sqldeveloper, data is correct:
      insert into scott.json_demo values('thetitle 龙 ','thedescription');
      and when running Apex's Sample Database Application, i can input and display chinese characters with no problem to Product Description.

      Please advise.
      Thanks in advance.
      Zack
        • 1. Re: Characterset problem when inserting data through Restful service.
          Colm Divilly-Oracle
          Thanks for your report, I was able to replicate the problem, there is a defect in the JSON Parser not choosing to parse text as UTF-8, but rather using the charset defined by file.encoding. When testing in standalone mode I found that I was able to workaround the issue by specifying the -Dfile.encoding=UTF-8 JVM command line option, this forces the correct charset and the problem does not occur.

          I recommend trying this option again, you could try setting it via the JAVA_TOOL_OPTIONS environment variable:

          http://docs.oracle.com/javase/7/docs/webnotes/tsg/TSG-VM/html/envvars.html
          • 2. Re: Characterset problem when inserting data through Restful service.
            Zack.L
            Hi Colm,

            Had tried as suggested but same result.
            Am i missing something ?
            Please see log below:
            Picked up JAVA_TOOL_OPTIONS: -Dfile.encoding=UTF-8
            INFO: Starting: /home/oracle/al114/apex.war
             See: 'java -jar apex.war --help' for full range of configuration options
            INFO: Extracting to: /home/oracle/apex
            INFO: Using classpath: file:/home/oracle/apex/apex/____embedded/start.jar:file:/home/oracle/apex/apex/WEB-INF/lib/commons-fileupload-1.2.1.jar:file:/home/oracle/apex/apex/WEB-INF/lib/apex.jar:file:/home/oracle/apex/apex/WEB-INF/lib/xdb-11.2.0.jar:file:/home/oracle/apex/apex/WEB-INF/lib/ojdbc6.jar:file:/home/oracle/apex/apex/WEB-INF/lib/ucp.jar:file:/home/oracle/apex/apex/WEB-INF/lib/je-4.0.103.jar:file:/home/oracle/apex/apex/WEB-INF/lib/ojmisc.jar:file:/home/oracle/apex/apex/WEB-INF/lib/poi-3.6-20091214.jar:file:/home/oracle/apex/apex/WEB-INF/lib/xmlparserv2-11.2.0.jar:
            INFO: Starting Embedded Web Container in: /home/oracle/apex
            Jan 19, 2013 1:48:38 PM ____bootstrap.Deployer deploy
            INFO: Will deploy application path=/home/oracle/apex/apex/WEB-INF/web.xml
            Jan 19, 2013 1:48:39 PM ____bootstrap.Deployer deploy
            INFO: deployed application path=/home/oracle/apex/apex/WEB-INF/web.xml
            Using config file: /home/oracle/apex/apex-config.xml
            -- listing properties --
            PropertyCheckInterval=60
            ValidateConnection=true
            MinLimit=1
            MaxLimit=10
            InitialLimit=3
            AbandonedConnectionTimeout=900
            MaxStatementsLimit=10
            InactivityTimeout=1800
            MaxConnectionReuseCount=1000
            APEX Listener version : 1.1.4.195.00.12
            APEX Listener server info: Grizzly/1.9.18-o
            Jan 19, 2013 1:48:39 PM com.sun.grizzly.Controller logVersion
            INFO: Starting Grizzly Framework 1.9.18-o - Sat Jan 19 13:48:39 SGT 2013
            INFO: http://localhost:8080/apex started.
            also tried :
            Picked up JAVA_TOOL_OPTIONS: -Dfile.encoding=UTF-8 -Djava.encoding=UTF-8
            Regards
            Zack

            Edited by: Zack.L on Jan 19, 2013 3:18 PM

            Had added the below to my .bash_profile:
            export JAVA_TOOL_OPTIONS="-Dfile.encoding=UTF-8"
            and also :
            export JAVA_TOOL_OPTIONS="-Dfile.encoding=UTF-8 -Djava.encoding=UTF-8"
            • 3. Re: Characterset problem when inserting data through Restful service.
              Zack.L

              Hi Colm,

               

              Just found out that problem was in my POST procedure.

              When using setting via the JAVA_TOOL_OPTIONS environment variable and using Firefox Resclient tool, everything is fine.

               

              Regards

              Zack