Solved: Getting and Setting global Vars from a db table

[j9v6]

Hi there,

I'm building an app that saves the users default settings / preferences in an SQL table. The table contain things such as preferred date format , region etc. The table looks something like this below

Row | varName | varValue
1 | gLocale | E
2 | gRegion | UK

I'm using a simple repeat loop to grab the data from the table, and then I want to dynamically set the variables that are declared within it.

Code: Select all

put "SELECT * FROM appSettings" into tSQL
put revDataFromQuery("§",,gdbid, tSQL) into tData
repeat with r = 1 to the number of lines in tData
	-- Pseudo code below
	-- put item 2 of line r of tData into the variable named in item 1 of line r of tData
	-- eg put 'E' into gLocale
	-- eg put 'UK' into gRegion
end repeat

So far I have not found a way to refer to the value of a variable as a it's own declared variable - so although I can see that the value of varName in row 1 is gLocale I can't get LiveCode to set gLocale to anything dynamically. The only (and distinctly clunky) way I've got around this so far is to add If statements in the repeat loop but this would restrict the creation of variables and I'd have to add more "if"'s every time I add a new row to the table as per below.

eg

Code: Select all

If item 1 of line r of tData = "gLocale" then
	put item 2 of line r of tData into gLocale
end if
if item 1 of line r of tData = "gRegion" then
	put item 2 of line r of tData into gRegion
end if
etc

Anyone know if it's possible to extract the value of a variable and use it as the name for another variable and set it?

Thanks,
Al.

[bangkok]

You make it much more complex that it is and should be.

2 cases.

CASE 1
You want to do a :

Code: Select all

put "SELECT * FROM appSettings" into tSQL

And imagine that the table appSettings contains 3 fields : f1, f2 and f3 (they were created in the table, in that order).

By doing :

Code: Select all

put revDataFromQuery(,,gdbid, tSQL) into tData

... you will receive each record on a different line, and each field separated by a tabulation

(that's the default for revDataFromQuery)

You will get :

Code: Select all

set itemdelimiter to tab
repeat with i = 1 to the number of lines of tData
put item 1 of line i of tData into tF1
put item 2 of line i of tData into tF2
put item 3 of line i of tData into tF3
answer "For record : "&i& " the value of f1="&tF1
end repeat

CASE 2

You do a select by listing the fields you want in the order that you want :

Code: Select all

put "SELECT f3,f2,f1 FROM appSettings" into tSQL
put revDataFromQuery(,,gdbid, tSQL) into tData
set itemdelimiter to tab
repeat with i = 1 to the number of lines of tData
put item 1 of line i of tData into tF3
put item 2 of line i of tData into tF2
put item 3 of line i of tData into tF1
answer "For record : "&i& " the value of f1="&tF1
end repeat

You have full control on the way records are "sucked" from the DB, and on the way the fields (or columns) are separated.

[j9v6]

Thanks for the message. However I've just solved my own problem (funny how that happens when you write out the problem) .

Here's my code solution:

Code: Select all

   put "SELECT * FROM settings" into tSQL
   put revDataFromQuery("§",,gdbid, tSQL) into tData
   set the itemdelimiter to "§"
   repeat with r = 1 to the number of lines in tData
      do ("put" && quote & item 2 of line r of tData && quote & "into" && item 1 of line r of tData)
   end repeat

For each row in the appSettings table, this will set the variable (varName) to the value (varValue)

eg.
gLocale is set to "E"
gRegion is set to "UK"

[dunbarx]

I think the key here, regardless of which way you go, is to set the itemDelimiter, which was not done at all in the original posted handler.

Craig

[j9v6]

Actually, the itemdelimiter was not the issue here as if you don't set it LiveCode defaults to using a comma. The issue was being able to extract the list of variables to be declared and the values to be assigned to them from a database table and once you have them assign the retrieved value to the retrieved variable name. As with most things in LiveCode, once I found it, the solution was quite simple and elegant.

[dunbarx]

Ah,

It was only that the delimiter changed to other chars in subsequent posts that made me think as I did. I do not work with DB's so I did not understand that the delimiter was already in hand.

Craig