I have decided to write an examination using a database as the method for storing the questions and answers.
Seeing as I didn’t want to re-invent the wheel I created my database in Access making sure I could read it in Authorware.
I used the show me ODBC.a7p file as the base for my Authorware file and the way I retrieve the data.
All works fantastic and I can retrieve anything I look for however I am having just one small problem I hope someone can help me with.
For example I use DB_SQLString:= "SELECT [ans4] FROM [Table] WHERE [recordnumber]=" ^ MyRecNum to retrieve Answer no 4
I would like to use DB_SQLString:= "SELECT [ans1], [ans2], [ans3], [ans4] FROM [Table] WHERE [recordnumber]=" ^ MyRecNum to retrieve all answers, but unfortunately I then Can’t separate the resulting string into the separate answer elements..
I also hope this make sense…
You can use a loop to run through the resulting string and use GetLine
and a custom separator to pull each item...thing is, hopefully the
returned string has something separating each answer.
Can you post a sample DB_SQLString result?
Thanks for the help Eric..
I've tried using GetLine.
I've tried using the List() function but can't find the separator.
I've even tried Substr() but again can't find the common separator.
When I said I didn't want to re-invent the wheel I meant it (lol) I am using the 'Execute SQL Command' routine from the ODBC.a7p showme example.
The resulting string comes very nicely presented, however I need it to do more.
I have users out there who will post the answers on a notice board. I therefore need not only to pick a random selection of questions but on each question picked I then need to present the answers in a random format. For example the same questions presented on two computers, on PC1 the correct answer is at the 3rd position and on PC2 the correct answer is shown at position 2.
I am using the ODBC.a7p DB_SQLString:= "SELECT [question] FROM [Table] WHERE [recordnumber]=" ^ MyRecNum command line to get the result.
Do you think I should not use the ODBC.a7p example and create my own 'Execute SQL Command' routine?
And I thought this would be the easier method lol
Sorry I can't actualy post the resulting string as it's contain within a variable. I could give you a screen copy but I didn't think it would help.
If you run the ODBC.a7p example in the showme folder on your c Drive then this is what I am using. I have not changed the 'Execute SQL Command' routine' in any way except to re-name the fields within the Table from Forname, Surname etc to ans12, ans2,ans3....
I've tried converting the resulting string to a list using the list function along with the line seperator as the return, enter, tab keys etc but nothing seems to work.
I will try using the suggestion of doing some sort of loop to retrieve the data - IF I get chance it will be today if not Monday.
I can't post the actual content of our quiz as it contains restricted data but as I am using the ODBC example I've used that as a Database and taken a screen copy of what I am trying to acheive.
1:MAP:Run to View Documentation (enter) I'm trying to get the resultant DB_ODBCData string seperated into the 4 different names as above. Unfortunately I'm not getting there. I'm beginning to think that I'm using the wrong method of retrieving data from a database - but have no idea which direction to go in. Paul
2:INT:Main Menu (enter)
2:MTN:Run the Example
1:INT:Retrieve Data (enter)
1:MAP:Sorted Student List (enter)
2:CLC:Sorted Student List
2:NAV:Navigate to "Execute SQL Command"
1:MAP:Execute SQL Command (enter)
-- ATHERTON BOBBI J
JOHNSON JOHN J
WISE CURTIS D
2:CLC:Send SQL Command
2:CLC:Check for ODBC Error
1:MAP:Run to View Documentation (enter)
I'm trying to get the resultant DB_ODBCData string seperated into the 4 different names as above. Unfortunately I'm not getting there.
I'm beginning to think that I'm using the wrong method of retrieving data from a database - but have no idea which direction to go in.
Your separator must be a RETURN character, which Authorware sees as "\r"
You can use something like this to divide your data into a list, then
it is easy to use
repeat with i:= 1 to LineCount(yourdata)
Then when you need to get the 4th name, you use Names
Sorry tried it and didn't work. - Linecount showed only as 1 (one) Everything was in Names record which is the problem I've been having all along.
Thanks for your help steve but I don't want to waste anymore of your time I will just use my original solution. Not very nice but it works. Basically just getting one bit of data at a time
Forgive me if I'm missing something but to me it seems the issue is basic. You guys are forgetting the separators. Between records it's a return and between fields it's a tab.
DB_SQLString:= "SELECT [ans1], [ans2], [ans3], [ans4] FROM [Table] WHERE [recordnumber]=" ^ MyRecNum
DB_Data := ODBCExecute(handle, DB_SQLString)
ResultLine = GetLine(DB_Data, 1) -- get first line (probably not necessary because there's only one
Ans1 = GetLine(ResultLine, 1, 1, tab) -- get field 1
Ans2 = GetLine(ResultLine, 2, 2, tab) -- get field 2
Ans2 = GetLine(ResultLine, 2, 2, tab) -- get field 2
Ans3 = GetLine(ResultLine, 3, 3, tab) -- get field 3
Ans4 = GetLine(ResultLine, 4, 4, tab) -- get field 4
This will get the items into the places you want. To take it a bit further you look at the code and notice that they're identical except for the number used for the Ans and the field. A common practice in this case is to set up a loop with a control variable that executes the same code over and over. Our problem is that we have four different variables and we can't cycle through the names to assign values. A "List" type variable is the best idea. We'll create a list type called Ans
Ans =  -- empty list
Repeat with i := 1 to 4
Ans[i] = GetLine(ResultLine, i, i, tab) -- get a single item into the spot in the list
i is the control variable and it increments by one each repeat of the loop. So it's 1 the first time and it does GetLine(ResultLine, 1, 1, tab) and puts that in Ans. On the second loop it does GetLine(ResultLine, 2, 2, tab) and puts it into spot 2. So anywhere in your code where you're using Ans1 you could use Ans instead. If you don't want to keep using ans1, 2, 3, and 4 you can use the first example without the loop and just assign the four items one on each line.
Let's say just for argument's sake you don't want to use a list and you do want to use the four variables and a loop to load them. You might want to do that because you don't know in advance if you're going to have 4, 2, or 6 answers. I mentioned up above that we can't just cycle through the names. Actually we can, but it involves some tricky code. The EvalAssign function takes a piece of text and executes it as if it were programming code. Here is the example of the loop with i as the control variable again but this time it uses EvalAssign to build the variable name Ans1, Ans2, Ans3, and Ans4 on the fly.
RepeatWith i := 1 to 4
EvalAssign("Ans"^i^" = GetLine(ResultLine, "^i^", "^i^", tab)")
This is what we used to use before lists were made. This is a rather trivial example. In order to make it mroe real-world we'd also control the limit of 1 to 4 with some other variable to indicate how many distracters are being loaded.
Eek - EvalAssign("Ans"i" = GetLine(ResultLine, "i", "i", tab)")
What's wrong with a nice List?
Apparently looking fot Return separator didn;t work for him, though
that surprises me ...
I don't think it was a return separator the OP needed. It was the tab separator between fields of the return. Between each record will be a return, between the fields like [Ans1] and [Ans2] will be a tab char.
Only thing wrong with using a list is if there's already 20,000 places where the code has Ans1, Ans2, etc on displays or other code.
Just tried using
DB_SQLString:= "SELECT [question], [ans1], [ans2], [ans3], [ans4], [ans5], [ans6] FROM [TABLE] WHERE [recordnumber]=" ^ MyRecNum
Navigate to "Execute SQL Command"
Question = GetLine(DB_ODBCData, 1, 1, tab) -- get field 1
Ans1 = GetLine(DB_ODBCData, 2, 2, tab) -- get field 2
Ans3 = GetLine(DB_ODBCData, 4, 4, tab) -- get field 4
Ans2 = GetLine(DB_ODBCData, 3, 3, tab) -- get field 3
Ans4 = GetLine(DB_ODBCData, 5, 5, tab) -- get field 4
Ans5 = GetLine(DB_ODBCData, 6, 6, tab) -- get field 4
Ans6 = GetLine(DB_ODBCData, 7, 7, tab) -- get field 4
AND IT WORKED A TREAT (YAAAAAAHHHHHHHHOOOOOOOOOO!) Thanks Mike
Now to get it all re-written into a for next loop, as you say it will work much better, I just used the example Ans1, Ans2, Ans3 to try to make my request a bit easier to understand.
Many thanks also to Seve for spending time on this.
Pat on the back to both of you
Europe, Middle East and Africa