3 Replies Latest reply on Feb 15, 2013 12:05 PM by ed_f

    What is the order of Column Names in Sqlite query results?

    decostj

      I am writing an application using Adobe Air, Sqlite, and Javascript.

       

      After writing the following select statement:

                SELECT field1, field 2, field 3, field 4 FROM TableA;

      I would like to get the columnName/data combination from each row -- which I do successfully with a loop:

                var columnName="";

                for (columnName in selResults.data[i]) {

                     output+=columnName + ":" + selResultsdata[i][columnName] + ";";

                     }

       

      My issue is that the column names come out in a different order every time I run the query and never once have they come out in the desired order -- field 1, field 2, field 3, field 4.  If I run the query in Firefox's Sqlite Manager, the columns come out in the "proper" order. When I run them in Adobe Air, the order will be the same if I run the query mulitple times without closing the app.  If I make a change such as declaring the columnName variable with "" before the for column, or declare it as (var = columnName in selResults.data) , then the order changes.  If I shut down my app and re-open after lunch and run query, it comes out in another order.  At this time, I'm not interested in the order of the rows, just the order of the columns in each output row.  I've even tried assiging an index to columnName which seems to just pick up a single letter of the columnName.

       

      I'm in the process of changing my HTML presentation of the data to assign a precise columnName to an HTML table title, but I'm reluctant to let go of the above concept as I think my separation of HTML/presentation and Javascript would be better if I could use the solution described above.

       

      So, does anybody know how to force the order of the columnNames in my output -- or what I'm doing to cause it to come out in a different order?

       

      Jeane

        • 1. Re: What is the order of Column Names in Sqlite query results?
          adobe_paul Adobe Employee

          Technically there isn't any "order" for the return columns. They aren't returned as an Array -- they're just properties on an Object instance (a "generic object"). The random order you're seeing is the behavior of the for..in loop iterating over the properties of the object. Unfortunately, with a for..in loop there is no guaranteed order for iterating over properties (and, as you've seen, it tends to vary wildly).

           

          The only solution is to create your own list of the column names and sort it the way you want to, then use that to create your output. For example, use the for..in loop to loop over the properties, but rather than actually get the values, just dump the column names into an Array:

           

          var columnName="";

          var columns = [];

          for (columnName in selResults.data[i]) {

              columns.push(columnName);

          }

           

          columns = columns.sort(); // just uses the default alphabetical sort -- you would customize this if desired

           

          var j = 0;

          for (j = 0; j < columns.length; j++) {

              columnName = columns[j];

              output+=columnName + ":" + selResultsdata[i][columnName] + ";";

          }

          1 person found this helpful
          • 2. Re: What is the order of Column Names in Sqlite query results?
            decostj Level 1

            Thank you.  That explanation was just what I needed.  I'll go back and try re-writing my code with this technique to be more re-useful.

            • 3. Re: What is the order of Column Names in Sqlite query results?
              ed_f

              This is an old thread but it asked my exact question so I will give it a try.

               

              I don't understand how you can say there is no order to columns. If you create a table with columns fname, lname, phone for example. and just do select * - as the original post stated in every sqlite manager I have see you always get them back in just that order.

               

              But with AIR it seems some arbitrary order comes back, why would that even happen? If as in my case I don't know what the data is specifically. If it returned it in the order of the table and I put it in a data grid all would be fine. The column headings are correct and everything would be what I need.

              But instead with air (given the earlier example) end up with lname,phone,fname or something else.

               

              Having to know exactly what is being sent so that it can be ordered programmatically just doesn't seem right to me.