Sqlite - sequentially accessing the first row

[glenn9]

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...!

[Cairoo]

I was hoping that movetoNextRecord would solve this...!

I'd be stumped if it doesn't.

[glenn9]

I feel its got to work as well, I just need to figure out what I`m not understanding....!

[Cairoo]

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.

Code: Select all

revMoveToRecord tCursorID. tRecordNumber - 1

Gerrie

[stam]

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:

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 

Easy enough to navigate the numerically indexed array then…
That’s the approximate workflow I’d consider anyway…

HTH
Stam

[glenn9]

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...

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
   ...

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

[stam]

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

Code: Select all

If the short name of the target is <your button name> then
   //do stuff
end if

Alternatively, you can exit the handler by putting this just after 'wait until the mouseClick'

Code: Select all

if the short name of the target <>  <your button name> then exit <handler name>

Just as a cautionary word though - keeping the recordset open increases the risk of database corruption (eg due to crash etc)
HTH
S.

[stam]

Code: 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
   ...

I'm not sure your code is optimal - you are controlling flow of the app inside a loop that can block your interface.
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

In the same script you can implement navigation - eg to go forward

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

And of course you can implement this in reverse if you want to navigate backwards...
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.

[stam]

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