Get the right ID using SQLite

Question

I'm doing this by example, this is my first attempt with SQLite

String name = etNewName.getText().toString();
String address = etNewAdress.getText().toString();

ContentValues cv = new ContentValues();
cv.put("name", name);
cv.put("address", address);

long rowID = db.insert("Org", null, cv);
Log.d("MyLog", "row inserted, ID = " + rowID);

updateDB();
adapter.notifyDataSetChanged();

So in log, I got ID=1510 for example, but of course, when I do

int idColIndex = c.getColumnIndex("id");
int id = c.getInt(idColIndex);
Log.d("MyLog"," get id="+id);

it returns 0, it doesn't return me that row ID I got when inserted data, although the initial DB I'm using from a file got it's IDs in 'id' field.

How do I record that index into 'id' field?

Because when you insert the data, it will return the *row number* inside the table. On the other hand, you didn't specify any value for id when you insert the data. If the schema puts 0 as the default value for id, then there is nothing wrong actually. This is just a guess since I don't know the real schema of your DB. — Andrew T., Sep 10 '14 at 08:13
possible duplicate of [how to get rowid of a sqlite fts3 table](http://stackoverflow.com/questions/5604165/how-to-get-rowid-of-a-sqlite-fts3-table) — Andrew T., Sep 10 '14 at 08:16
not a duplicate. But every new data I insert have 0 id ! it should be different. besides, first Log after insertion show me something like id=1523 — Vlad Alexeev, Sep 10 '14 at 08:17
It's actually has got nothing to do with "this question was answered" !! The answer there is totally has nothing to do with my question — Vlad Alexeev, Sep 10 '14 at 08:22
Calm down, I might be wrong because currently it's not really clear. The problem is, your defined "id" is not the same as the "rowid" the method `insert()` returns. And you said that *"(the id) should be different"*, then please post the schema of the DB. — Andrew T., Sep 10 '14 at 08:25
@user2976267 : I'll ask again...why do you need to know (or get) the row id? It's actually rarely of any real use when dealing with databases and datasets returned by queries. — Squonk, Sep 10 '14 at 08:25
Andrew, exactly) I need them to be same. The whole .db is big, i need only one table in which there is an "id" field which i'm needing — Vlad Alexeev, Sep 10 '14 at 08:29
@user2976267 then I'm afraid there is something wrong with the DB schema and you need to set "id" as primary key (refer to [the official guide](http://developer.android.com/training/basics/data-storage/databases.html)). OR, you need to update the "id" manually after inserting the data, but I don't know how to do that. — Andrew T., Sep 10 '14 at 08:37
Yes, I thought of that that i need to edit this entry manually and insert correct id number forcefully. — Vlad Alexeev, Sep 10 '14 at 08:38
@user2976267 : Having a field (such as "id" or any other name) which *MUST* have the same value as the `rowid` is a ridiculous concept for a SQL database - it's simply not necessary to deal with the `rowid` directly. — Squonk, Sep 10 '14 at 08:40

score 0 · Answer 1 · answered Sep 10 '14 at 07:59

0

getColumnIndex converts a column name into a column number. Returning 0 just means that id is the first column in the cursor.

To get the value of some column in the cursor's current row, you have to call getInt():

int id = c.getInt(idColIndex);

answered Sep 10 '14 at 07:59

CL.

173,858
17
217
259

i'm sorry - i missed that one ! I updated question. of course I use int id = c.getInt(idColIndex);, the id is still 0 – Vlad Alexeev Sep 10 '14 at 08:05

score 0 · Answer 2 · answered Sep 10 '14 at 08:02

You need to use below code to get the integer value of ID

int id = c.getInt(c.getColumnIndex("id"));

Explanation: Suppose your query is

SELECT ID, COL1, COL2  FROM MYTABLE;

If the query returns a resultset of say, ID COL1 COL2 1234 1020 2040

Then

c.getColumnIndex("id") ==> 0
c.getColumnIndex("COL1") ==> 1
c.getColumnIndex("COL2") ==> 2

c.getInt(c.getColumnIndex("id"));  ==> 1234
c.getInt(c.getColumnIndex("id"));  ==> 1020
c.getInt(c.getColumnIndex("id"));  ==> 2040

Get the right ID using SQLite

2 Answers2