1

I created a SQLITE3 database in DB Browser for SQLITE and ran CREATE VIRTUAL TABLE recinfo USING fts4(content='Listing', Company) to create FTS4 database. Loaded the database to an android app but unable to run a full text select statement successfully from within the app, so tried ADB in an emulator using the latest versions of Android Studio and SDK. If I go to ADB I can see all relevant tables (inc FTS4 tables) and if I use Select * from recinfo; I get the full list of records displayed. However if I try, select * from recinfo where recinfo match 'ab*'; I get nothing. I have tried many combinations of the prefix full index search with no success. The fts4 table is a single columned text table circa 500 records, to become 90,000 records when fully developed.
I have successfully used SQLITE3 as a standard database but database size has moved me down the FTS4 route which is new to me and I was trying to keep the use simple.
Does Android Studio support full text index prefix searches? Any advice appreciated

rebras
  • 11
  • 5

1 Answers1

0

FTS4 was added in SQLite 3.7.4.

Android guarantee version 3.4.0 according to documentation. Some devices ships with newer version according to this list

Sqlite 3.7.4 available on most devices since Android 3.0 Honeycomb and higher.

You can check actual version using adb shell sqlite3 --version command on emulator.

Or using this code:

Cursor cursor = SQLiteDatabase.openOrCreateDatabase(":memory:", null).rawQuery("select sqlite_version() AS sqlite_version", null);
String sqliteVersion = "";
while(cursor.moveToNext()){
   sqliteVersion += cursor.getString(0);
}
Community
  • 1
  • 1
dasar
  • 5,321
  • 4
  • 24
  • 36
  • Hi, thanks for quick response, I did check version which is sqlite3 3.8.6. and using android studio 1.2.2. I am offline for 14 hrs now , long haul flight but please leave any further info and I will check tomorrow. Has anyone else succeeded in using fts4?. – rebras Jun 15 '15 at 16:30
  • Still struggling to get this to work as a complex query. – rebras Jun 23 '15 at 14:10
  • Maybe you must try FTS3 ? – dasar Jun 23 '15 at 16:35
  • I have tried both FTS3 and FTS4 and both work if you use the insert command but I have 90,000 rows. I think it may be my use of 'content' as I cannot get anything reported from within DB Browser for SQLite having created the FTS3 or FTS4 tables in that application. – rebras Jun 23 '15 at 16:54
  • sorry was going to add, using DB Browser for SQLite also cut out anything to do with android. – rebras Jun 23 '15 at 16:57