6 Replies Latest reply on Oct 24, 2018 6:21 PM by Ramesh Reddy

    OData Translator Paging Extension in Web Services Translator

    David Bailey Newbie

      Hello, all.

       

      I just joined the group today and have just been working in JBoss for 5 months.

       

      I have been tasked with adding the OData Translator paging capability to the Web Services Translator.  The OData translator paging is in ODataSQLVisitor.javaThe pagination is in ODataSQLVIsitor::buildURL(), in which both $top and $skip are arguments to uriBuilder.queryParam().  My team has written a test driver program that instantiates an ODataSQLVisitor object and tests the paging.

       

          @Test
          public void testLimit() throws Exception {
              helpExecute("SELECT ContactName FROM Customers limit 10", "Customers?$select=ContactName&$top=10");
          }

          @Test
          public void testLimitOffset() throws Exception {
              helpExecute("SELECT ContactName FROM Customers limit 10, 19", "Customers?$select=ContactName&$skip=10&$top=19");
          }

       

      The test driver's helpExecute() function actually instantiates the ODataSQLVisitor object and compares the URLs returned to the expected values.

          private void helpExecute(String query, String expected) throws Exception {

              Select cmd = (Select)this.utility.parseCommand(query);

              ODataSQLVisitor visitor = new ODataSQLVisitor(this.translator, utility.createRuntimeMetadata());

              visitor.visitNode(cmd);

              String actual = URLDecoder.decode(visitor.buildURL(), "UTF-8");

              assertEquals(expected, actual);

          }

          private void helpExecute(String query, String expected) throws Exception {

              Select cmd = (Select)this.utility.parseCommand(query);

              ODataSQLVisitor visitor = new ODataSQLVisitor(this.translator, utility.createRuntimeMetadata());

              visitor.visitNode(cmd);

              String actual = URLDecoder.decode(visitor.buildURL(), "UTF-8");

              assertEquals(expected, actual);

          }

       

       

      The function invokehttp() in the WS Translator seems to be the way to go to get the streams of data and translate the SQL queries to pass to the OData Translator.

      The link: https://docs.jboss.org/author/display/TEIID/OData+Translator has the line:

      "Native or direct query execution is not supported through OData translator.  However, user can use Web Service Translator's invokehttp method directory to issue a Rest based call and parse results using SQLXML."

       

      I think the data flow involves the database being directly connected to the OData Translator, with the queries going from OData to the database, and the results going from the database to OData.  In turn, the Web Services Translator sends the query to the OData Translator, which passes the results to Web Services, which in turn returns the results to the driver program (like the Test Java programs).  The results are non-paginated; the task is to include pagination, which is to be passed to the OData Translator to be used in the variables top and skip.

       

      Am I clear on what I am trying to do, and am I on the right track as far as the solution?  I would appreciate any help I can get.  Getting a SQL query from WS Translator in a form that the OData Translator can use is the biggest hurdle.

       

       

        • 1. Re: OData Translator Paging Extension in Web Services Translator
          Marc Kusters Newbie

          Why would you create a whole new translator when you can pass SQL/OData arguments directly through the QUERYSTRING option?

           

          Example:

          DECLARE STRING VARIABLES.queryString = QUERYSTRING('?$format=json');

           

          Use it here:

          invokeHttp(action => 'GET', endpoint => VARIABLES.queryString, stream => 'TRUE'))

           

          This way you can add a extra procedure parameter in which you specify the SQL/OData arguments.

          1 of 1 people found this helpful
          • 2. Re: OData Translator Paging Extension in Web Services Translator
            Steven Hawkins Master

            > I have been tasked with adding the OData Translator paging capability to the Web Services Translator.

             

            As Marc is showing the ws transaltor just exposes procedures, which can take any query string / payload.  If you are using OData in between Teiid and the data base, you should just use one of the OData translators. 

             

            Is the issue that the source supports a non-standard OData, or some other issue with the built-in OData translators?

            • 4. Re: OData Translator Paging Extension in Web Services Translator
              David Bailey Newbie

              Thank you, Marc.  I think that is the way we should go.  That method would be much simpler than writing a bunch of spaghetti code

               

              The invokeHttp method is used in the test driver in the following lines:

              • Call call = (Call)cb.getCommand("call invokeHttp('GET', null, null)");
              • Call call = (Call)cb.getCommand("call invokeHttp('GET', null, null, true)");
              • Call call = (Call)cb.getCommand("call invokeHttp('GET', null, null, true)");

               

              Therefore, I would have to use:

                   Call call = (Call)cb.getCommand("call invokeHttp(action => 'GET', endpoint => VARIABLES.queryString, stream => 'TRUE'));

               

              Pardon the dumb statement here, but I would guess I would put the statement:

                  DECLARE STRING VARIABLES.queryString = QUERYSTRING('?$format=json');

              above that statement so that the queryString is defined, and include the top and skip variables in queryString.

               

              Finally, I should write a new method that takes the top and skip (or whatever pagination variable names you want) and creates the SQL query string to be used in the invokeHttp method.

               

              I think I am understanding the flow of things.  Am I on track here?

              • 5. Re: OData Translator Paging Extension in Web Services Translator
                David Bailey Newbie

                Working with a couple of more knowledgeable colleagues, I came up with the function insertPaging(), which I put in WSExecutionFactory.java.

                 

                private Blob insertPaging(CommandBuilder cmd, String baseSQLCmd, int rows, int page) {

                   /* Error checking */

                    /* Negative page or rows values are invalid */

                     if (rows < 0){

                          System.out.println("Invalid rows value: " + rows.toString());

                          System.out.println("rows cannot be negative");

                          return;

                     }

                    if (page < 0) {

                          System.out.println("Invalid page value: " + rows.toString());

                          System.out.println("page cannot be negative");

                         return;

                     }

                   

                     /* Parse the SQL query */

                     String baseSQLQuery = (String)this.utility.parseCommand(query);

                 

                     /* Append the rows and page value for the corresponding OData Translator $top and $skip values, if present */

                     String topString = "";

                     String skipString = "";

                     if (rows > 0 and rows != null) {

                          topString = "&$top=" + String.valueOf(rows);

                          if (page > 0 and page != null) {

                               skipString = "&skip=" + String.valueOf(page);

                          }

                     }

                 

                     /* Determine queryString and command string for use in invokeHttp */

                     String queryString = baseSQLQuery + topString + skipString;

                     String cmdString = "call invokeHttp(action => 'GET', endpoint => '" + queryString + "', stream => 'TRUE'";

                 

                     /* Execute invokeHttp and return Blob */

                     Call call = (Call)cb.getCommand(cmdString);

                     /* The this pointer is used because we are in WSExecutionFactory */

                     BinaryWSProcedureExecution pe = new BinaryWSProcedureExecution(call, rm, Mockito.mock(ExecutionContext.class), this, mockConnection);

                     pe.execute();

                     List<?> result = pe.getOutputParameterValues();

                     Blob b = (Blob) result.get(0);

                     return b;

                }

                 

                 

                I would appreciate any feedback on this method, even if I am way off base.  Thanks for all of your help.

                • 6. Re: OData Translator Paging Extension in Web Services Translator
                  Ramesh Reddy Master

                  If you are going to just do that why not write a function like QueryString say for ex: PagingQueryString and add the top and skip to it and not have to extend the translator at all?

                   

                  The real intent of extending a translator is to automatically handle the user's SQL query conversion of the top and skip information into source specific (web service in this case) automatically. As soon as you introduce a special construct like "insertPaging" call, you are saying that your user knows how the paging works and they are converting the query for that themselves (yes you provided an additional utility method to mask real syntax).

                   

                  The second goal to extending the translator is, if the web service is going to return 1000's of rows, then how that query can be broken into multiple queries, such that translator can keep fetching rows next set of rows without user intervention. You can not do either with above.

                   

                  But if those are not what you are looking for then yes, above works fine. Just take my first line as a recommendation and you do not need to extend the translator.