Recordset.FieldCount 0 or 1 based?

Databases
1

Hi,
when using the RecordSet.FieldCount property - if there are 10 records stored in the database, will this return 10 (as I would expect), or is it 0 based, thus returning 9?

I just don’t want to get caught by an unexpected GOTCHA!

The Language Reference does not indicate this.

Thank you all in advance.

2

*Count should be 1 based, if I’m not mistaken. But one should be cautious of using FieldCount, if paired with the real bug maker IdxField. Just taking a guess, maybe that’s not your end goal IdxField that is. If it is, let us know, I’ll give you a few examples why IdxField is evil.

3

Thanks Jeremy - that’s what I would expect, but you never can tell!

4

Richard, just did a quick, bare bones, no error checking, do not use this as an example code test…

  dim db as new SQLiteDatabase
  db.DatabaseName = ":memory"
  call db.Connect
  
  db.SQLExecute "CREATE TABLE people (name varchar, age integer)"
  db.SQLExecute "INSERT INTO people VALUES ('John', 33)"
  
  Print Str(db.SQLSelect("SELECT * FROM people").FieldCount)

Output is 2.

5

Arghhhhhhh - I must be extremely tired!
My brain swapped FieldCount for RecordCount

Therefore my question should be:
Is RecordCount 0 or 1 based.

Oh dear :frowning:

6

OK, in that case it is still 1 based, however… RecordCount cannot be relied on if you are going to use multiple databases. Only some databases return the number of records available. Some you have to loop through the rows until you hit the end before RecordCount will be valid.

SQLite, however, happens to be one of the (few?) that populate RecordCount from the get go.

  dim db as new SQLiteDatabase
  db.DatabaseName = ":memory"
  call db.Connect
  
  db.SQLExecute "CREATE TABLE people (name varchar, age integer)"
  db.SQLExecute "INSERT INTO people VALUES ('John', 33)"
  
  dim rs as RecordSet = db.SQLSelect("SELECT * FROM people")
  Print Str(rs.FieldCount)
  Print Str(rs.RecordCount)

Prints 2 then 1

BTW, the above code toss into a ConsoleApp.Run event provides a good testing ground for various database operations. Handy to keep around as a .xojo_binary_project just to play with how Xojo works with the db, especially if you are using SQLite, i.e. can just use a :memory database.

7

Thanks Jeremy - much appreciated.