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):
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
person.language[a]_percentage
doesn't look like a legal variable name.
Larry
Hi
Quote2+(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
Quote from: Allan on September 15, 2008, 07:17:11 PM
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%:
QuoteFirst 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
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
QuoteSo 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
dbBindVariable(hstmt_select_2,2,mycountry)
dbBindVariable(hstmt_select_2,1,myfname)in that order
Larry
Quote from: Allan on September 15, 2008, 08:19:46 PM
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.
Quote from: Larry McCaughn on September 15, 2008, 07:47:59 AM
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
TYPE individual
STRING first_name
STRING last_name
STRING country
STRING english_percentage
STRING french_percentage
STRING german_percentage
ENDTYPEI've changed to
TYPE individual
STRING first_name
STRING last_name
STRING country
STRING language[3]
STRING percentage[3]
ENDTYPEIn spite of using this invalid code
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.
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 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
Hi
Trying to figure out your code. Just a few questions please?
In the bind area:
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
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???
Quoteminimum_language_percentage[a]
Is that a variable array or is it supposed to represent something in the TYPE?
In the WHILE loop:
QuoteWHILE 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.
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
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
TYPE individual
STRING first_name
STRING last_name
STRING country
STRING english_percentage
STRING french_percentage
STRING german_percentage
ENDTYPE
I should have used
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:
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...
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?
QuoteWHILE 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
QuoteWill 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:
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
That is great.
Problem solved!
Allan