sqlite not saving french accents like à as $agrave

Question

When i will save into my database "C'est tout à fait juste.", sqlite will remove the & from &agrave, becouse it can't maybe handle utf-8.

var sql += "INSERT INTO Test(TestId,Text) Values(1, "C'est tout à fait juste.");

I tried for every insert to replace the $agrave with à, but I think there will be a better solution.

Is there a better way to solve this?

Thanks for any help...

UPDATE

This is my code to create my sqlite database:

  function initDb($cordovaSQLite, logService) {
    var defcorrect = $.Deferred();
    try {

        logger = logService;

        db = window.sqlitePlugin.openDatabase({ name: "xxx.db", location: 0 }, successCB, errorCB);

        $cordovaSQLite.execute(db, "CREATE TABLE IF NOT EXISTS Card(CardId UNIQUEIDENTIFIER PRIMARY KEY, CardSetId UNIQUEIDENTIFIER NOT NULL,  FrontText NVARCHAR(4000) NOT NULL, BackText NVARCHAR(4000) NULL, ControlType INT NOT NULL, CardLevel INT NOT NULL, IsDirty bit NOT NULL, ChangedAt INT NOT NULL, Active bit default 'true')").catch(function (err) {
            logService.writeAppError("00000000-0000-0000-0000-000000000000", "Could not init Table Card: ErrorNo: " + startGUIDLog + " ErrorMessage: " + err.message);
            window.location = "#/error/" + startGUIDLog;
        });

        $cordovaSQLite.execute(db, "CREATE UNIQUE INDEX IF NOT EXISTS Card_Index on Card (CardId)").catch(function (err) {
            logService.writeAppError("00000000-0000-0000-0000-000000000000", "Could not init INDEX Card_Index on Card: ErrorNo: " + startGUIDLog + " ErrorMessage: " + err.message);
            window.location = "#/error/" + startGUIDLog;
        });

        defcorrect.resolve(db);

    } catch (err) {
        logService.writeAppError("00000000-0000-0000-0000-000000000000", "Could not init Database: ErrorNo: " + startGUIDLog + " ErrorMessage: " + err.message);
        window.location = "#/error/" + startGUIDLog;
    }
    return defcorrect.promise();
}

And with this code I insert the data

     function saveCard(cardList, userId, db) {
        var defcorrect = $.Deferred();
        truncateTable("Card", db, userId).done(function () {
            if (cardList.length !== 0) {
                var sql = "";
                cardList.forEach(function (card) {
                    sql += "INSERT INTO Card(CardId,CardSetId,FrontText,BackText,ControlType,CardLevel,IsDirty,ChangedAt,Active) VALUES (" + JSON.stringify(card.CardId) + "," + JSON.stringify(card.CardSetId) + "," + JSON.stringify(Card.FrontText) + "," + JSON.stringify(Card.BackText) + "," + card.ControlType + ", " + card.CardLevel + ",'" + card.IsDirty + "'," + card.ChangedAt + ",'" + card.Active + "');";
                });

                var successFn = function (count) {
                    logService.writeAppInfo(userId, "Sync Save Card Successfully imported " + count + " SQL statements to DB");
                    defcorrect.resolve();
                };
                var errorFn = function (error) {
                    logService.writeAppError(userId, "Sync Save Card Error: " + error);
                    $('#statusOverview').hide();
                    $('#syncError').show();
                };
                var progressFn = function (current, total) {
                    $("#statusText").text("Importiere " + current + "/" + total + " Karten");
                };
                cordova.plugins.sqlitePorter.importSqlToDb(db, sql, {
                    successFn: successFn,
                    errorFn: errorFn,
                    progressFn: progressFn,
                    batchInsertSize: 500
                });
            } else {
                defcorrect.resolve();
            }
        });

        return defcorrect.promise();
    }

In the debug mode I can see, that the data comes in the right way like "C'est tout à fait juste." and after insert when i read out this data it comes like this: "C'est toutagrave; fait juste."

AFAIK squlite allows to use UTF8 coding, doesn't it? What's your db's collation? — , Apr 01 '16 at 17:16
You might want to check [question I asked once](http://stackoverflow.com/questions/27955610/php-windows-filename-incorrect-after-upload-%C3%BC-saved-as-%C3%83%C2%BC-etc), basically I had similar problem with filenames but it was mostly due to different encoding of filenames between windows and php (cp-1252 vs utf-8). In the end I went around it saving filenames in db as html encoded characters and decoding them when retrieving. I think in worst case scenario you might edit that code and fit it to your needs, not the perfect solution but so far I haven't found a better one. — , Apr 01 '16 at 17:32
SQLite handles UTF-8 (because that's the only encoding supported). The error is elsewhere. Show your code. — CL., Apr 01 '16 at 18:02
I don't know the collation of my sqlite db. How can I find out? — Dario M., Apr 02 '16 at 13:09

score 0 · Accepted Answer · answered Apr 07 '16 at 11:14

0

For me this solution worked:

var elemfolderName = document.createElement('textarea');
elemfolderName.innerHTML = "C&#39;est tout &agrave; fait juste.";
var  folderName = elemfolderName.value;

And then I save folderName to sqlite3 db...

answered Apr 07 '16 at 11:14

Dario M.

415
7
21

sqlite not saving french accents like à as $agrave

1 Answers1