Retrieve a variable result using a string

[JoaoAfonso]

Good evening.

I have again a question for sure very easy to answer, but my limited knowledge does not help me.

With an example will be easier to explain my question (eventually will show a bad program construction, but as this is the second or third time I think about it, I guess I should clarify it):

Code Select Expand

TYPE individual
STRING first_name
STRING last_name
STRING country
STRING english_percentage
STRING french_percentage
STRING german_percentage
ENDTYPE

INDIVIDUAL person

(...)

hstmt_select_2 = dbExecSQL(pdb,"SELECT * FROM task_pre_requisites")
IF hstmt_select_2
check=NULL
dbBindVariable(hstmt_select_2,1,task)
FOR a=0 TO 3
dbBindVariable(hstmt_select_2,2+(a*2),language[a])
dbBindVariable(hstmt_select_2,3+(a*2),minimum_language_percentage[a])
NEXT a
WHILE dbGetNext(hstmt_select_2) AND (check=NULL)
IF person.language[a]_percentage>minimum_language_percentage[a] THEN check=1
ENDWHILE
dbFreeSQL(hStmt_select_2):hStmt_select_2 = NULL
ENDIF

What I am trying to do is the following:
- set a string into variable language[a]
- use that string as a variable name (person.language[a]_percentage; if language[a] is english, then this would also mean person.english_percentage)

Thanks in advance

[LarryMc]

Code Select Expand

person.language[a]_percentage
doesn't look like a legal variable name.

Larry

[Allan]

Hi

2+(a*2)

Bind Variables are done by sequence 1, 2, 3, 4, 5, and so on

"SELECT * FROM task_pre_requisites"  will return ALL the FIELDS in the TABLE.

First VBind starts at 1 and will bind to the first FIELD.
dbBindVariable(hstmt_select_2,1,task)

The next bind is to the second FIELD in the TABLE and it requires the number  2
dbBindVariable(hstmt_select_2,2,xxx)

Numbers must be sequential.

Allan

[LarryMc]

Numbers must be sequential.

Allan, are you sure of that.
You can return all the dbase fields with the * but that doesn't mean you have to bind each one.
I thought the numbers in the bindings were strictly to identify which location the returned field is in the select statement.
I know you don't have to select fields in the order they appear in the database.
And you don't have to bind in the order the fields appear in the select statement. (I've got working examples of that)

Therefore there is no absolute direct correlation between the binding and the location of the field in the TABLE.
The only time it even appears that way is when you return all the fields.
If you change your statement to this I will agree 100%:

First VBind starts at 1 and will bind to the first FIELD in the select statement.
dbBindVariable(hstmt_select_2,1,task)

The next bind is to the second FIELD in the TABLE select statement and it requires the number  2
dbBindVariable(hstmt_select_2,2,xxx)

Numbers must be sequentialBindings to select statement fields can be in any order.

Larry

[Allan]

Hi Larry

My wording is out for sure. His numbers are sequential!

What I mean is the numbers used DO bind to their respective Fields returned. The example appears to have 9 fields in the TABLE.

So if the SELECT statement only asked for say 'first_name', 'country' to be returned then would they not bind as 1 and 2 even though they are 1 and 3 in the actual TABLE?

Allan

[LarryMc]

So if the SELECT statement only asked for say 'first_name', 'country' to be returned then would they not bind as 1 and 2 even though they are 1 and 3 in the actual TABLE?

yes you are correct

also, if they are SELECTED as  'first_name', 'country'
you can bind them as

Code Select Expand

dbBindVariable(hstmt_select_2,2,mycountry)
dbBindVariable(hstmt_select_2,1,myfname)
in that order

Larry

[JoaoAfonso]

What I mean is the numbers used DO bind to their respective Fields returned. The example appears to have 9 fields in the TABLE.

Allan

Table has 9 columns, yes. I use this way to bind all data in order to write less code.

Code Select Expand

person.language[a]_percentage
doesn't look like a legal variable name.

Larry

That is the problem I know I cannot do this this way, but how to turn around that problem using the type and the db structure I told in first post?


I already figure out a solution, but that made me change the type structure. In spite of having

Code Select Expand

TYPE individual
STRING first_name
STRING last_name
STRING country
STRING english_percentage
STRING french_percentage
STRING german_percentage
ENDTYPE

I've changed to

Code Select Expand

TYPE individual
STRING first_name
STRING last_name
STRING country
STRING language[3]
STRING percentage[3]
ENDTYPE

In spite of using this invalid code

Code Select Expand

IF person.language[a]_percentage>minimum_language_percentage[a] THEN check=1


I now first compare the language[a] variable got from table with all person.language[a] variables. If they are equal, then I can compare person.percentage with minimum_language_percentage. Anyway, I still ask if someone thinks about other way to do it.

[pistol350]

Hi Joao!

This solution is not directly linked to your problem, but in case you did not know of this sample code,
I think it may be useful to you.

Forgot to say that this sample code is compatible with Ebasic as it was converted from ibasic std using Standard to Pro utility.
Hope it helps.

Code Select Expand


'Code converted with S2P v1.03 on the 06-APR-2007 at 12:55:11

$MAIN
AutoDefine "Off"
Def Version$:String:Version$="0.1"
'save and load username and password with encryption 
'ibasic std code 
'by jos de jong, Jan 2007 
'based on code by Junner2003 
'the RC4 encryption method was written by Sm0oth 
'declare subroutines
Declare loadUserData(myFileName:String, key:String)
Declare saveUserData(myFileName:String, key:String)
Declare RC4(inp:String, key:String),String
'declare global variables for the username, password and language 
Def username:String 
Def password:String 
Def language:String 
Def dataFileName:String 
username = "" 
password = "" 
language = "" 
'choose a filename to store the data 
dataFileName = Getstartpath + "userdata.txt" 
'create a dialog with controls 
Const ctlSave = 1 
Const staUsername = 10 
Const ctlUsername = 11 
Const staPassword = 12 
Const ctlPassword = 13 
Const staLanguage = 14 
Const ctlLanguage = 15 
Const MB_ICONEXCLAMATION = 48 
Const MB_ICONINFORMATION = 64 
Const MB_ICONQUESTION = 32 
Const MB_ICONSTOP = 16 
Def main:Dialog 
CreateDialog main, 100,100,320,120, @size | @minbox | @sysmenu | @caption, 0, "Configure User Data.Version "+version$,&HandlerMain 
Control main, @Static," Username:", 20, 20, 80,20, 0,staUsername
Control main, @Edit,"", 100, 20, 120,20, @TABSTOP | @CTEDITAUTOH,ctlUsername
Control main, @Static," Password:", 20, 50, 120,20, 0,staPassword
Control main, @Edit,"", 100, 50, 120,20, @TABSTOP | @CTEDITAUTOH | @CTEDITPASS,ctlPassword
Control main, @Static," Language:", 20, 80, 180,20, 0,staLanguage
Control main, @ComboBox,"", 100, 80, 120,80, @TABSTOP | @CTEDITAUTOH | @CTCOMBODROPLIST,ctlLanguage
Control main, @Button," Save", 230, 20, 80,24, @TABSTOP,ctlSave
'define an encryption key used for saving the data encrypted 
Def encrKey:String 
encrKey = "secretKey#1" 
'show the dialog on screen 
'the returned value of ret can be @IDOK or @IDCANCEL 
Def ret:Int 
ret = Domodal main 
If ret = @idok 
    'user has pressed "Save" in the dialog 
    saveUserData(dataFileName, encrKey) 
Else 
    Messagebox 0, "Saving username and password is canceled", "Canceled" 
Endif 
'end the program 
End 
'_______________________________________________ 
Sub HandlerMain 
    'handler for the dialog Main 
    Select @class 
        Case @idinitdialog 
            'initialize the content for the controls 
            Addstring main, ctlLanguage, "English" 
            Addstring main, ctlLanguage, "French" 
            Addstring main, ctlLanguage, "German" 
            Addstring main, ctlLanguage, "Dutch" 
            'load a saved username and password (if available) 
            loadUserData(dataFileName, encrKey) 
            Centerwindow main 
        Case @idclosewindow 
            'close the dialog 
            Closewindow main 
        Case @idcontrol 
            Select @controlid 
                Case ctlSave 
                    If @notifycode = 0 
                        'close the dialog if the user presed the button "Save" 
                        'save the content of the edit boxes in variables 
                        username = Getcontroltext(main, ctlUsername) 
                        password = Getcontroltext(main, ctlPassword) 
                        language = Getcontroltext(main, ctllanguage) 
                        'close the dialog 
                        Closedialog main,@idok 
                    Endif 
            Endselect 
    Endselect 
Return 
EndSub
'_______________________________________________ 
Sub saveUserData(myFileName:String, key:String) 
    'save the username and password 
'save it encrypted with the given key 
    Def myFile As File 
Def encrUsername:String     
Def encrPassword:String     
Def encrLanguage:String     
    'write a textfile with name and password 
    If(Openfile(myFile,myFileName,"w") = 0) 
'encrypt the username and password 
encrUsername = RC4(Username, key) 
encrPassword = RC4(Password, key) 
encrLanguage = RC4(Language, key) 
'write the encrypted strings to the file 
        Write myFile, encrUsername 
        Write myFile, encrPassword 
        Write myFile, encrLanguage 
        Closefile myFile 
        Messagebox 0, "Username and password are saved", "Success", MB_ICONINFORMATION 
    Else 
        Messagebox 0,"unable to read file","message...", MB_ICONSTOP 
    Endif 
Return 
EndSub
'_______________________________________________ 
Sub loadUserData(myFileName:String, key:String) 
    'load the username and password 
'load and decrypt it with the given key 
    Def myFile:File 
    Def n:Int 
Def decrUsername:String     
Def decrPassword:String     
Def decrLanguage:String     
    If(Openfile(myFile,myFilename,"r") = 0) 
        'the file exists 
'read the (encrypted) text from the file 
        Read myFile, decrUsername 
        Read myFile, decrPassword 
        Read myFile, decrLanguage 
'decrypt the strings 
Username = RC4(decrUsername, key) 
Password = RC4(decrPassword, key) 
Language = RC4(decrLanguage, key) 
        'fill in the read name and password in the controls 
        Setcontroltext main, ctlUsername, username 
        Setcontroltext main, ctlPassword, Password 
        For n=0 To Getstringcount(main, ctlLanguage) - 1 
            If Getstring(main, ctlLanguage, n) = Language 
                Setselected main, ctlLanguage, n 
            Endif 
        Next n 
        Closefile myFile 
    Endif 
Return 
EndSub
'_______________________________________________ 
Sub RC4(inp:String, key:String),String 
'RC4 encryption/decryption 
'inp is the input string which must be encrypted/decrypted 
'key is the key used for a unique encryption of the input string 
'the function returns the encrypted string 
'when a string is encrypted you can decrypt it by executing this 
'sub again with the same key as used for the encryption 
Dim S[256],K[256],temp,y As Int 
Dim i,j,t,x As Int 
Dim Outp As String 
Outp="" 
For i=0 to 255 
S[i]=i 
Next i 
j=1 
For i=0 to 255 
If j>Len(key) Then j=1 
K[i]=Asc(Mid$(key,j,1)) 
j=j+1 
Next i 
j=0 
For i=0 to 255 
j=(j+S[i]+K[i])%256 
temp=S[i] 
S[i]=S[j] 
S[j]=temp 
Next i 
i=0 
j=0 
For x=1 to Len(inp) 
i=(i+1)%256 
j=(j+S[i])%256 
temp=S[i] 
S[i]=S[j] 
S[j]=temp 
t=(S[i]+(S[j]%256))%256 
Y=S[t] 
Outp = Outp + Chr$(Asc(Mid$(inp,x,1))||Y) 
Next x 
Return Outp 
EndSub

' 12 lines converted from 213 lines Read by S2P v1.03


[Allan]

Hi

Trying to figure out your code. Just a few questions please?

In the bind area:

Code Select Expand

dbBindVariable(hstmt_select_2,2+(a*2),[b]language[a][/b])
dbBindVariable(hstmt_select_2,3+(a*2),minimum_language_percentage[a])


Should it be

Code Select Expand

dbBindVariable(hstmt_select_2,2+(a*2),[b]person.language[a][/b])

or do you have an actual array variable language[3] that you are binding too???

minimum_language_percentage[a]

Is that a variable array or is it supposed to represent something in the TYPE?


In the WHILE loop:

WHILE dbGetNext(hstmt_select_2) AND (check=NULL)
      IF person.language[a]_percentage>minimum_language_percentage[a] THEN check=1
   ENDWHILE

Wont the index  'a' always be = to 3 after it has exited the FOR loop??  Do you wish to access the index 3 only?

The following code is how I would look at, but no doubt you may have more to do than just check for Language and percentage.

Code Select Expand

TYPE individual
STRING first_name
STRING last_name
STRING country
STRING language
FLOAT percentage
ENDTYPE
DEF EnglishPC, FrenchPC, GermanPC:float

INDIVIDUAL person

(...)

hstmt_select_2 = dbExecSQL(pdb,"SELECT * FROM task_pre_requisites")
IF hstmt_select_2
check=NULL
dbBindVariable(hstmt_select_2,1,task)
dbBindVariable(hstmt_select_2,2,person.language)
dbBindVariable(hstmt_select_2,3,person.percentage)

WHILE dbGetNext(hstmt_select_2) AND (check=NULL)
SELECT person.language
CASE "English"
IF person.percentage > EnglishPC THEN check=1
CASE "French"
IF person.percentage > FrenchPC THEN check=1
CASE "German"
IF person.percentage > GermanPC THEN check=1
ENDSELECT
ENDWHILE

dbFreeSQL(hStmt_select_2):hStmt_select_2 = NULL
ENDIF


Allan

[JoaoAfonso]

The main purpose of write code this way is that I can use this piece of code in other programs without change much, any future modification can be easier to make and without writing much code.



The specific purpose of this code is, with a table of tasks with language requirements and a list of personal with their knowledge of languages, check which persons are able to fullfill the tasks.
Eventually I could even get the results using just SQL syntax, but if later I want to increase one more language or use this code in another program which do not work with languages but another requirements, I will be forced to change much code.


The table "task_pre_requisites" is composed of the following columns:

task, lang_1, min_lang_perc_1, lang_2, min_lang_perc_2, lang_3, min_lang_perc_3, lang_4, min_lang_perc_4

This way I catalog each task, and the next 8 columns are the minimum knowledge capabilities someone must have to fullfill the given task. Some tasks just need one language knowledge (if a task is "teach english level 1", I would fill "lang_1"="english" and "min_lang_perc_1"="70") or knowledge of more languages (if a task is "text translation, french-english", I could fill "lang_1"="english", "min_lang_perc_1"="50", "lang_2"="french", "min_lang_perc_1"="50").

The problem arised because of my type definition: in spite of

Code Select Expand

TYPE individual
STRING first_name
STRING last_name
STRING country
STRING english_percentage
STRING french_percentage
STRING german_percentage
ENDTYPE

I should have used

Code Select Expand

TYPE individual
STRING first_name
STRING last_name
STRING country
STRING lang[3]
STRING lang_percentage[3]
ENDTYPE

This way would be much easier to compare strings: I could check all task lines, and in each dbGetNext, I could compare with all persons.

This can be a solution:

Code Select Expand

TYPE individual
STRING first_name
STRING last_name
STRING country
STRING lang[3]
STRING lang_percentage[3]
ENDTYPE

INDIVIDUAL person

(...)

TABLE "task_pre_requisites" with the following columns:

task, lang_1, min_lang_perc_1, lang_2, min_lang_perc_2, lang_3, min_lang_perc_3, lang_4, min_lang_perc_4

(...)

hstmt_select_2 = dbExecSQL(pdb,"SELECT * FROM task_pre_requisites")
IF hstmt_select_2
check=NULL
dbBindVariable(hstmt_select_2,1,task)
FOR a=0 TO 3
dbBindVariable(hstmt_select_2,2+(a*2),language[a])
dbBindVariable(hstmt_select_2,3+(a*2),minimum_language_percentage[a])
NEXT a
WHILE dbGetNext(hstmt_select_2) AND (check=NULL)
FOR b=0 TO 2
IF person.lang[b]=language[a]
IF person.lang_percentage[b]>minimum_language_percentage[a] THEN check=1
ENDIF
NEXT b
ENDWHILE
dbFreeSQL(hStmt_select_2):hStmt_select_2 = NULL
ENDIF

There is just one thing more to fix: the way I coded this now returns true (check=1) if the person knows just one of the languages. If a given task has more than one language requirement, I need to add abit more code.

Btw, I solved my problem long ago I just wanted to know other way to turn around.

Hope this is not too much confusing...

[Allan]

Hi Joao

That makes it easier to understand.  The Database "task_pre_requisites"  is a list of all the different TASKS and their language requirements.

The Individual TYPE is for each person you want to check against the TASKS

Will  lang_1  always be 'English" and lang_2 = "French" and lang_3 = "German" in "task_pre_requisites"?
or will they be different languages depending on what TASK it is?

And in the PERSON variable will the lang[1] = "English" and lang[2] = "French" and lang[3] = "German"
Or will they be different languages for each person?

WHILE dbGetNext(hstmt_select_2) AND (check=NULL)
      FOR b=0 TO 2
         IF person.lang=language[a]
            IF person.lang_percentage>minimum_language_percentage[a] THEN check=1
         ENDIF
      NEXT b
   ENDWHILE

In that code person.lang goes from 0 to 2.
The retrieved record from the database  language[a] does not change in your code. The value of 'a' is always the same.

So the persons Three different languages (0 to 2) are compared to the "task_pre_requisites" database Language[a] which would be Language[3] seeing the last value of a = 4 in the First For Loop.

Is that what is supposed to be happening?

Allan

[JoaoAfonso]

Will  lang_1  always be 'English" and lang_2 = "French" and lang_3 = "German" in "task_pre_requisites"?

No, the idea is if a task just needs french, only lang_1 is used and ="french". Also it will work if in spite of fill lang_1="french", lang_2="", lang_3="" and lang_4="", I use lang_1="", lang_2="", lang_3="" and lang_4="french".

If I use column names "english", "french", etc, if later on I want to add another language, I need to increase another column.

Indeed there was a mistake in code. This should be a solution to check if a "person" meets requirements for all tasks in db:

Code Select Expand

hstmt_select_2 = dbExecSQL(pdb,"SELECT * FROM task_pre_requisites")
IF hstmt_select_2
dbBindVariable(hstmt_select_2,1,task)
FOR a=0 TO 3
dbBindVariable(hstmt_select_2,2+(a*2),language[a])
dbBindVariable(hstmt_select_2,3+(a*2),minimum_language_percentage[a])
NEXT a
WHILE dbGetNext(hstmt_select_2)
check=NULL
FOR a=0 to 3
FOR b=0 TO 2
IF language[a]=CHR$(0)
check=check+1
ELSE
IF person.lang[b]=language[a]
IF person.lang_percentage[b]>minimum_language_percentage[a] THEN check=check+1
ENDIF
ENDIF
NEXT b
NEXT a
IF check=4
MESSAGEBOX 0,first_name+" "+last_name+" meets the requirements to perform task "+task+".","Testing!"
ELSE
MESSAGEBOX 0,first_name+" "+last_name+" does not meet the requirements to perform task "+task+".","Testing!"
ENDIF
ENDWHILE
dbFreeSQL(hStmt_select_2):hStmt_select_2 = NULL
ENDIF

[Allan]

That is great.

Problem solved!

Allan