StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQLiteDatabase.execSQL not working as expected for INSERT INTO query
text
Body
copied!<p>I am porting iOS code that works perfectly to the Android OS. I perform a bunch of queries, inserting the results of the queries into a temporary table. When all of the queries are complete I then grab all of the results from the temporary table into a collection of objects of my own creation. I am using a temporary table rather than selecting straight into the collection because I believe it executes more quickly, or at least it does on the iOS side anyways. My problem is that execSQL() is not working as I would expect, here is the code:</p> <pre><code>db.execSQL("CREATE TEMPORARY TABLE SearchResults(Name text);"); db.execSQL("INSERT INTO SearchResults (Name) SELECT Name FROM ProductNames WHERE NameLower MATCH '" + termLowerCase + "*';"); db.execSQL("INSERT INTO SearchResults (Name) SELECT Name FROM BrandNames WHERE NameLower MATCH '" + termLowerCase + "*';"); </code></pre> <p>When I execute this code I only ever get one row from the first execSQL() call with the INSERT. I know there are more than one result in the ProductNames table that should match my term, and I know there are hundreds of rows in the BrandNames table that should match my term. If I change the code to this:</p> <pre><code>Cursor cursor = db.rawQuery("SELECT Name FROM ProductNames WHERE NameLower MATCH '" + termLowerCase + "*'", null); cursor.moveToFirst(); while (!cursor.isAfterLast()) { resultSet.add(cursor.getString(0)); cursor.moveToNext(); } cursor.close(); cursor = db.rawQuery("SELECT Name FROM BrandNames WHERE NameLower MATCH '" + termLowerCase + "*'", null); cursor.moveToFirst(); while (!cursor.isAfterLast()) { resultSet.add(cursor.getString(0)); cursor.moveToNext(); } </code></pre> <p>I get all the results that I'm expecting.</p> <p>Could anyone tell me what I'm doing wrong? Am I using execSQL() in an inappropriate way? I assumed it would simply pass the query onto sqlite3_exec() which is what I use on iOS. If I am using execSQL() inappropriately what are its limitations and what is the fastest alternative to what I want to do? Thanks very much for any help!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload