Code Examples of Processing json Data with esProc

esProc can process json data. Here we’ll introduce some of the applications through examples. 1. Analyzing and generating json data with esProc; 2. Data-interchange between esProc and application program through json; 3.Reading jsonfile data inesProc.

A. Analyzing and generating json data with esProc

Generally speaking, json is a format used by webpage js program and Java’s server-side program (such as servlet) to interchange data. While data access between Java’s server-side program and the databases adopts SQL result set format. esProc can act as an intermediary in the data computation and data-interchange between the two formats.

In this example, we use esProc to query detailed information of a group of designated employees. Both data input and output will adopt json format. Table employee of database demo contains all information of the employees:esProc_json_1

 

esProc receives an EID list of json format and returns corresponding detailed information of employees in json format. The code is as follows:

1.esProc program test.dfx receives a parameter: json EID.

esProc_json_2

2. esProc completes json analysis, data processing and generates results in json format:

esProc_json_3

A1: Connect to database demo.

A2: Retrieve data from table employee.

A3: Use import@j function to parse the inputting json EID parameter (EID list in json format) and generate a table sequence containing only one field EID.

A4: Use align function to get from users data the employee information designated by A3.

A5: Convert employee information into json strings. 

A6: Return employee information of json format.

B. Inter changing json data between esProc and Java application

In the above example, esProc program is saved as test.dfx file to be called by Java application. Steps for calling the file are as follows:

1.Deploy esProc in Java application.

See esProc Tutorial for detail.

2.Call test.dfx in Java application.

Code example is as follows:

public void testDataServer(){

                   Connection con = null;

                   com.esproc.jdbc.Internal CStatementst;

                   try{

                            //Usersidlist injson format can be transmitted from browser-side to the program and converted into strings for use. Here process of receiving json data is omitted and value is assigned directly

                            String jsonEid=”[{EID:8},{EID:32},{EID:44}]”;

                            //Create a connection

                            Class.forName(“com.esproc.jdbc.InternalDriver”);

                            con= DriverManager.getConnection(“jdbc:esproc:local://”);

                            //Call stored procedure. test is the file name of dfx

                            st =(com.esproc.jdbc.InternalCStatement)con.prepareCall(“call test(?)”);

                            //Set parameters

                            st.setObject(1,jsonEid);

                            //Execute stored procedure

                            st.execute();

                            //Get result set

                            ResultSet set = st.getResultSet();

                            String jsonEmployee=null;

                            if (set.next()) jsonEmployee=set.getString(1);

                            //After getting detailed user information injson format, convert it into json objects and return them to browser-side. How to use json Employee is omitted here

                   }

                   catch(Exception e){

                            System.out.println(e);

                   }

                   finally{

                            //Close the connection

                            if (con!=null) {

                                     try {

                                               con.close();

                                     }

                                     catch(Exception e) {

                                               System.out.println(e);

                                     }

                            }

                   }

}

C.Reading and processing json file data inesProc

JSON file test.json contains information including class, serial number, names, subjects,scores, etc. Format is as follows:

[

    {

        “class”: “Class one”,

        “id”: 1,

        “name”: “Emily”,

        “subject”: “English”,

        “score”: 84

    },

    {

        “class”: “Class one”,

        “id”: 1,

        “name”: “Emily”,

        “subject”: “Math”,

        “score”: 77

    },

 

    ……

 

    {

        “class”: “Class one”,

        “id”: 7,

        “name”: “Nicholas”,

        “subject”: “PE”,

        “score”: 60

    }

It is convenient for esProc to perform the reading and computation of JSON data. After that the result is submitted to Java application in the format of JDBC result set. Steps are as follows:

  1. Developing esProc script

Use esProc editor to develop script (from JSON.dfx), read the json file, analyze it and complete the computation:

 

 

esProc_json_4

 

A1:Use read() to read json file in string format;

A2:Use import@j()function to parse the json file into a table sequence;

A3:Group students ID and summarize the total scores in A4;

A5:Sort by total scores in descending order and return result set through result in A7

 

  1. Java application callsfromJSON.dfx to present result.

Steps are omitted here for they are almost the same as those in the above example.

Advertisements

About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in Program Language and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s