Last 5 comments
84 years ago
Rafael:  Thank you very much, I was having a huge headache to solve the very same problem!
91 years ago
Ray:  Having the same problem. Very frustrating. Luckily, for some reason my released version worked on the iPad itself, but now I can't get it to run in the simulator. Getting no such table, which I'm guessing is an initialization error. Will continue to investigate.
91 years ago
Jeremy:  FYI, I've just tried it with the SQLite 3.7.0 preview and the same problem occurs.
Also, I'm not using any extra third-party libraries with my SQLite, so the problem isn't your Unicode extension.
91 years ago
Jeremy:  I'm having the same problem with compiling SQLite against iOS 4 for the iPad simulator, but in my case it works fine running on an actual iPad (also works in the iPhone simulator and on an iPod Touch).
Same problem with 3.6.23.1, 3.6.23, and at least back to 3.6.21. Compiling against iOS 3.2 makes it work, though that's not really an option for iPhone (as opposed to iPad) apps.
I have no idea what to do about it or how big a problem it really is...
91 years ago
Pascal:  The problem seems to have deep roots, however there is a solution, see the updated post. :)
The archive
March 2011  (1)
July 2010  (1)
July 2009  (1)
March 2009  (1)
July 2008  (3)
June 2008  (1)
May 2008  (3)
March 2008  (1)
July 2007  (1)
June 2007  (3)
May 2007  (1)
April 2007  (1)
July 2006  (2)
June 2006  (6)

Static SQLite Library with Unicode Support for the iPhone

Saturday, August 8th 2009 - 12:25 • 1 update Tuesday, June 29th 2010 - 00:21
For my Eponyms iPhone App I wanted to bless SQLite with the ability to handle special chars - especially German Umlaute - like they should be handled, which e.g. is treating ä like a when sorting. Currently these Umlauts appear after Z, which is not what we want.

SQLite offers the ability to compile with complete Unicode support, provided by the ICU Project, but that will bloat the library and is not too easy to cross compile to the iPhone platform. This is also what Ioannis Epaminonda thought and he took action and provides a solution including "only" the desired features of the Unicode package, resulting in a much smaller footprint.

Here's how to get this to work for your SQLite-using iPhone App, implemented as a static library:

Create your own SQLite static library

  1. Create a new Project in Xcode, use the iPhone » Library » Cocoa Touch Static Library Template. Pick a good name, the library will be called libYourName.a in the end
  2. Download the SQLite Amalgamation source - that is all of SQLite in one (well, two with the additional header) file. Add the Header and the main file to the currently empty project
  3. Download Ioannis source code files, and add the two files (.h and .c) to your project. Your project should now look like this:
  4. GroupsAndFiles.png
  5. Add SQLITE_CORE and SQLITE_ENABLE_UNICODE preprocessor definitions:
  6. Call up the project's information window, select the "Build" tab and click the gogwheel icon, then select "Add User-Defined Setting". Add the two statements under the keyword GCC_PREPROCESSOR_DEFINITIONS.
    Add_preprocessor_definitions.png
  7. By default the library template will enable Objective C code, but we don't need this. Remove the -ObjC flag from the Project's Other Linker Flags in the project's information window and remove the Foundation Framework from the frameworks:

  8. Remove_ObjC_Flag.png
  9. At the very last, the unicode c file still contains the line #include <sqlite3.h>. This will include the system-wide header file, but we want it to include the header file we've just downloaded. So we add "." to our header search paths (don't check the recursive checkbox when asked):

  10. Add_header_search_path.png
At this point you can hit the Build Button and Xcode will already generate the libX.a static library file which you could already use. But we're taking advantage of Xcode's ability to cross reference projects, this way you'll only have to build your original project and the library will be built using your current build settings; for each build you'll have the latest version of your static library for your current settings, which is quite convenient.

Add the library to your iPhone project

  1. Open your iPhone project in Xcode, the SQLite project still open
  2. Drag and drop the SQLite project into the Groups & Files Section of your iPhone project:

  3. DaD_the_project_file.png
    In the sheet that pops out, do NOT check the copy items into...; we want a reference to that project, not a copy:
    Dont_create_copy.png
  4. Now we tell Xcode to use our own SQLite library, not the general one we've used so far. Remove the SQLite library from Frameworks in case it's still there. No need to add our own there, it's already known to Xcode since we added a reference to our project. But we need to tell Xcode that it must copy this library to the application bundle, otherwise your users will not get that library and the app will of course not run. Our library appears right where we inserted the reference to the library project, you can simply drag it from there down to the Copy Bundle Resources target group. In case Xcode did not do this automatically, also make sure that it appears in the Link Binary With Libraries group.

  5. Copy_and_Link.png
  6. Xcode will use the build settings of the main project also for linked projects, so make sure you add the GCC_PREPROCESSOR_DEFINITIONS also to the settings of your iPhone project (See step 4 of Create your own SQLite static library)
  7. We need to load these Unicode extensions on App launch and unload them before App quit; I've included the corresponding functions in the function that initially opens a database connection and the function that closes it again:
    • On launch: sqlite3_unicode_load();
    • Before quit: sqlite3_unicode_free();
  8. That should be all, compile and Run!

I hope this helps, in case I missed something leave a comment. In case you want to do cross referencing more often, there's a more dedicated way in doing this by adding constants to Xcode and define a shared build directory. See Clint Harris Blog for how to do this.

Update June 29, 2010

As mentioned in the comments, using your own SQLite library on the iPad, built against iOS 4, does not work; SQLite does not initialize the database correctly. Some people have looked into this and at least Evan an me have posted to the sqlite-dev and sqlite-users mailing list; no solution so far.

However there is a workaround - for those using the unicode extension mentioned above, you can still use it:
  • Link your app against Apple-provided libsqlite3.dylib
  • Add » sqlite_unicode.h and » sqlite_unicode.c to your project (I've mirrored the sourcecode here since Ioannis Epaminonda's original blog seems to be gone)
  • Define SQLITE_CORE and SQLITE_ENABLE_UNICODE as preprocessor definitions
  • Build and go

You can then use the extension as before. If you want full FTS3 support, this is also possible, see Evan's blog post for this.
Lorenz at 08.08.2009 23:43

nice work!
Mathias at 20.05.2010 06:30

Hi, I have used this guide in my iPhone project for a long time, since I also depend on german umlauts. However, for the new iPhone SDK 4 it won't work anymore. The libSQLiteUnicode compiles fine, but the program crashes without further message. If I use the bundled sqlite it works fine. Do you have any ideas, what could be the problem?
Pascal at 20.05.2010 09:11

I haven't yet checked out SDK 4. Will do that soon and hopefully find the problem - I'll also be using this custom library for OS 4, so if there is a problem I need to solve it. :)
Mathias at 20.05.2010 17:43

My sequence of calling is the following:
sqlite3_unicode_load()
Mathias at 20.05.2010 17:44

My sequence of calling is the following:
sqlite3_unicode_load() sqlite3_open(file,&db)
It crashes when trying to open the database. Perhaps the problem is related to linking against the libSQLiteUnicode. Some people in the Appe Devforum are reporting problems when linking against a 3rd-party Lib (Llike Flurry)
Pascal at 29.06.2010 00:23

The problem seems to have deep roots, however there is a solution, see the updated post. :)
Jeremy at 06.07.2010 00:45

I'm having the same problem with compiling SQLite against iOS 4 for the iPad simulator, but in my case it works fine running on an actual iPad (also works in the iPhone simulator and on an iPod Touch).
Same problem with 3.6.23.1, 3.6.23, and at least back to 3.6.21. Compiling against iOS 3.2 makes it work, though that's not really an option for iPhone (as opposed to iPad) apps.
I have no idea what to do about it or how big a problem it really is...
Jeremy at 06.07.2010 01:00

FYI, I've just tried it with the SQLite 3.7.0 preview and the same problem occurs.
Also, I'm not using any extra third-party libraries with my SQLite, so the problem isn't your Unicode extension.
Ray at 07.07.2010 19:30

Having the same problem. Very frustrating. Luckily, for some reason my released version worked on the iPad itself, but now I can't get it to run in the simulator. Getting no such table, which I'm guessing is an initialization error. Will continue to investigate.
Comments are disabled