Layer7 API Management

 View Only
  • 1.  JDBC Result Set Processing

    Posted Oct 22, 2020 11:56 AM
    What is the best practice for processing a row of data returned by the Perform JDBC Query assertion? It's odd to me that the result sets are grouped by column instead of row as this makes the parsing of the data more difficult when more than one row is returned by the SQL query.  For example,

    jdbcQuery.mycolumn1= {Object[]} size = 3
    • [0] = {String} "zero"
    • [1] = {String} "one"
    • [2] = {String} "two"
    jdbcQuery.mycolumn2 = {Object[]} size = 3
    • [0] = {String} "four"
    • [1] = {String} "five"
    • [2] = {String} "six"
    What is the general method used to associate element [0] of mycolumn1 to element [0] of mycolumn2 when the results are saved to context variables?

    The other possibility is saving the result as XML and then writing a transform to process the data. If this is the best practice, does Broadcom have a sample XSLT for this?


  • 2.  RE: JDBC Result Set Processing
    Best Answer

    Posted Oct 23, 2020 01:21 PM
    Hi Jackie,
    The way I have done this in the past is by using a "Run Assertions for Each Item" assertion. It is a bit convoluted but its the best I have come up with. Would be happy to hear if anyone has a better approach. But the pattern I have used is something like this:
    If you think it would be helpful, I'd be happy to share the XML from this policy.


    ------------------------------
    Casey Gibson
    CRG Technologies
    casey@crgtech.io
    ------------------------------



  • 3.  RE: JDBC Result Set Processing

    Posted Oct 26, 2020 12:13 PM
    Thanks Casey.  That's what I was looking for. It still seems odd to me that the gateway provides the result set variables grouped by column instead of row. The XML output of the Perform JDBC Query assertion actually outputs the data in rows and columns. The issue is getting that XML data back into usable variables in the policy


  • 4.  RE: JDBC Result Set Processing

    Broadcom Employee
    Posted Oct 26, 2020 05:31 PM
    Edited by Jay MacDonald Oct 26, 2020 05:37 PM
      |   view attached
    You will always need to iterate through the result set UNLESS you use XSLT (which would do just that). There used to be a "bug" where if a field was null, it would not get included in the result set so you could get the columns out of sync and *had* to use the XML result. The best way to convert that would be to use XSLT, and I've often intended to try and write that XSLT but my chops there are quite limited, so I've always relied on policy to iterate through it. See the attached policy for what I mean. That being said, I think Casey's example is a better way to do this now.

    The database is a single table of food:

    mysql> select * from food;
    +----+-----------+-------+--------+
    | id | name      | price | source |
    +----+-----------+-------+--------+
    |  1 | apple     | 1.40  | NULL   |
    |  2 | carrot    | 0.80  | NULL   |
    |  3 | peach     | 1.2   | NULL   |
    |  4 | celery    | 0.75  | NULL   |
    |  5 | banana    | 1.7   | Mexico |
    |  6 | pineapple | 1.00  | Mexico |
    +----+-----------+-------+--------+
    6 rows in set (0.00 sec)
    mysql>​


    The XML result set looks like:

    <jdbcQueryResult xmlns:L7j="http://ns.l7tech.com/2012/08/jdbc-query-result">
    <row>
    <col name="price" type="java.lang.String">1.40</col>
    <col name="name" type="java.lang.String">apple</col>
    <col name="id" type="java.lang.Integer">1</col>
    <col name="source"><![CDATA[NULL]]></col>
    </row>
    <row>
    <col name="price" type="java.lang.String">0.80</col>
    <col name="name" type="java.lang.String">carrot</col>
    <col name="id" type="java.lang.Integer">2</col>
    <col name="source"><![CDATA[NULL]]></col>
    </row>
    <row>
    <col name="price" type="java.lang.String">1.25</col>
    <col name="name" type="java.lang.String">peach</col>
    <col name="id" type="java.lang.Integer">3</col>
    <col name="source"><![CDATA[NULL]]></col>
    </row>
    <row>
    <col name="price" type="java.lang.String">0.75</col>
    <col name="name" type="java.lang.String">celery</col>
    <col name="id" type="java.lang.Integer">4</col>
    <col name="source"><![CDATA[NULL]]></col>
    </row>
    <row>
    <col name="price" type="java.lang.String">1.75</col>
    <col name="name" type="java.lang.String">banana</col>
    <col name="id" type="java.lang.Integer">5</col>
    <col name="source" type="java.lang.String">Mexico</col>
    </row>
    <row>
    <col name="price" type="java.lang.String">1.00</col>
    <col name="name" type="java.lang.String">pineapple</col>
    <col name="id" type="java.lang.Integer">6</col>
    <col name="source" type="java.lang.String">Mexico</col>
    </row>
    </jdbcQueryResult>
    The resulting JSON looks like:
    [
    {"price":"1.40","name":"apple","id":"1","source":""},
    {"price":"0.80","name":"carrot","id":"2","source":""},
    {"price":"1.25","name":"peach","id":"3","source":""},
    {"price":"0.75","name":"celery","id":"4","source":""},
    {"price":"1.75","name":"banana","id":"5","source":"Mexico"},
    {"price":"1.00","name":"pineapple","id":"6","source":"Mexico"}
    ]
    ------------------------------
    Jay MacDonald - Adoption Architect - Broadcom API Management (Layer 7)
    ------------------------------

    Attachment(s)

    xml
    JDBC XML to JSON.xml   17 KB 1 version