Oh and from the same session: sqlite> sqlite> CREATE VIRTUAL TABLE pages USING fts4(title, body); SQL error: no such module: fts4SQL error: near "sqlite": syntax error sqlite>
On Jan 26, 3:15 pm, Robert Hawkey <[email protected]> wrote: > SQlite supports FTS3 and FTS4 (full text searching). I've seen > documentation (don't have it handy) that FTS3 is enabled in the build > of SQLite shipped in the API level I'm using so MATCH is valid. I > know MATCH does work because I do at least get one return value. > Also, when I perform the work via rawQueries I get the full results. > > Rob > > On Jan 26, 2:23 pm, Mark Murphy <[email protected]> wrote: > > > > > > > > > I have never used MATCH in SQLite. The LIKE operator uses %, not *, as > > the wildcard. > > >http://sqlite.org/lang_expr.html > > > On Tue, Jan 24, 2012 at 10:45 AM, Robert Hawkey <[email protected]> wrote: > > > 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 [email protected] > > > To unsubscribe from this group, send email to > > > [email protected] > > > For more options, visit this group at > > >http://groups.google.com/group/android-developers?hl=en > > > -- > > Mark Murphy (a Commons > > Guy)http://commonsware.com|http://github.com/commonsguyhttp://commonsware.com/blog|http://twitter.com/commonsguy > > > Android Training in DC:http://marakana.com/training/android/ -- You received this message because you are subscribed to the Google Groups "Android Developers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/android-developers?hl=en
