Android sqlite query with a WITH clause?

Question

On Android, is there any way that I can execute a raw android.database.sqlite query that starts with a WITH clause such as: WITH one(x) AS (SELECT 1) SELECT x FROM one;?

The following fragment gives me a syntax error message (in my main activity code):

    AlertDialog.Builder b = new AlertDialog.Builder(this);
    AlertDialog ad = b.create();

    SQLiteDatabase db;
    Cursor c;

    try {
        db = this.openOrCreateDatabase("test.db", 0, null);
    } catch (Exception e) {
        ad.setMessage("Could not open database: " + e.toString());
        ad.show();
        return;
    }

    try {
        c = db.rawQuery("WITH one(x) AS (SELECT 1) SELECT x FROM one;", new String[0]);
    } catch (Exception e) {
        ad.setMessage("Could not execute query: " + e.toString());
        ad.show();
        return;
    }

    if (c == null)
        ad.setMessage("Got null query result");
    else
        ad.setMessage("Got valid query result");

    ad.show();

It works if I replace the db.rawQuery statement with:

    c = db.rawQuery("SELECT 1;", new String[0]);

UPDATE 1 (edited): The same WITH one(x) AS (SELECT 1) SELECT x FROM one; statement works for me in the following:

sqlite3 CLI
within a Web SQL transaction on ~~Android and~~ iOS (iOS 9.2, not 8.0)
in the iOS version of https://github.com/litehelpers/Cordova-sqlite-storage (iOS 9.2, not 8.0).

You have named your column "1" so the "SELECT x FROM one" will fail because there is no column named X — CathalMF, Apr 10 '16 at 16:56
"SELECT 1" returns the results of executing the "1" expression, which is simply 1. In the query, the "x" means return the results of the one(x) function (with no value specified for "x" which is OK in this case). For reference: https://www.sqlite.org/lang_with.html — brodybits, Apr 10 '16 at 17:24
This WITH ... SELECT statement seems to work on 3.8.5 (and later), _not_ on 3.7.x (or earlier). I will next try it with SQLCipher 3.4.0 for Android, which is based on SQLite 3.11.0. — brodybits, Apr 10 '16 at 18:22

score 0 · Accepted Answer · edited May 23 '17 at 12:09

The built-in Android sqlite database may be too old to support this query. The following Android sqlite database libraries will support this query (tested):

In addition, some newer versions of Android may have updated sqlite. For reference:

score 0 · Answer 2 · answered Feb 23 '17 at 04:48

0

The WITH syntax were introduced only recently in sqlite version 3.8.3. https://www.sqlite.org/lang_with.html

So you check version sqlite using

answered Feb 23 '17 at 04:48

Huỳnh Ngọc Bang

1,572
1
19
22

Android sqlite query with a WITH clause?

2 Answers2