Sqlite - sequentially accessing the first row
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller
Re: Sqlite - sequentially accessing the first row
omitted to mention that `revMovtoRecord` works ok but seems to work in the same way as `offset`.
Ie, if I`m just sequentially going through records it doesn`t really matter using offset or movetoRecord, but I`ve discovered its an issue when I edit a record in that sometimes I seem to be editing the incorrect record (by 1) but I guess I can take this into account with a script...
I was hoping that movetoNextRecord would solve this...!
Ie, if I`m just sequentially going through records it doesn`t really matter using offset or movetoRecord, but I`ve discovered its an issue when I edit a record in that sometimes I seem to be editing the incorrect record (by 1) but I guess I can take this into account with a script...
I was hoping that movetoNextRecord would solve this...!
Re: Sqlite - sequentially accessing the first row
I'd be stumped if it doesn't.I was hoping that movetoNextRecord would solve this...!
Last edited by Cairoo on Wed May 18, 2022 4:33 pm, edited 2 times in total.
Re: Sqlite - sequentially accessing the first row
I feel its got to work as well, I just need to figure out what I`m not understanding....!
Re: Sqlite - sequentially accessing the first row
The dictionary states that revMoveToRecord takes as second argument the zero-based index of the record to move to. You can correct for this in your script by just subtracting 1 from your one-based index when passing it to revMoveToRecord. e.g.
Gerrie
Code: Select all
revMoveToRecord tCursorID. tRecordNumber - 1
Last edited by Cairoo on Wed May 18, 2022 4:38 pm, edited 1 time in total.
Re: Sqlite - sequentially accessing the first row
Hi Glenn,
** edited for silly autocorrects on the phone **
In general I avoid keeping the database cursor around for long. While it’s in use it keeps an open connection to the database and increases the risk of bad things happening.
The most bullet proof way to deal with this is
- open a connection to database
- do a query or command
- if a recordSet is returned then store this in a temp data structure
- close the recordSet
- close the connection to the database
So in your case, I wouldn’t keep this recordSet open anyway, and wouldn’t use this to navigate flash cards (if I understood correctly that’s what you’re doing - apologies if this wrong).
Instead, I’d just grab the records and put them in a numerically indexed array. Approximate pseudocode:
Easy enough to navigate the numerically indexed array then…
That’s the approximate workflow I’d consider anyway…
HTH
Stam
** edited for silly autocorrects on the phone **
In general I avoid keeping the database cursor around for long. While it’s in use it keeps an open connection to the database and increases the risk of bad things happening.
The most bullet proof way to deal with this is
- open a connection to database
- do a query or command
- if a recordSet is returned then store this in a temp data structure
- close the recordSet
- close the connection to the database
So in your case, I wouldn’t keep this recordSet open anyway, and wouldn’t use this to navigate flash cards (if I understood correctly that’s what you’re doing - apologies if this wrong).
Instead, I’d just grab the records and put them in a numerically indexed array. Approximate pseudocode:
Code: Select all
revdb_moveFirst
Repeat While not revdb_isEoF
Add 1 to x
Put each column of the record into tArray[x][columnName]
revdb_moveNext
end repeat
Close recordSet
That’s the approximate workflow I’d consider anyway…
HTH
Stam
Re: Sqlite - sequentially accessing the first row
Thanks Stam, all noted - it will give me practice in engaging with arrays again although I`m still in a `dog with a bone` mode re trying to get the revMoveToNextRecord to work!
I recall from a previous forum posting in which the revMoveToNextRecord command was used, I`m assuming successfully, that it was used within a loop and so I thought why not do the same and so tried using the mouseclick command with a repeat loop like this... and it works....!
Well, to a degree, the record now advances with each mouseclick as intended, but the problem I now have is that it fires with EVERY mouseclick, ie it fires even when the mouseclick is outside of the relevant button, infact anywhere on the card...!!
I guess all I`ve got figure out now is how to only have it fire when the button is clicked, and no where else... back to the drawing board....!
Regards,
Glenn
I recall from a previous forum posting in which the revMoveToNextRecord command was used, I`m assuming successfully, that it was used within a loop and so I thought why not do the same and so tried using the mouseclick command with a repeat loop like this...
Code: Select all
...
put "SELECT * FROM FC" into vSQL
put revQueryDatabase(gConID, vSQL) into cID
repeat with x = 1 to revNumberOfRecords(cID)
wait until the mouseclick
revMoveToNextRecord cID
put revDatabaseColumnNamed(cID, "Question") into fld"Front"
put revDatabaseColumnNamed(cID, "Answer") into fld"Back"
put revDatabaseColumnNamed(cID, "nr") into fld"RowID"
end repeat
...
Well, to a degree, the record now advances with each mouseclick as intended, but the problem I now have is that it fires with EVERY mouseclick, ie it fires even when the mouseclick is outside of the relevant button, infact anywhere on the card...!!
I guess all I`ve got figure out now is how to only have it fire when the button is clicked, and no where else... back to the drawing board....!
Regards,
Glenn
Re: Sqlite - sequentially accessing the first row
i presume this is in the card script then?
If that's the case you can just add a clause to check if the target is (or is not) your button, eg
Alternatively, you can exit the handler by putting this just after 'wait until the mouseClick'
Just as a cautionary word though - keeping the recordset open increases the risk of database corruption (eg due to crash etc)
HTH
S.
If that's the case you can just add a clause to check if the target is (or is not) your button, eg
Code: Select all
If the short name of the target is <your button name> then
//do stuff
end if
Code: Select all
if the short name of the target <> <your button name> then exit <handler name>
HTH
S.
Re: Sqlite - sequentially accessing the first row
I'm not sure your code is optimal - you are controlling flow of the app inside a loop that can block your interface.glenn9 wrote: ↑Thu May 19, 2022 4:20 pmCode: Select all
... repeat with x = 1 to revNumberOfRecords(cID) wait until the mouseclick revMoveToNextRecord cID put revDatabaseColumnNamed(cID, "Question") into fld"Front" put revDatabaseColumnNamed(cID, "Answer") into fld"Back" put revDatabaseColumnNamed(cID, "nr") into fld"RowID" end repeat ...
No such loop should be needed.
If you make the cID variable script local (ie define it at the start of the script, outside any handlers) it will (*should* - this code is untested) remember it's position.
so your code could be changed to a command to retrieve the ID for the recordSet (cID) and further command to navigate forward & backward. As long as all are on the same script, this will (*should*) work.
Code: Select all
-- do the query and store the recordSet ID
local cID
command getRecordSet
put empty into cID -- reset the script local variable in case the query fails
put "SELECT * FROM FC" into vSQL
put revQueryDatabase(gConID, vSQL) into cID
revMoveToFirstRecord cID
put revDatabaseColumnNamed(cID, "Question") into fld"Front"
put revDatabaseColumnNamed(cID, "Answer") into fld"Back"
put revDatabaseColumnNamed(cID, "nr") into fld"RowID"
end getRecordSet --> this sets the script local variable cID to hold the instance of your recordSet and displays first record
Code: Select all
command goNext
if revQueryIsAtEnd(cID) then exit goNext -- or go back to first with revMoveToFirstRecord
if cID is empty then exit goNext -- in case the previous handler failed
revMoveToNextRecord cID
put revDatabaseColumnNamed(cID, "Question") into fld"Front"
put revDatabaseColumnNamed(cID, "Answer") into fld"Back"
put revDatabaseColumnNamed(cID, "nr") into fld"RowID"
end goNext
As rule i minimise use of loops as they add a lot of computational overhead and can block the interface... I can't test this myself right now, and this is completely unproven & untested, but in my mind it *should* work... give it a try and let us know
HTH
S.
Re: Sqlite - sequentially accessing the first row
BTW, the script should live in the card script ideally.
Your button only needs the goNext command, which will be executed in the card script (so that the same cID is used)
In theory at least - untested
Your button only needs the goNext command, which will be executed in the card script (so that the same cID is used)
In theory at least - untested