Storing "String" as "TEXT" instead of "VarChar" in SQLite - WP8

Question

I'm storing my Data in local database using SQLite. I've string typed few variables to store. Now, when I store that string variables, it is being stored as "VARCHAR" (as in the picture below).

Picture 1

But I want to store those strings as "TEXT" type in SQLite. (as in the picture below)

Picture 2

The class I'm using to store db is as below:

public class abc
{
    [SQLite.AutoIncrement, SQLite.PrimaryKey]
    public int _id { get; set; }
    public string a { get; set; }
    public string b { get; set; }
    public string c { get; set; }
    public string d { get; set; }
    public string e { get; set; }
    public string f { get; set; }
    public string g { get; set; }
    public DateTime date { get; set; }

}

any help will be appreciated.

Malloy

varchar is TEXT in SQLite so u need not to worry ! – Warily

Answer 1

varchar is TEXT in SQLite so u need not to worry!

Internally the data is always stored as TEXT, so even if you create table with VARCHAR(LEN), SQLite is going to follow the rules of TEXT data type

Warily

Thanks for reply but Its not like that. As i posted the image, there is a difference and that difference makes incompatibility issues with the database file created in other platform like android and ios – Malloy

What incompatibility? – Stasny

@CL see, we have cross platform app for wp, android & iphone. now we are sharing the common DB file made by SQLite in every platform. In the question, Picture 1 is of the DB file created by WP and Picture 2 is of the DB file created by android. now, when the DB file created by WP copied to android device, then the data isn't displaying on that device. that means VarChar(len) datatype creates issue. if we change VarChar(len) to TEXT type manually then it works.. same with iphone app. p.s. sorry for long explanation.. – Malloy

SQLite itself does not distinguish between VARCHAR and TEXT. – Stasny

@KevalLangalia Hi keval, so what was the conclusion? did VARCHAR worked for you or you changed it all to TEXT after all? – Weslee

no.. not actually. I had to change the scenario I was working on. Except that, the problem will arise only if the scenario is of cross platform app, otherwise VARCHAR is TEXT itself. – Malloy

You are wrong. Try to declare some column with varchar, then try to select where yourcol = 'somevalue. It will not work. You need where CAST(yourcol AS text) = 'somevalue' to work – Novanovaculite

Answer 2

Although there is no difference between Text and Varchar data types in SQLite, but if you want to change it the way you want, do this:

If you are using sqlite-net (SQLite.cs & SQLiteAsync.cs) in your WP8.0 app, then go to SQLite.cs class and find the SqlType method (about line 1863). Then you can see if (clrType == typeof(String)) statement. If you prefer Text data type then you can change varchar to text as you like.

Spurn

Answer 3

As to my knowledge, Text type accepts UTF-8 and special chars symbols, etc. Its not the same with type Varchar.

Banky