5 Replies Latest reply on Feb 17, 2017 9:21 AM by Pavel_p

    How to convert an SQL returned string into a javascript object array?

    PPlatt

      APEX 4.2.6 on Linux Ora 12c

      Working on implementing a google map demo at https://ruepprich.wordpress.com/?s=maps

      I want to plot multiple addresses on google maps as multiple markers.  I have an oracle table that has long and lats with addresses.  Using the following query

      Select '{name: '''||geo_description||' '',lat: '''||Substr(GEO_LOCATION,1,Instr(GEO_LOCATION,',')-1)||''',lng: '''||Substr(GEO_LOCATION,Instr(GEO_LOCATION,',')+1)||'''}'
      From GEO_ADDRESS
      

      returns

                 {name: 'Digicel 1 ',lat: '18.5563585',lng: '-72.25996719999999'}
                 {name: 'Digicel 2 ',lat: '19.7577071',lng: '-72.2013075'}
      

      I put this in a PLSQL loop and return the string

      {name: 'Digicel 1 ',lat: '18.5563585',lng: '-72.25996719999999'},{name: 'Digicel 2 ',lat: '19.7577071',lng: '-72.2013075'}

      I can return it with or without '[',']' wrapped around the string.

       

      I need to turn this string into a javascript object to use in a function defined in the apex page header attribute Functional and Global Variable Declaration

      If I hardcode this in the js function declaration it works

      var data = [{name: 'Digicel 1 ',lat: '18.5563585',lng: '-72.25996719999999'},{name: 'Digicel 2 ',lat: '19.7577071',lng: '-72.2013075'}] ;

       

      but if I try to use a variable  in the assignment it doesn't work

      var data = "&P5_QUERY_TO_ARRAY.";

       

      How do I convert the string to a object array so it will work in my javascript function?

      I've tried the following and these DON'T work in APEX 4.2.6

      var data = $.parseJSON('[' + datastring + ']');

      var data = eval('(' + '&P5_QUERY_TO_ARRAY.' + ')');

      var data = (new Function("return [" + objectstring + "];")());

       

      I can't get the string into an object array which is needed for plotting multiple markers on Google maps

       

      thanks in advance

      PaulP

        • 1. Re: How to convert an SQL returned string into a javascript object array?
          Scott Wesley

          It really depends on how your page is set up, parameter passing may negate the need for direct refernce like that, but perhaps all you need is

          $v('P5_QUERY_TO_ARRAY')

          • 2. Re: How to convert an SQL returned string into a javascript object array?
            Pavel_p

            Hi Paul,

            the crucial part of the entire process is to pass a valid JSON object as an argument to the $.parseJSON function. There is a lot of online validators and this one https://jsonformatter.curiousconcept.com/ says:

             

            Error:Strings should be wrapped in double quotes.[Code 17, Structure 2]

            Error:Strings should be wrapped in double quotes.[Code 17, Structure 4]

            Error:Strings should be wrapped in double quotes.[Code 17, Structure 6]

            Error:Strings should be wrapped in double quotes.[Code 17, Structure 8]

            Error:Strings should be wrapped in double quotes.[Code 17, Structure 10]

            Error:Strings should be wrapped in double quotes.[Code 17, Structure 12]

            Error:Multiple JSON root elements[Code 22, Structure 14]

            Error:Strings should be wrapped in double quotes.[Code 17, Structure 15]

            Error:Strings should be wrapped in double quotes.[Code 17, Structure 17]

            Error:Strings should be wrapped in double quotes.[Code 17, Structure 19]

            Error:Strings should be wrapped in double quotes.[Code 17, Structure 21]

            Error:Strings should be wrapped in double quotes.[Code 17, Structure 23]

            Error:Strings should be wrapped in double quotes.[Code 17, Structure 25]

             

            So the valid JSON in your case would look something like

            {  
            "data":[  
            {  
            "name":"Digicel 1 ",
            "lat":"18.5563585",
            "lng":"-72.25996719999999"
            },
            {  
            "name":"Digicel 2 ",
            "lat":"19.7577071",
            "lng":"-72.2013075"
            }
            ]
            }
            

             

            Also lat and lng values are probably numbers, so they should not be double quoted, but maybe google can somehow handle it.

            There is no need to generate JSONs "manually", we have a really nice and convenient API for this purpose WRITE Procedure Signature 7 and since you're already on 12c, you can preferably use it's native JSON support.

            Let's take the example from apex_json.write procedure and generate a JSON, assign it to P1_DEPT_JSON item and then read it and parse it in JS.

            1) create a DA (or computation, process...whatever), Action = Set Value, type PL/SQL function body with the following code:

            DECLARE
                l_json_output clob;
                c sys_refcursor;
            BEGIN
                apex_json.initialize_clob_output;
                open c for select deptno, dname, loc from dept where deptno in (10, 20);
                apex_json.write(c);
                l_json_output := apex_json.get_clob_output();
                apex_json.free_output;
                --with dbms_lob.getlength check if lenght of l_json_output is not > then max varchar2 (32767) and eventually handle it
                --return clob (implicit conversion to varchar2)
                return l_json_output;
            END;
            

            Now you have stored your JSON object in P1_DEPT_JSON item. You can parse it to array using JS as follows:

            1) create a dynamic action

                 Action: execute JavaScript code (as Scott already proposed, use the JS api apex.item )

                 Code:

            var jsonObj = $v('P1_DEPT_JSON');
            var data = $.parseJSON(jsonObj);
            

            Regards,

            Pavel

             

            json_array.jpg

            • 3. Re: How to convert an SQL returned string into a javascript object array?
              PPlatt

              Hi swesley_perth  thanks for your reply but oddly enough I can't get $v('P5_QUERY_TO_ARRAY') to return anything??? It's what I tried first but when I used "&P5_QUERY_TO_ARRAY." it works. Strange yes. Not sure why $v on any item returns null??! $x('item').value doesn't work either...??

              PaulP

              • 4. Re: How to convert an SQL returned string into a javascript object array?
                PPlatt

                Thanks for your detailed reply Pavel_P.

                Great solution using native JSON support in the database...but the only problem is that my client is still on V4.2.6 of APEX...apex_json is only supported in APEX 5!

                I have found an alternative solution to my problem at http://blog.whitehorses.nl/2009/10/04/integrating-google-maps-in-oracle-apex/

                thanks for your help and will definitely pursue this course when upgrading to APX 5

                PaulP

                • 5. Re: How to convert an SQL returned string into a javascript object array?
                  Pavel_p

                  Hi Paul,

                  sorry, I forgot there was no apex_json in previous APEX versions.

                  P.