Two specific problems

  • e_jim

    Hello. First off, congratulations on a great application! I've been using SQLite database browser a lot lately, as I am working on a project which uses SQLite as an embedded database. So I'm doing much of the testing and debugging of the database files with this browser.

    So far I've found two problems. The first is related to international characters. If I insert fields containing spanish characters (    , , etc.) from my application or from the sqlite.exe command line utility provided by sqlite, they are shown as squares (garbage) in the browser. Likewise, if I insert those characters in the browser, they show up as garbage on my application. Any ideas?

    The second problem is that so far I've been unable to import CSV files into the browser. Importing SQL dump files almost always hangs the browser (perhaps I should make them transactional?)

    Anyways, the SQL database browser has worked great besides that, thanks a lot for a free, well done product.

    • Jens Miltner
      Jens Miltner

      Are you talking about sqlitebrowser 1.2 or earlier? sqlitebrowser 1.2 uses sqlite version 3, which has unicode support and thus should handle international characters correctly, whereas the earlier versions essentially were agnostic to character encodings.

      Regarding sqlitebrowser 1.2, I can only comment that international characters display correctly on Mac OS X... maybe someone with Windows experience can comment?


      • I am seeing some strange in Linux with version 1.2 and international characters as well. Need to examine it further to understand exactly what is happening.

        Jens: according to the SQLite docs data stored as UTF16 will be kept in the host byte order. So in theory this should hurt the portability of the db across different platforms, right?

        Maybe we should use UTF16 for OSX, you probably have a reason for implementing it in this way. But for my other projects that use SQLite I am using UTF8 and have not run into these issues with accented characters.

        This is something we should research when we have more time, and maybe add a preference to the browser (use UTF8 or 16). It is a non-trivial change, but doable imo.

        I am quite busy with other work that also deals with SQLite codebase these days, will try to find some time to research the implications of the different UTF schemes in each platform, and how they interact with the Unicode conversion functions in Qt.

    • I confirmed the problems with encoding, and strangely even in OSX. I tried adding data via the browser interface with international (accented) characters. It looks like it is stored ok, but it was actually just the cache: as soon as you reload the view you would see that the codes were not right. I guess Jens was not viewing this because he probably populates data from another application directly, and not via the browser interface.

      Please check the new version, 1.2.1, just posted. Among other small changes (proper support for integer primary key, etc) I changed the code to use UTF8 across the board, and make the conversions using Qt's internal UTF8 routines. I can now take accented characters correctly to/from the browser on all architectures, including PPC and i386 (linux, win and osx tested.)

      I will research the possibility of re-adding UTF16 storage as an option in the near future, as soon as the next official version of SQLite is released. But in the meantime the critical CVS changes from the past month that were commited to the main SQLite tree were merged in the 1.2.1 release, so all known SQLite issues should have been corrected..

      • Jens Miltner
        Jens Miltner

        > I will research the possibility of re-adding UTF16 storage as an option in the near future

        I wouldn't spend too much time on this, since apparently, internally, sqlite uses UTF-8 as well, so using the UTF-16 APIs will only cause sqlite to do it's internal conversion again, going to and from UTF-8...

        It'd still be interesting to find out what went wrong when using UTF-16, though :-/

        • Jens Miltner
          Jens Miltner

          Following up my own post - I think I've found the problem:

          in sqlitedb.cpp, DBBrowserDB::updateRecord, the update query's column value is constructed using the following code:

             char * formSQL = sqlite3_mprintf("%Q",static_cast<const char*>(wtext.utf8()));  
              if (formSQL) sqlite3_free(formSQL);

          and then, the query is executed as

          if (SQLITE_OK==sqlite3_exec(_db,statement.utf8(),

          I assume the "statement.append(formSQL);" will interpret the character pointer as a system encoded string (i.e. usually Latin encoded), so the UTF-8 characters are stuffed into the query string 'as is'.
          Afterwards, the entire string is converted to UTF-8 when executing the query and the already UTF-8 encoded value string is encoded again.

          The fix would be something like


          Where the fictitious function QString::fromUTF8 would take a const char* string in UTF-8 representation and construct a QString out of it.
          (Not being a Qt expert, I'm not sure which function would provide this functionality...)

          BTW: a few lines below,

          (*cv) = wtext.latin1();

          IMHO also doesn't really make sense, since (*cv) is already a QString, so a plain

          (*cv) = wtext

          should do?