SQLite functions - how to use them?

by sirius » Thu, 02 Apr 2009 01:22:44 GMT

Sponsored Links
I read in the documentation for SQLite that there should be some core
functions and aggregate functions available, but I can't seem to use
them in my code.. Can anyone help?

Here's the documentation i read:

There is for example the function count() but how do I use it?
I have tried to use it as stated below, but none of the functions in
the documentation is available that way (i.e when I type "db." the
list that shows in Eclipse documentation doesn't include any of the
functions from the SQLite doc except from execSQL):

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DbAdapter{
 private SQLiteDatabase db;

public Cursor getRow(long rowId) throws SQLException {
                Cursor c =
                        db.query(true, TABLE_NAME, new String[] {
                                        KEY_ROWID + "=" + rowId,
                int numberOfRows = c.count(); //This doen't work!
                if (c != null) {
                return c;


Thankful for any help!

PS. I haven't worked with databases before, so please keep that in
mind when you answer me. DS.


SQLite functions - how to use them?

by Mark Murphy » Thu, 02 Apr 2009 01:28:57 GMT


Those are functions available to SQL statements executed by SQLite.

For example:

SELECT count(*) FROM sometable WHERE column1='somevalue';

The above SQL statement will return a count of the number of rows in
sometable that have a value of 'somevalue' for column1.

SQL is not Java. You can execute SQL statements against SQLite via the
Java rawQuery() method available on SQLiteDatabase.

You are attempting to call SQLite functions as if they were methods on
the SQLiteDatabase object, which generally will not work.

I would recommend you learn SQL outside of Android at the outset. If you
specifically want to focus on SQLite, _The Definitive Guide to SQLite_
(Apress) is a good book.

Once you are comfortable with the notion of executing queries to get
results back, possibly including the functions you linked to, then it
will be easier for you to see how to access databases from inside Android.

Mark Murphy (a Commons Guy)
 http://commonsware.com  |  http://twitter.com/commonsguy 

Warescription: Three Android Books, Plus Updates, $35/Year


Sponsored Links

SQLite functions - how to use them?

by ndefo arnauld » Thu, 02 Apr 2009 03:21:20 GMT

 see the documentation on this link.It cans help you


SQLite functions - how to use them?

by nEx.Software » Thu, 02 Apr 2009 03:34:19 GMT

 I have found that, if you will be doing a lot of data access, compiled
statements will cut the time to complete most operations in half. This
is particularly true of inserts. Used in conjunction with
beginTransaction()/endTransaction(), using compiled statements can
make your data access quite swift. In addition to being faster, I find
it easier to construct compiled statements as I can just use plain SQL
syntax. Of course, getting familiar with the functions themselves is
an important first step.

SQLite functions - how to use them?

by Glen Humphrey » Thu, 02 Apr 2009 08:03:26 GMT

 I've used this and it works for me.



SQLite functions - how to use them?

by Marco Nelissen » Thu, 02 Apr 2009 09:07:40 GMT

 While that works, it will create a Cursor with 'N' rows and then
counts the number of rows. If you instead retrieved "count(*)" as a
column, you'd have a Cursor with 1 row, with a field that contains the
count. The latter is more efficient.

On Wed, Apr 1, 2009 at 5:03 PM, Glen Humphrey


SQLite functions - how to use them?

by sirius » Fri, 03 Apr 2009 16:08:42 GMT

 Thanks all of you! That was really helpful!

Took me some time though to find out how to run the SQL statements
from inside of Java, but this is how I did it:

SQLiteStatement r = db.compileStatement("SELECT count(*) FROM " +
long result = r.simpleQueryForLong();

Other Threads

1. Sensor and Compass APIDemo failing on emulator 1.5

Yes, this is a known bug in the emulator.


2. AdSense for mobile

from http://www.google.com/ads/mobileapps/developerform.html

-- Beta requirements

    1. Min 100K daily pageviews
    2. Free apps only
    3. Android or iPhone app
    4. Ready to implement now, live within 4 weeks
    5. Participation for at least 3 months

Can anyone explain to me WTF "pageviews" mean for APPLICATIONS ?


3. Web development strategies for small screens... Sorry, not Android specific

4. Update message - how to check whether app has run before?

5. startActivity called from non-Activity context - help

6. How to get to android jni library to access /dev/ttyS0