CODEWITHSUNDEEP
sqlandroidsqlite
Codevalley
Codevalley

Reputation: 4651

SQLite3 storing String which contains only numbers

I have to run a very simple query like this in SQLite3.

INSERT OR REPLACE INTO tblPhoneList(Phone, PeopleId, StorageType) VALUES('+91912345789', '1','1');

But whatever I do, the number is stored as 91912345789. The + prefix is ignored. The field Phone is a String. Why does it ignore the + prefix? Is there anyway to avoid this?

P.S. This code is run inside Android

EDIT: This is the schema of the table

CREATE TABLE tblPhoneList(Phone STRING PRIMARY KEY ON CONFLICT REPLACE, StorageT
ype INTEGER, PeopleId INTEGER, FOREIGN KEY(PeopleId) references tblPeople(id));

Upvotes: 1

Views: 4028

Answers (5)

Codevalley
Codevalley

Reputation: 4651

Thanks for all the replies. I changed the type String (yes, String type IS supported) to Varchar(32) and this error magically goes off. Looks like String type is poorly supported in here.

Upvotes: 0

ax.
ax.

Reputation: 60217

<UPDATE> from the manual:

the declared type of "STRING" has an affinity of NUMERIC, not TEXT

so please change the type of this column to TEXT, and you should be done. </UPDATE END>

what is the type of your Phone column (try PRAGMA table_info (tblPhoneList))?. if it is TEXT (or BLOB), you shouldn't have any problems - just tried this here. if it is INTEGER or REAL, you should convert it to TEXT - you cannot expect an INTEGER or REAL column to store the + string.

for all the details, see Datatypes In SQLite Version 3.

Upvotes: 9

Praveen
Praveen

Reputation: 91205

try this:

INSERT OR REPLACE INTO tblPhoneList(Phone, PeopleId, StorageType) VALUES('\+91912345789', '1','1');

Upvotes: 0

Husky110
Husky110

Reputation: 741

It's text! SQLite only supports Integer, Text, Real and BLOB.

Upvotes: 2

Zoozy
Zoozy

Reputation: 1325

the field Phone in your DB cannot be a String, you'd better check it, TEXT or VARCHAR may work. Good Luck!

Upvotes: 1

Related Questions