Android Question RowIndex is there such a thing. [SOLVED: Answer NO]

Roger Daley

Well-Known Member
Licensed User
Longtime User
Hi All,

A grasping at straws question regarding finding the index of a value in a SQL table. I have a thought that somewhere in my recent researches something that gives the row index.

Example code that doesn't work.

B4X:
   Private  RowLine As Int
   RowLine = SQL1.ExecQuery("SELECT rowindex  FROM sites max(rowid) ")
   Log("RowLine  ="&  RowLine)

My question is:

Is there such a beast as "rowindex" or something like it? Yes/No.

If no, it must have been a dream. If yes please tell me the correct name/syntax.

Regards Roger
 

MarkusR

Well-Known Member
Licensed User
Longtime User
Last edited:
Upvote 0

Roger Daley

Well-Known Member
Licensed User
Longtime User
index???
edit: ahh i guess u just want to have a incremental row number for each row in the resultset?

are you trying to make a auto increment value?

https://www.sqlite.org/lang_createtable.html#rowid

B4X:
sql1.ExecNonQuery2("INSERT INTO Inspection (Title) VALUES (?);", Array As Object("New") )
Item.InspectionId = sql1.ExecQuerySingleResult("SELECT last_insert_rowid() FROM Inspection")
MarkusR Thanks for the reply but it's NOT an auto increment problem.

I have this rowindex thing stuck in my head. It is like sometimes you get part of a tune stuck in your head but can't remember the rest.

Regards Roger
 
Last edited:
Upvote 0

klaus

Expert
Licensed User
Longtime User
It depends on what you want.
rowid is an internal, autoincrement, column in SQLite.
If you want a list of the rowids you can use something like this:
RowIDList is a List.
B4X:
Private Sub ReadDataBaseRowIDs
    Private ResultSet1 As ResultSet
   
    ResultSet1 = SQL1.ExecQuery("SELECT rowid FROM sites")
   
    'We read only the rowid column and put them in RowIDList
    RowIDList.Initialize          'initialize the ID list
    Do While ResultSet1.NextRow
        RowIDList.Add(ResultSet1.GetInt2(0))    'add the rowid's to RowIDList
    Loop
    ResultSet1.Close                   'close the ResultSet, we don't need it anymore
End Sub

If you want the highest rowid value then you can use:
B4X:
Private  RowLine As Int
RowLine = SQL1.ExecQuerySingleResult("SELECT max(rowid) FROM sites")
Log("RowLine  ="&  RowLine)
 
Upvote 0

MarkusR

Well-Known Member
Licensed User
Longtime User
MarkusR Thanks for the reply but it's an auto increment problem.
I thought so.
you can also setup a autoincrement field in the table.
Snap_2019.06.15_14h18m24s_001_.png

if u insert a record this field is filled automatic.
 
Last edited:
Upvote 0

Roger Daley

Well-Known Member
Licensed User
Longtime User
MarkusR Thanks for the reply but it's an auto increment problem.

I have this rowindex thing stuck in my head. It is like sometimes you get part of a tune stuck in your head but can't remember the rest.

Regards Roger
I thought so.
you can also setup a autoincrement field in the table.
View attachment 81342

if u insert a record this field is filled automatic.
Sorry MarkusR I meant to type it's not an auto increment problem..
 
Upvote 0

Roger Daley

Well-Known Member
Licensed User
Longtime User
It depends on what you want.
rowid is an internal, autoincrement, column in SQLite.
If you want a list of the rowids you can use something like this:
RowIDList is a List.
B4X:
Private Sub ReadDataBaseRowIDs
    Private ResultSet1 As ResultSet
  
    ResultSet1 = SQL1.ExecQuery("SELECT rowid FROM sites")
  
    'We read only the rowid column and put them in RowIDList
    RowIDList.Initialize          'initialize the ID list
    Do While ResultSet1.NextRow
        RowIDList.Add(ResultSet1.GetInt2(0))    'add the rowid's to RowIDList
    Loop
    ResultSet1.Close                   'close the ResultSet, we don't need it anymore
End Sub

If you want the highest rowid value then you can use:
B4X:
Private  RowLine As Int
RowLine = SQL1.ExecQuerySingleResult("SELECT max(rowid) FROM sites")
Log("RowLine  ="&  RowLine)
Thanks Klaus,
This is a great explanation but you've already given me the answer to the original problem in another thread.
It is just that when I was researching I came across terms like : "rowindex, row.index and row_index" and thought that there might be a term rowindex equivalent of the term rowid.

It appears there is not, I will take that as the answer.

Regards Roger
 
Upvote 0

mangojack

Well-Known Member
Licensed User
Longtime User
and thought that there might be a term rowindex equivalent of the term rowid.

Just a bit of extra info regarding the internal field 'rowid'. As @klaus mentioned above it auto increments when records are added to the db.
But it will not always reflect the number of records in your db.

For instance ... if you enter 5 records into your db, then delete record 2 & 3, then add a 6th record your db will show rowid's of 1, 4, 5, 6.

You can correct this , depending on a condition , but that is getting way over the scope of your question.

this might be of interest. https://stackoverflow.com/questions/35876171/sqlite-rowid-after-deleting-rows
 
Upvote 0

Roger Daley

Well-Known Member
Licensed User
Longtime User
Just a bit of extra info regarding the internal field 'rowid'. As @klaus mentioned above it auto increments when records are added to the db.
But it will not always reflect the number of records in your db.

For instance ... if you enter 5 records into your db, then delete record 2 & 3, then add a 6th record your db will show rowid's of 1, 4, 5, 6.

You can correct this , depending on a condition , but that is getting way over the scope of your question.

this might be of interest. https://stackoverflow.com/questions/35876171/sqlite-rowid-after-deleting-rows


Thanks mangojack,

It's all good stuff, looking at things from different angles helps to get the big picture and to get it stuck in my head. Sometimes I have go around in circles a few times to build up enough knowledge to understand what I read in the first instance.:confused:;):)


Regards Roger
 
Last edited:
Upvote 0
Top