get data from database in array

[jackads]

Hello guys,

I'm trying to learn how to use database in livecode properly... When I do a select query like this:

put revDataFromQuery(,,gConnectionID,gQuery) into qResult

Ex. in a query like: "select id, name, email"
I receive the data like a String: "1 myName myEmail"

So, is hard to separate into differents variables, may would have a simple way to recover the query data into an array, so I could do something like this:

answer qResult[2]

and that would return to me the email (myEmail)

Any way to do that easily?

Thanks for the help! Cheers!

[Simon]

Hi Jack,
Try this"

Code: Select all

repeat for each word tWord in qResult
      add 1 to x
      put word x of qResult into gArray[x]
   end repeat

and

Code: Select all

on mouseUp
answer gArray[2]
end mouseUp

But I'm guessing you will have lots of entries so you might want multidimensional array based on ID?

Simon

[bangkok]

put revDataFromQuery(,,gConnectionID,gQuery) into qResult
So, is hard to separate into differents variables, may would have a simple way to recover the query data into an array, so I could do something like this:

By default revDataFromQuery use TAB to separate columns, and CR to separate row.

So in your case, you just have to :

Code: Select all

set itemdelimiter to tab
put item 1 of line 1 of qResult into theID
put item 2 of line 1 of qResult  into theEmail

etc.

But you can use other separators, like :

Code: Select all

put revDataFromQuery(TAB,"|",gConnectionID,gQuery) into qResult

In this case, each row will be separated by "|"
Here you can use :

Code: Select all

set linedelimiter to "|"
set itemdelimiter to tab
put item 1 of line 1 of qResult into theID
put item 2 of line 1 of qResult  into theEmail

This last trick can be valuable if one of your column contains text with CR for instance. So LiveCode won't cut the result, thinking it's another row.

After you can do :

Code: Select all

put item 2 of line 1 of qResult  into theLongText
replace "|" with CR in theLongText
put theLongText in fld "myText"

[Adrian]

Perhaps also you could use "split"? (See the dictionary for details).

[jackads]

Thanks for all answers

Split looks great, but may it can create only single dimensional array.

So, if I want a multiple dimensional array, something like

Code: Select all

answer myArray[1]["name"]

and it shows to me "James" for example
even something like myArray[1][1]

Why did you use "tWord" in the for each statment if you don't use tWord variable inside it?

Code: Select all

repeat for each word tWord in qResult
      add 1 to x
      put word x of qResult into gArray[x]
   end repeat

But I use this and works fine, but a multidimensional array would be great, I will need that in the next card implementation.

Thanks for the support!

[dave_probertGA6e24]

Hi,

Split, if you read the dictionary, can actually do the split in two dimensions - which is more than adequate for SQL results.

Code: Select all

split tData by cr and tab

I use this all the time. The only problem with it is that you need to know beforehand the returned field order. It can be easier to have a helper function to split the data into named fields in the array.

Hope that helps,
Dave