Bad Encoding when inserting non-latin data to sqlite database using c#

Question

I'm trying to insert non-latin data into sqlite database using bind variables using System.Data.SQLite. when I inserting the data without bind variables everything is ok:

cmd.CommandText = "INSERT INTO mytab VALUES (/" some non-latin data /")"; cmd.ExecuteNonQuery();

when I try to use bind variables the data is being insert but the encoding is bad and all I can see is stuff like ??? or some weird letters. I read the solutions here and tried to use PRAGMA ENCODING="UTF-8" in the connection string. also I've tried to encode it from "ISO-8859-1" to UTF-8, and also from the default encoding to UTF-8. nothing fix the problem.

here's my code, please tell me what im doing wrong:

public void addItem(List updateProperties) {

try
{
    _Conn.Open();
    SQLiteCommand oCmd = new SQLiteCommand ( "BEGIN", _dtflsConn );
    oCmd.ExecuteNonQuery (  );
    oCmd.Dispose (  );


    foreach (List<string> lst in updateProperties)
    {    
        string table = lst[0];
        lst.RemoveAt(0);

        //building query
        oCmd = new SQLiteCommand ( "INSERT INTO " + table + " VALUES (", _dtflsConn);
        foreach (string str in lst)
            oCmd.CommandText +=   "?,";

        oCmd.CommandText = (oCmd.CommandText.Substring(0, oCmd.CommandText.Length -1)) + ");";
        int i = 0;
        foreach ( string str in lst )
        {
            var val = oCmd.CreateParameter();
            val.ParameterName = i.ToString();

            //Encoding
            byte[] b = Encoding.Default.GetBytes(str);
            b = Encoding.Convert(Encoding.Default, Encoding.UTF8, b);
            val.Value = b;
            oCmd.Parameters.Add(val);
            i++;
        }

        //executing
        oCmd.ExecuteNonQuery();
        oCmd.Dispose();
    }

    oCmd = new SQLiteCommand ( "END", _dtflsConn);
    oCmd.ExecuteNonQuery (  );
    oCmd.Dispose (  );

    _Conn.Close();
}

catch (Exception e)
{
    _Conn.Close();
    logger.LogMessageToFile(e.Message);
}

}

Taha Paksu · Accepted Answer · 2016-03-02 06:22:01Z

2

In a project we inserted non-UTF8 (sqlite default encoding) into a SQLite database. This was done by inserting the text as binary type. First convert the string (in your favorite encoding) to byte array, then add a parameter of type binary with your bytearray as value.

edited Mar 2, 2016 at 6:22

Taha Paksu

15.6k2 gold badges50 silver badges83 bronze badges

answered Mar 22, 2012 at 19:59

user1286858

313 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Taha Paksu Over a year ago

This should be the answer as it solves the problem above.

Alex Lockwood · Accepted Answer · 2012-03-22 22:55:40Z

1

Well this doesn't look like a good idea:

byte[] b = Encoding.Default.GetBytes(str);

Why are you even trying any encoding yourself? Just use the string:

val.Value = str;

That's what you should be doing, as this is text. If that doesn't work, look at the schema to find out how the field is declared, and so on - but I would strongly advise against performing the encoding yourself. That's fundamentally the job of the database/driver combination - it shouldn't be in application code.

edited Mar 22, 2012 at 22:55

Alex Lockwood

83.3k39 gold badges209 silver badges250 bronze badges

answered Nov 1, 2011 at 9:58

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

4 Comments

adi ohaion Over a year ago

thanks for your comment. I tried to do it like this from the start but it doesn't work and I get bad encoding, so I tried to use some of the solutions in stackoverflow. this is one of them. the field is just varchar field, nothing spacial.

Jon Skeet Over a year ago

@adiohaion: What do you mean by "I get bad encoding"? Are you sure it's not just a case of the client not displaying the text properly? The driver really should be handling this correctly.

adi ohaion Over a year ago

When im inserting the data without bind variables its all good. I tried to insert the data using the client and when I select the table the data appear correctly. I can't figure out why this way something goes bad and all I can see is bad encoding. even when I debug it and see the data I getting before sending the INSERT query, I can see all the data well (I mean in Visual Studio debugger).

Jon Skeet Over a year ago

@adiohaion: It does sound like a bug in the driver - which I would have thought others would have noticed. It may be worth asking (ideally with a short but complete program to demonstrate the problem) on a Sqlite-specific mailing list as well...

Collectives™ on Stack Overflow

Bad Encoding when inserting non-latin data to sqlite database using c#

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related