Yes you would be right if I was using MATCH for the SELECT from the temporary SearchResults table; however, I'm not doing that. I just select all the rows from that table as it is not an FTS3 table. Yes you're also correct, neither iOS nor Android seem to support FTS4 tables at the moment. My tables are FTS3 tables.
I suspect it must have something to do with the internal implementation of execSQL(). It must not truly be a 1:1 mapping of the C sqlite3_exec() function in the SQLite3 library, I suspect they're doing something else. I suspect this because the documentation says not to include multiple statements separated with ;'s when calling execSQL(), whereas you can do that with sqlite3_exec(). Rob On Jan 26, 6:52 pm, John <jsp...@gmail.com> wrote: > 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 <rhaw...@gmail.com> 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 <mmur...@commonsware.com> 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 <rhaw...@gmail.com> 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 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 > > > > -- > > > 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 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
