Android Question Get rowid from SQL database

[andredamen]

I know how to get al the data from a sql database, but the row begins with a rowid. I want to us this rowid. How do I retrieve this field from the sql database? Is there someone who can help me with this?

 

[josejad]

how to get al the data from a sql database

What sentence do you use for this?

 

[mc73]

select rowid,* from yourTable

 

[Andrew (Digitwell)]

I would suggest that in most(all) cases you don't need a rowid but should be using the primary key for the table.

 

[Mahares]

most(all) cases you don't need a rowid but should be using the primary key for the table.

The primary key is an alias for the rowid only if the primary key is: INTEGER PRIMARY KEY or ( Integer Primary key autoincrement). But a table can have a TEXT field as PRIMARY KEY or even a multi fields as PRIMARY KEY. In that case using the rowid is a viable way as shown in post #3

 

[Andrew (Digitwell)]

@Mahares, you are of course correct in your description of a primary key, but I wasn't talking directly about an alias for rowid but that in general the primary key should be used to UNIQUELY reference items in the table and rowid should be avoided.

an extract from the SQLLite documentation (i.m making an assumption that the OP is using this)

Rowid Tables

All of the complications above (and others not mentioned here) arise from the need to preserve backwards compatibility for the hundreds of billions of SQLite database files in circulation. In a perfect world, there would be no such thing as a "rowid" and all tables would following the standard semantics implemented as WITHOUT ROWID tables, only without the extra "WITHOUT ROWID" keywords. Unfortunately, life is messy. The designer of SQLite offers his sincere apology for the current mess.

 

[andredamen]

Thanks for jour reply's. You can't acces and then use the rowid. I know now. I'm going to us a primary key. In ACCES you can us a auto number so that the field is always unique. By using a fieldin SQLITE as primary key I must fill that field myself in the program. I hoped that there was a way in SQLITE whit auto numbering.

 

[Andrew (Digitwell)]

see this:

1. The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.
2. In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer.
3. On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used.
4. If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

from

SQLite Autoincrement

 

[Mahares]

By using a fieldin SQLITE as primary key I must fill that field myself in the program

That is not correct. You can use one of the fields and have SQLite auto fill it for you. See example below:

strQuery="CREATE TABLE IF NOT EXISTS Country (ID INTEGER PRIMARY KEY, COUNTRY TEXT, POPULATION TEXT)"
SQL1.ExecNonQuery(strQuery)

strQuery ="INSERT INTO Country VALUES(?,?,?)"
SQL1.ExecNonQuery2(strQuery, Array As Object(Null, "Netherlands","17500000"))

I hope we are not beating a dead horse.

 

[andredamen]

Thanks for all the help. I made a ID field as INTEGER with UNIQUE set as true and use it as a PRIMARY KEY. And this works! Fine, thanks.