2

I want to get the number of NOT NULL records from my SQLite database. Since I'm using autoincrement, the last inserted row won't give me the real number of records in the table as even if I delete any middle record, it'll insert at the position higher than the last inserted record.

The insert statement returns me the number of last inserted row, but I want this value on the fly.

halfer
  • 19,824
  • 17
  • 99
  • 186
neha
  • 6,327
  • 12
  • 46
  • 78

2 Answers2

0

Doing a count before on the table should work. Simply query for the id column with the where check of NOT NULL and on the returned cursor just call the getCount()

Just to be sure: You should never ever, really never ever, manipulate the auto increment in a productive database. If you delete a record, than the "gap" should stay there. It has no impact on performance or anything else. If you insert a new record in the gap, you can create a lot of trouble...

WarrenFaith
  • 57,492
  • 25
  • 134
  • 150
  • 2
    Doing a COUNT() query is probably more efficient, since the Cursor will actually be populated with every single row that is returned. – EboMike Dec 09 '10 at 07:25
  • The cursor should only be populated if you loop over the results, right? Anyway, I didn't have in mind, that even selecting id only could result in a huge amount. So your right. +1 – WarrenFaith Dec 09 '10 at 07:28
  • Yep, not on Android. A query returns a fully populated Cursor. – EboMike Dec 09 '10 at 07:36
0

So you just want to find the number of rows? (There no such thing as a "null record" as far as I'm aware.)

Can you not just do

select count(1) from YourTableName

or

select count(*) from YourTableName

? (Some databases are faster using one form or other... I don't know about sqlite.)

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194