2 Replies Latest reply: Mar 11, 2015 5:05 AM by Shane Porter RSS

    Engine API - GetTablesAndKeys column order

      Hi,

       

      The GetTableAndKeys method doesn't return the fields in column order.

       

      I want to use the data to output table headings (I can then use GetTableData to get the table data.)

       

      Is this a bug? What's an alternative method to retrieve source table columns in the correct order?

       

      thanks,

      Shane.

        • Re: Engine API - GetTablesAndKeys column order

          Some more info:

           

          The script that loads the data:

           

          Characters:

          Load Chr(RecNo()+Ord('A')-1) as Alpha, RecNo() as Num autogenerate 26;

           

          The result:

           

          NumAlpha
          A1
          B2
          C3
          D4
          E5
          F6
          G7
          H8
          I9
          J10
          K11
          L12
          M13
          N14
          O15
          P16
          Q17
          R18
          S19
          T20
          U21
          V22
          W23
          X24
          Y25
          Z26

           

          The JSON for the GetTableData:

           

          {"jsonrpc":"2.0","id":4,"result":{"qData":[{"qValue":[{"qText":"A","qNumber":0},{"qText":"1","qIsNumeric":true,"qNumber":1}]},{"qValue":[{"qText":"B","qNumber":0},{"qText":"2","qIsNumeric":true,"qNumber":2}]},{"qValue":[{"qText":"C","qNumber":0},{"qText":"3","qIsNumeric":true,"qNumber":3}]},{"qValue":[{"qText":"D","qNumber":0},{"qText":"4","qIsNumeric":true,"qNumber":4}]},{"qValue":[{"qText":"E","qNumber":0},{"qText":"5","qIsNumeric":true,"qNumber":5}]},{"qValue":[{"qText":"F","qNumber":0},{"qText":"6","qIsNumeric":true,"qNumber":6}]},{"qValue":[{"qText":"G","qNumber":0},{"qText":"7","qIsNumeric":true,"qNumber":7}]},{"qValue":[{"qText":"H","qNumber":0},{"qText":"8","qIsNumeric":true,"qNumber":8}]},{"qValue":[{"qText":"I","qNumber":0},{"qText":"9","qIsNumeric":true,"qNumber":9}]},{"qValue":[{"qText":"J","qNumber":0},{"qText":"10","qIsNumeric":true,"qNumber":10}]},{"qValue":[{"qText":"K","qNumber":0},{"qText":"11","qIsNumeric":true,"qNumber":11}]},{"qValue":[{"qText":"L","qNumber":0},{"qText":"12","qIsNumeric":true,"qNumber":12}]},{"qValue":[{"qText":"M","qNumber":0},{"qText":"13","qIsNumeric":true,"qNumber":13}]},{"qValue":[{"qText":"N","qNumber":0},{"qText":"14","qIsNumeric":true,"qNumber":14}]},{"qValue":[{"qText":"O","qNumber":0},{"qText":"15","qIsNumeric":true,"qNumber":15}]},{"qValue":[{"qText":"P","qNumber":0},{"qText":"16","qIsNumeric":true,"qNumber":16}]},{"qValue":[{"qText":"Q","qNumber":0},{"qText":"17","qIsNumeric":true,"qNumber":17}]},{"qValue":[{"qText":"R","qNumber":0},{"qText":"18","qIsNumeric":true,"qNumber":18}]},{"qValue":[{"qText":"S","qNumber":0},{"qText":"19","qIsNumeric":true,"qNumber":19}]},{"qValue":[{"qText":"T","qNumber":0},{"qText":"20","qIsNumeric":true,"qNumber":20}]},{"qValue":[{"qText":"U","qNumber":0},{"qText":"21","qIsNumeric":true,"qNumber":21}]},{"qValue":[{"qText":"V","qNumber":0},{"qText":"22","qIsNumeric":true,"qNumber":22}]},{"qValue":[{"qText":"W","qNumber":0},{"qText":"23","qIsNumeric":true,"qNumber":23}]},{"qValue":[{"qText":"X","qNumber":0},{"qText":"24","qIsNumeric":true,"qNumber":24}]},{"qValue":[{"qText":"Y","qNumber":0},{"qText":"25","qIsNumeric":true,"qNumber":25}]},{"qValue":[{"qText":"Z","qNumber":0},{"qText":"26","qIsNumeric":true,"qNumber":26}]}]}}

           

          The JSON for the GetTablesAndKeys:

           

          • "jsonrpc":"2.0",
          • "id":3,
          • "result":{
            • "qtr":[],
              1. {},
                • "qName":"Characters",
                • "qNoOfRows":26,
                • "qFields":[],
                  1. {},
                    • "qName":"Num",
                    • "qOriginalFields":[
                        ],
                      1. "qPresent":true,
                      2. "qInformationDensity":1,
                      3. "qnNonNulls":26,
                      4. "qnRows":26,
                      5. "qSubsetRatio":1,
                      6. "qnTotalDistinctValues":26,
                      7. "qnPresentDistinctValues":26,
                      8. "qKeyType":"PERFECT_KEY",
                      9. "qTags":[
                        1. "$key",
                        2. "$numeric",
                        3. "$integer"
                        ],
                      10. "qDerivedFields":[
                          ]
                      11. {
                        • "qName":"Alpha",
                        • "qOriginalFields":[
                            ],
                          1. "qPresent":true,
                          2. "qInformationDensity":1,
                          3. "qnNonNulls":26,
                          4. "qnRows":26,
                          5. "qSubsetRatio":1,
                          6. "qnTotalDistinctValues":26,
                          7. "qnPresentDistinctValues":26,
                          8. "qKeyType":"NOT_KEY",
                          9. "qTags":[
                            1. "$ascii",
                            2. "$text"
                            ],
                          10. "qDerivedFields":[
                              ]
                            }
                        • "qPos":{
                          • "qx":0,
                          • "qy":-9
                          }
                  • Re: Engine API - GetTablesAndKeys column order

                    So, the GetTableAndKeys method call doesn't return columns in the correct order, but...

                     

                    if I pass in a -1 for the offset in the GetTableData method call, I get the column names as the first row.  At least now I get a match between data and column names (even if the column names aren't strictly in the same order).