Sqlite - sequentially accessing the first row

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

glenn9
Posts: 220
Joined: Wed Jan 15, 2020 10:45 pm
Location: Europe

Sqlite - sequentially accessing the first row

Post by glenn9 » Tue May 17, 2022 7:42 am

Dear All,

I`ve put together a flashcard app which works reasonably well but I`m also wanting to sequentially go through the records, for example to edit etc.

I`ve stored all the fields in sqlite but having a problem with accessing the first sqlite row!

For example, to go back through the records sequentially I`ve put together this code:

Code: Select all


on mouseup
   put tRecordNumber - 1 into tRecordNumber
   if tRecordNumber <= 1 then 
      put 1 into tRecordNumber
   end if
   
   openDB
   
   put "SELECT Question, Answer, _ROWID_, Tag FROM FC" & CR & "LIMIT 1 OFFSET '"&tRecordNumber&"'" into tSQL
   put revdb_querylist(,,gConID,tSQL) into tList
   
   set itemdelimiter to tab
   put item 1 of tList into fld"Front"
   put item 2 of tList into fld"Back"
   put item 3 of tList into fld"RowID"
   put item 4 of tList into fld"Tag"
   
   closeDB
   
end mouseup
This works fine until I want to access the first record of Sqlite, as despite tRecordNumber showing 1, all I can access is record 2 of Sqlite!

Not sure why I can`t access the first record of sqlite using the above code??

Grateful for any tips on what I`m doing wrong!

Many thanks,

Glenn

Klaus
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Sqlite - sequentially accessing the first row

Post by Klaus » Tue May 17, 2022 8:28 am

Hi Glenn,

well, to access the first record, just omit -> ... & CR & "LIMIT 1 OFFSET '"&tRecordNumber&"'", which will skip the records up to tRecordnumber! 8)

Best

Klaus

Cairoo
Posts: 107
Joined: Wed Dec 05, 2012 5:54 pm

Re: Sqlite - sequentially accessing the first row

Post by Cairoo » Tue May 17, 2022 9:05 am

Hi Glenn,

Omitting the LIMIT OFFSET clause as Klaus suggested would result in returning all the rows instead of just one row, due to the fact that the revdv_querylist function returns all the data of the records matching the SELECT query.

For sequential access, i.e. accessing each row one-by-one, you may want to use the revQueryDatabase function which returns a record set ID (cursor) you can use to sequentially step through the records using the revMoveToFirstRecord, revMoveToNextRecord, revMoveToPreviousRecord, and revMoveToLastRecord commands. To retrieve the column values of the current row, you would use the revDatabaseColumnNamed function.

If, however, you still want to use the revdb_querylist function to retrieve one record at a particular offset, then please note that the number you specify after the OFFSET keyword tells Sqlite how many rows to skip, e.g. "OFFSET 10" would start retrieving records at the 11th row. This explains why you could not see the first row, because "OFFSET 1" tells Sqlite to skip 1 row and start retrieving from the 2nd row.

Regards,

Gerrie

glenn9
Posts: 220
Joined: Wed Jan 15, 2020 10:45 pm
Location: Europe

Re: Sqlite - sequentially accessing the first row

Post by glenn9 » Tue May 17, 2022 1:34 pm

Many thanks for this which has resulted in some progress in that I can now query my database using revQueryDatabase

Code: Select all

put "SELECT * FROM FC" into vSQL 
   put revQueryDatabase(gConID, vSQL) into cID 
   put revNumberofRecords(cID) into vRecNo
   answer vRecNo 
but unfortunately that`s as far as I`ve got...

I guess I`m not understanding the syntax to implement the `revMoveToFirstRecord` command!

i`ve looked at past postings on the forum and dictionary but I can`t see an example of the syntax to use...

Grateful for any help!

Thanks,

Glenn

Cairoo
Posts: 107
Joined: Wed Dec 05, 2012 5:54 pm

Re: Sqlite - sequentially accessing the first row

Post by Cairoo » Tue May 17, 2022 4:00 pm

glenn9 wrote:
Tue May 17, 2022 1:34 pm
I guess I`m not understanding the syntax to implement the `revMoveToFirstRecord` command!
The dictionary does in fact show the syntax, but perhaps you missed it because it really is as simple as

Code: Select all

revMoveToFirstRecord cID
Gerrie

glenn9
Posts: 220
Joined: Wed Jan 15, 2020 10:45 pm
Location: Europe

Re: Sqlite - sequentially accessing the first row

Post by glenn9 » Tue May 17, 2022 4:14 pm

Thanks Gerrie,

If I use this code:

Code: Select all

on mouseup
   openDB
   
   
   put "SELECT * FROM FC" into vSQL 
   put revQueryDatabase(gConID, vSQL) into cID 
   revMoveToFirstRecord cID
   
   answer cID
   
   closeDB
end mouseup
I`m not sure what to do with the `cID` - at the moment I`m getting cID to be a number...
not sure what this number means!?

Regards,

Glenn

Cairoo
Posts: 107
Joined: Wed Dec 05, 2012 5:54 pm

Re: Sqlite - sequentially accessing the first row

Post by Cairoo » Tue May 17, 2022 5:08 pm

cID is the record set ID a.k.a cursor ID that identifies the set of records returned by revQueryDatabase. You pass this ID to the revMoveToFirstRecord, revMoveToNextRecord, revMoveToPreviousRecord, and revMoveToLastRecord commands to navigate between records, and to the revDatabaseColumnNamed function to retrieve the column values of the current row.

glenn9
Posts: 220
Joined: Wed Jan 15, 2020 10:45 pm
Location: Europe

Re: Sqlite - sequentially accessing the first row

Post by glenn9 » Wed May 18, 2022 7:59 am

Have made some progress...

The problem I discovered was that I wasn`t understanding how to visualise the data that revMove... etc was producing but then discovered the command`revDatabaseColumnNamed`!

Using this I can now visualise the first (and last) records of the database using this code:

Code: Select all

   ...
   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"
   ...
However I`m still struggling with the syntax of moving to the `next` (and `previous`) records though...

I guess somewhat optimistically I thought this might work...

Code: Select all

   put "SELECT * FROM FC" into vSQL 
   put revQueryDatabase(gConID, vSQL) into cID 
 
   revMoveToNextRecord cID
   
   put revDatabaseColumnNamed(cID, Question) into fld"Front"
   put revDatabaseColumnNamed(cID, Answer) into fld"Back"
   put revDatabaseColumnNamed(cID, nr) into fld"RowID"
and although it gets me to a next record, it does not advance any further

I`m assuming I need some kind of loop that is activated with each mouseclick but despite lots of tries have yet to discover how to code this!

Any hints gratefully received!

Thanks,

Glenn

stam
Posts: 2636
Joined: Sun Jun 04, 2006 9:39 pm
Location: London, UK

Re: Sqlite - sequentially accessing the first row

Post by stam » Wed May 18, 2022 12:02 pm

Hi Glenn,

forgive me for not reading this thread properly and risking either answering the wrong question or possibly reiterating stuff others have said.

In general:
- i would use revQueryDatabase to get a 'database cursor' - this is basically a collection of records returned by your query and this record set is represented by an ID - the 'cID' i saw in an example above is basically the internal name for the database cursor (the recordset returned by your SELECT statement).

- To navigate the cursor i would use revdb_move*(ID) (where * is first, last, next or previous and ID is the recordset ID from the previous step).

- to access the first record just use revDB_moveFirst(ID). If the cursor is not in the order you expect, ie you identify 'first' by a column value, you should include an ORDER BY clause in your SQL query so that the records are in the sequence you want.

Again, forgive me if I'm missing the point, but hope this helps...
S.

Klaus
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Sqlite - sequentially accessing the first row

Post by Klaus » Wed May 18, 2022 12:08 pm

Hi glenn,

some hints:
1. VERY IMPORTANT!
Get used to QUOTE names or this may get you into trouble when you least exspect it!

Code: Select all

...
put revDatabaseColumnNamed(cID, "Answer") into fld"Back"
## e.g. Answer is a reserved word in LC!
...
2. You need to check "the result" after moving in the db cursor, it will give you FALSE if you reached at the
last record or there are no records at all or a string starting with "revdberr," if something else went wrong:

Code: Select all

...
revMoveToNextRecord cID
if the result <> EMPTY then 
  answer "Problem: && the result
end if
...
3. Not sure, but I think LC needs a starting point to know what NEXT record may be:

Code: Select all

...
  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"
...
Now you should be able to use -> revMoveToNextRecord cID in a repat loop with checking the result right after that.
Again, this is what I guess, so no guarantee, never worked this way so far with a database. 8-)

Best

Klaus

stam
Posts: 2636
Joined: Sun Jun 04, 2006 9:39 pm
Location: London, UK

Re: Sqlite - sequentially accessing the first row

Post by stam » Wed May 18, 2022 12:22 pm

Hi Klaus,

While i've not used this in LiveCode (but have done in other languages), i would normally check if a at the start or end of the database cursor (ie BOF or EOF) rather resorting to checking the result and seeing if an error has been generated (of course one should ALWAYS do this, but not to check the bounds of the cursor)

The dictionary includes the commands revQueryIsAtStart(<recordSetID>), revQueryIsAtEnd(<recordSetID>) so i would use these for bounds checking.
(synonyms: revdb_isbof and revdb_iseof)

I also saw there is a nice command to jump to a specific record in the cursor: revMoveToRecord <recordSetId>, <recordNumber>
Although not supported by all databases...

I'm not sure but i think each recordSet (named by it's ID) remembers it's cursor position.

Can't really contribute more as some time ago i switched to LiveCloud for may database needs, but useful discussion as no doubt will need to use an sqlite database in the future...

S.

Klaus
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Sqlite - sequentially accessing the first row

Post by Klaus » Wed May 18, 2022 12:29 pm

Hi stam,

ah yes, thanks for the hints with rev_isbof etc.!
Been a while I have been working with a DB in LC...


Best

Klaus

Cairoo
Posts: 107
Joined: Wed Dec 05, 2012 5:54 pm

Re: Sqlite - sequentially accessing the first row

Post by Cairoo » Wed May 18, 2022 1:26 pm

and although it gets me to a next record, it does not advance any further
Glenn

The code that opens the database and retrieves the cursor id should probably not be in the same handler that navigates between records. Perhaps you can open the database and retrieve the cursor id in the openStack handler, and close the cursor and the database in the closeStack handler? Then the variables that store the database id and cursor id would have to be global variables.

Gerrie

stam
Posts: 2636
Joined: Sun Jun 04, 2006 9:39 pm
Location: London, UK

Re: Sqlite - sequentially accessing the first row

Post by stam » Wed May 18, 2022 2:07 pm

Cairoo wrote:
Wed May 18, 2022 1:26 pm
Then the variables that store the database id and cursor id would have to be global variables.
Or a script local variable - assuming all handlers are in the same script. I generally prefer this approach as they are then global but to that script only and don’t have to worry more about them in other scripts…

glenn9
Posts: 220
Joined: Wed Jan 15, 2020 10:45 pm
Location: Europe

Re: Sqlite - sequentially accessing the first row

Post by glenn9 » Wed May 18, 2022 3:43 pm

Gerrie, Klaus, Stam,

Many thanks for all the advice and tips - unfortunately still no luck, even with separating out the handlers and using script local variables.

However, I do feel I`ve now got a better understanding of accessing sqlite db and have also learnt a few more commands!

I`ll keep on trying...!

Regards,

Glenn

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”