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

[Roger Daley]

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.

   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]

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

sql1.ExecNonQuery2("INSERT INTO Inspection (Title) VALUES (?);", Array As Object("New") )
Item.InspectionId = sql1.ExecQuerySingleResult("SELECT last_insert_rowid() FROM Inspection")

 

[Roger Daley]

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

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]

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.

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:

Private  RowLine As Int
RowLine = SQL1.ExecQuerySingleResult("SELECT max(rowid) FROM sites")
Log("RowLine  ="&  RowLine)

 

[MarkusR]

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]

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..

 

[Roger Daley]

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.

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:

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]

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]

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