Are dynamic variable names possible?

[RAK]

Is there any way to programmatically create a variable name from external data and assign a value to it?

In other words - I have source data in an external file that may change per user - I need to assign values to each item in the external list. I can get the job done using a reference array but it complicates the additional coding the solution requires.

If I could use a string, acquired by the code from an external file as a variable name and assign a value to it, it would simplify this large project dramatically.

Thanks!

[Deana]

Dynamic variables can be created using a variable substitution trick, that looks something like this:

Code (winbatch) Select Expand

datacount = 10
For x = 1 to datacount
   var%x% = value
Next


[JTaylor]

In the event it is helpful, here is what I do for one of my applications which contains a large number of settings.  I store mine in a table.   I can assign a default value and the Parity function applies it to the user when they load the settings.  This approach allows me to handle any number of variables for any number of users and a small bit of code to manage them.

Jim

CREATE TABLE `settings` (
  `rec_num` int(11) NOT NULL AUTO_INCREMENT,
  `use_login` char(25) NOT NULL,
  `set_module` varchar(15) NOT NULL,
  `vname` varchar(30) NOT NULL,
  `vtype` varchar(15) DEFAULT NULL,
  `vvalue` varchar(2000) DEFAULT NULL,
  PRIMARY KEY (`use_login`,`set_module`,`vname`),
  UNIQUE KEY `rec_num_UNIQUE` (`rec_num`)
)

Code (winbatch) Select Expand



#DefineSubRoutine Parity_Settings(use_loginp, set_module)

  SQLText = "Select * from settings where settings.use_login = 'default' and set_module = '%set_module%'"
  myREC2.Open (SQLText, myConn, adOpenStatic, adLockReadOnly, adCmdText)
  While myREC2.EOF == @FALSE
    vname =  myREC2.fields("vname").value
    vtype =  myREC2.fields("vtype").value
    vvalue = StrReplace(StrReplace(myREC2.fields("vvalue").value,"\","\\"),"'","''")
    SQLText = "Insert Ignore into settings (vvalue, use_login, set_module, vname, vtype) values ('":vvalue:"', '%use_loginp%', '%set_module%', '%vname%','%vtype%');"   
    myConn.Execute(SQLText)
    myREC2.MoveNext
  EndWhile
  myREC2.Close
 
#EndSubRoutine

#DefineSubRoutine Load_Settings(use_loginp, set_module, vname)
 
  vname_save = vname

  and_vname = ""
  If vname != "" Then and_vname = " and vname = '%vname%' "

  SQLText = "Select * from settings where settings.use_login = '%use_loginp%' and set_module = '%set_module%' ":and_vname
  myREC2.Open (SQLText, myConn, adOpenStatic, adLockReadOnly, adCmdText)
  While myREC2.EOF == @FALSE
    vname =  myREC2.fields("vname").value
    vvalue=  myREC2.fields("vvalue").value
    vtype =  myREC2.fields("vtype").value
    %vname% = vvalue
    myREC2.MoveNext
  EndWhile
  myREC2.Close

  If vname_save != "" Then Return vvalue

#EndSubRoutine


#DefineSubRoutine Save_Settings(use_loginp, set_module)

  SQLText = "Select * from settings where settings.use_login = '%use_loginp%' and set_module = '%set_module%'"
  myREC2.Open (SQLText, myConn, adOpenStatic, adLockReadOnly, adCmdText)
  While myREC2.EOF == @FALSE
    vname =  myREC2.fields("vname").value
    vtype =  myREC2.fields("vtype").value
    vvalue = StrReplace(StrReplace(%vname%,"\","\\"),"'","''")
    SQLText = "Insert into settings (vvalue, use_login, set_module, vname, vtype) values ('":vvalue:"', '%use_loginp%', '%set_module%', '%vname%','%vtype%') ON DUPLICATE KEY UPDATE vvalue = '%vvalue%';"   
    myConn.Execute(SQLText)
    myREC2.MoveNext
  EndWhile
  myREC2.Close
 

#EndSubRoutine





[JTaylor]

Guess I should clarify that the "vname" parameter is usually blank but can be used when I want to retrieve the value of a single setting rather than loading them all.

          Load_Settings(use_loginp, set_module, vname)


Jim

[snowsnowsnow]

The bottom line to this question is: Yes, you can do it (using % substitution), but most people will advise you against it because there really are better (read: more complicated) methods for most use cases.

Jim's idea of bringing in a SQL database is certainly a good example of an approach that is way complicated, but way powerful and flexible at the same time.

[JTaylor]

In the event I need to clarify...I wouldn't generally advocate the use of a database unless one's application was already using a database as that adds unnecessary pieces to the puzzle but a simple For loop using the contents of an INI or delimited text file would work in the same fashion.

Not sure I consider either approach "way complicated".  I know from experience that it is far simpler than managing a few hundred variables one by one.  I am curious about the other, better approaches mentioned as they might be useful.

Jim

[RAK]

thanks Deana, awesome...

I can now use the variables to extract data no matter what order the columns appear in the sheet! SO cool....

the posHeads variable below contains all the column headings from the excel worksheet.

#DefineFunction clean_textNum (ctxt)
   return (StrClean(ctxt, 'abcdefghijklmnopqrstuvwxyz1234567890.-_ ', "", @FALSE, 2))
#EndFunction


msgNames = ''
For x = 1 to itemcount( posHeads, tab)
   v = clean_textNum (strreplace (itemextract( x, posHeads, tab ),' ','_'))   ;next item - removes characters not allowed in variables
   if itemcount( v, '_') < 2 then v = v:'_'                           ; replace spaces with "_" or add one at end if no spaces (so variables names != headings)
   %v% = x
   msgNames = msgNames:cr:'message ("':v:'", ':v:')'  ; I used this as a test - creates messages you can paste into code to check each var/value
Next
clipput (msgNames)


on the other subject - I use ini files for settings when I can - much simpler. The only reason I use database settings is when they are user specific and are set by ADMIN level users @ a central location. Any machine/non-admin settings stay local in ini files. I have so many settings that I concatenate them so I can put groups into a single field  - then parse after download. I use PHP to talk to SQL from WB, it's powerful, adds functionality, speed, and I can transfer heavy processing duties to the server when desired. I also avoid any additional software requirements for OBDC etc...