Page 1 of 1
Solved: Getting and Setting global Vars from a db table
Posted: Tue Nov 26, 2019 12:06 pm
by 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.
Re: Getting and Setting global Vars from a db table
Posted: Tue Nov 26, 2019 12:26 pm
by 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.
Re: Getting and Setting global Vars from a db table
Posted: Tue Nov 26, 2019 12:37 pm
by 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"
Re: Getting and Setting global Vars from a db table
Posted: Tue Nov 26, 2019 3:21 pm
by 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
Re: Getting and Setting global Vars from a db table
Posted: Tue Nov 26, 2019 4:11 pm
by 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.
Re: Getting and Setting global Vars from a db table
Posted: Tue Nov 26, 2019 6:20 pm
by 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