0

I'm working with Android - SQLite. I have code postal number stored as CHAR[5] in SQLite db.

Cursor cursor = db.query( ... )
String code = cursor.getString(cursor.getColumnIndex("code"));

CREATE TABLE codepostal (
    code CHAR( 5 ) PRIMARY KEY NOT NULL UNIQUE,
    name VARCHAR( 70 ) NOT NULL
);
INSERT INTO [codepostal] ([code], [name]) VALUES (01001, 'My_city');

However it always returns with deleting "0" begining character of code postal? (ex: code postal = "01234" but return "1234" instead)

Can someone explain?

CL.
  • 173,858
  • 17
  • 217
  • 259
Anh-Tuan Mai
  • 1,129
  • 19
  • 36
  • 1
    maybe you used `putInt` to the key `code` into your `ContentValues` object? – Leo Sep 02 '15 at 09:22
  • Sorry, I dont get your answer. Can you explain it more precisely? – Anh-Tuan Mai Sep 02 '15 at 09:25
  • 1
    I supposed you used this method http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#insert(java.lang.String, java.lang.String, android.content.ContentValues) to insert "01234" into your database, but apparently you are reading from a database file, aren't you? If not, please show how you inserted "01234" into your database. – Leo Sep 02 '15 at 09:28
  • 1
    Have you checked the insert worked with the leading zero? – Rowland Shaw Sep 02 '15 at 09:28
  • 2
    Possibly, you used an INTEGER datatype for the postal code instead of TEXT. So, when you save `01234`, the INTEGER `1234` is stored, instead of the TEXT `"01234"`. – Phantômaxx Sep 02 '15 at 09:28
  • I created my db by SQLite Studio then export to a sql file, then execute sql file in my Android app. Like: CREATE TABLE codepostal ( code CHAR( 5 ) PRIMARY KEY NOT NULL UNIQUE, name VARCHAR( 70 ) NOT NULL ); INSERT INTO [codepostal] ([code], [name]) VALUES (01001, 'My_city'); So even with CHAR[5], it still inserts my query as Int? Thank you so much. – Anh-Tuan Mai Sep 02 '15 at 09:34
  • 2
    @Anh-TuanMai In your SQL you should have ...VALUES('01001', ...) instead of VALUES(01001, ...). Your are missing quote marks, without them SQLite converts this value to int (so without leading zero) and then saves it as varchar. – Pawel Urban Sep 02 '15 at 09:35
  • 2
    Insert the value as string literals by adding 'quote'. – AC-OpenSource Sep 02 '15 at 09:36
  • 1
    Or you can use bound parameters (those funny **?** placeholders), passing a String array as the values. Android will add the necessary **'** to string values for you. – Phantômaxx Sep 02 '15 at 09:42
  • Since I makes my sql file with sqlite studio. I cant add 'quote'. Maybe I should insert data directly from csv files instead of using sqlite studio exported .sql file. – Anh-Tuan Mai Sep 02 '15 at 09:47

1 Answers1

0

All response LeoLink, Frank N. Stein, Pawel Urban, AC-OpenSource was right. Thanks you guys so much.

Must insert the value as string literals by adding 'quote'

use

VALUES ('01001', 'My_city');

instead of

VALUES (01001, 'My_city');
CL.
  • 173,858
  • 17
  • 217
  • 259
Anh-Tuan Mai
  • 1,129
  • 19
  • 36