Hi everyone,
I have an app I wrote for the iOS that makes extremely heavy use of a
large database, I am now porting that app to the Android platform.
I have a great deal of operations that follow this pattern:
1 db.execSQL("CREATE TEMPORARY TABLE SearchResults(Name text);");
2 db.execSQL("INSERT INTO SearchResults (Name) SELECT Name FROM
ProductNames WHERE NameLower MATCH '" + term + "*';");
3 db.execSQL("INSERT INTO SearchResults (Name) SELECT Name FROM
BrandNames WHERE NameLower MATCH '" + term + "*';");
...
Cursor cursor = db.rawQuery("SELECT Name FROM SearchResults" +
myCount, null);
cursor.moveToFirst();
if (!cursor.isAfterLast())
{
Debug.log("DB", "- Adding: " + cursor.getString(0));
resultSet.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
On iOS using the SQLite3 C API and sqlite3_exec() all of these
statements work perfectly fine. However, on Android they are
exhibiting strange behaviour. I seem to only ever gets one row, it
seems from the very first INSERT (the line that starts with 2 above).
My goal here is to wrap all of the above commands in a begin and end
transaction so that I can prevent multiple transactions from being
created, also I use a temporary in memory table rather than a
rawQuery() with just the selects because that prevents the bridge from
the database layer to the Java layer from happening until the very end
which seems to result in much better performance.
When I rewrite the above logic to look like this:
Cursor cursor = db.rawQuery("SELECT Name FROM Table1 WHERE NameLower
MATCH '" + term + "*'", null);
cursor.moveToFirst();
while (!cursor.isAfterLast())
{
resultSet.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
cursor = db.rawQuery("SELECT Name FROM Table2 WHERE NameLower MATCH '"
+ term + "*'", null);
cursor.moveToFirst();
while (!cursor.isAfterLast())
{
resultSet.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
...
It works perfectly returning all the proper results, however this is
extremely slow.
Could anyone explain to me why the execSQL() calls above would not
work as I expect them too (and how they work on iOS)?
Thanks!
Rob
--
You received this message because you are subscribed to the Google
Groups "Android Developers" group.
To post to this group, send email to android-developers@googlegroups.com
To unsubscribe from this group, send email to
android-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/android-developers?hl=en
