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

Roger Daley · Jun 15, 2019

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 · Jun 15, 2019

index???
edit: ahh i guess u just want to have a incremental row number for each row in the resultset?

http://www.sqlitetutorial.net/sqlite-window-functions/sqlite-row_number/ <- seems not supported in b4j

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")

Roger Daley · Jun 15, 2019

MarkusR said:
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

klaus · Jun 15, 2019

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)

MarkusR · Jun 15, 2019

Roger Daley said:
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.

if u insert a record this field is filled automatic.

Roger Daley · Jun 15, 2019

Roger Daley said:
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

MarkusR said:
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..

Roger Daley · Jun 15, 2019

klaus said:
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

mangojack · Jun 16, 2019

Roger Daley said:
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

Roger Daley · Jun 16, 2019

mangojack said:
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.

Regards Roger

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

Roger Daley

Well-Known Member

MarkusR

Well-Known Member

Roger Daley

Well-Known Member

klaus

Expert

MarkusR

Well-Known Member

Roger Daley

Well-Known Member

Roger Daley

Well-Known Member

mangojack

Expert

Roger Daley

Well-Known Member

Similar Threads

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

Well-Known Member

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency