What is "Special" about "Column" ??

[aetaylorBUSBnWt]

Hi,

Specifically:

combine tArray by column

I am pulling data from a sqlite database and displaying it in a table.

If I just get all records in a variable and put it into the Table field - fine.
(no don't bug me about Data Grids just yet)

So next I wanted to get the table sorted. but that can mean using record sets.
Ok fine.

My SQLite statement:
"select * from datatable order by dateField"
But record sets require you to get each column from the current selected record, field by field.
OK, did that, now have an array, indexed by a number, 1 through n, as I get the fields in order.

But I need a text variable to put into a table.
BUT, I don't want that bull going on where magic sorting of an array happens.
I put them in numerically and all I want out is the actual values, in the order that they were put in.

So I combine the array and get a single variable out.

combine tArray by column

So far, good.

NOW is when trouble happens.

I want ONLY a FEW of the fields from the record.
That means a different select statement

"select field1,fid2,fld3,fl9,fld12 from datatable order by dateField"

At this point the ONLY thing that does not work is:

combine tArray by column
!!!

It takes in the array with the data in it and returns empty.
That array is single dimensional - only has numerically indexed values from a single database record.
If the SQLite Select query is for ALL fields in the record, then "combine" works.
If the SQLite Select query is for some fields in the record, then "combine" returns empty.
NOTE: prior to the combine call, everything is perfect.

So somewhere in the SQLite magic, somebody is screwing with some property or whatever and changes how COMBINE uses "COLUMN"!

Where, how and WHY!!
Most importantly how do I get whatever changed put back so combine works the way it does when all the fields are present.

Thanks for your help

Given the troubles with combine acting weird: the following Never has problems with hidden stuff or array magic.

Code: Select all

function recordFromArray @pArray
    local tIndex, tRecord, tCount;
    put empty into tRecord;
    put the number of lines of the keys of pArray into tCount;
    if tCount > 0 then
        put pArray[1] into tRecord;
        repeat with tIndex = 2 to tCount
            put tab & pArray[tIndex] after tRecord;
        end repeat
    end if
    return tRecord
end recordFromArray

[stam]

TL;DR

but scanning your post i think i got the gist of it; i can't see why, if you have a full array extracted from a db, you'd need a new SQL query to show only *some* fields from the array data.

Why not just created handler that extracts this from the array for you?

In my experience, the less one messes with SQL the higher level of happiness one achieves. It's not complicated to return a text object with only certain keys from an array, filter it for certain values in keys and order it any way you want. Likely a lot quicker than an SQL call as well...

S.

[bn]

Could you post some sample data of what is not working?

The one example I do not understand. What is in the array when you try to combine by column and what do you expect?

Kind regards
Bernd

[bobcole]

aetaylorBUSBnWt:
My first thought was to determine if the results (tResults) of your query are in an array:

Code: Select all

   if tResults is an array then
      put "tResults is an array" & return after message box
   else
      put "tResults is NOT an array." & return after message box
   end if

After digging around the Dictionary, I think it is important to know what LC command you are using to query the database:

• revQueryDatabase() or
• revExecuteSQL or
• revDataFromQuery()
  ...

My favorite is revDataFromQuery() because it does not return an array.
Rather, simply, that command returns a list of delimited records that contain delimited columns.

Bob