Android Question Get rowid from SQL database

andredamen · Jul 21, 2021

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 · Jul 21, 2021

andredamen said:
how to get al the data from a sql database

What sentence do you use for this?

mc73 · Jul 21, 2021

B4X:

select rowid,* from yourTable

Andrew (Digitwell) · Jul 21, 2021

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 · Jul 22, 2021

Andrew (Digitwell) said:
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) · Jul 22, 2021

@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 · Jul 22, 2021

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) · Jul 22, 2021

see this:

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.

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.

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.

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 · Jul 22, 2021

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

B4X:

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 · Jul 22, 2021

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.

Android Question Get rowid from SQL database

andredamen

Active Member

josejad

Expert

mc73

Well-Known Member

Andrew (Digitwell)

Well-Known Member

Mahares

Expert

Andrew (Digitwell)

Well-Known Member

andredamen

Active Member

Andrew (Digitwell)

Well-Known Member

Mahares

Expert

andredamen

Active Member

Similar Threads

Android Question Get rowid from SQL database

Active Member

Expert

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Active Member

Well-Known Member

Expert

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency