First record of a recordset?

Databases
1

I am trying to switch from Valentina to sqLite, and haves a couple really easy questions.

  1. How do you know what record you are in in the database? In Valentina I would use rs.pointer (or some such thing)

  2. When I do a db.commit, it says that “no transaction is active.” But I opened the database, created the reforest, updated a change, and then Xojo says I can’t commit.

Thanks for your help.

Bill

2
  1. Visit CREATE TABLE and read the section titled “ROWIDs and the INTEGER PRIMARY KEY”.
  2. Visit http://documentation.xojo.com/index.php/SQLiteDatabase and read the section titled “Transactions”.

Visiting those pages and reading those sections will not only answer your questions but may also lead to a better understanding of SQLite and how to access it from Xojo.

3

[quote=43571:@Bill Mounce]I am trying to switch from Valentina to sqLite, and haves a couple really easy questions.

  1. How do you know what record you are in in the database? In Valentina I would use rs.pointer (or some such thing)
    [/quote]
    Records have no inherent “position”
    Since you can query 7 retrieve them in what ever order you want using order by they do not have a “position”

[quote=43571:@Bill Mounce]2. When I do a db.commit, it says that “no transaction is active.” But I opened the database, created the reforest, updated a change, and then Xojo says I can’t commit.
[/quote]
Unless you start a transaction there isn’t one to commit
Basically every “statement” (insert, update, delete) is “auto committed” unless you explicitly start a transaction (a group of commands)

4

But I have two specific needs.

  1. I need to know when I am on the first or last record of true recordset. I can use BOF and EOF if necessary, but it is extra work.

  2. Regardless of the order of the records, I need to be able to go to, let’s say, the fifth record. Do I have to iterate through the recordset every time? Seems like I should be able to go to a specific record without going through all of them.

5

It sounds to me like you might want to just do you own caching. A common technique is to loop through the RecordSet once and store all the primary keys in an array. Then you can navigate it however you want and specifically fetch back the row for the primary key when you need it.

Alternatively, if the set is not large, you could create a custom class that maps to it and then load up an array of class instances populated with the DB data and then refer to that.

6

Thanks. I am almost there. When you say “fetch,” I assume you mean do a sql call for that one record?

7

Yes, one DB call to get the one record.