SQLITE store figures with leading zero

I’m on macOs 10.12.6 with XOJO 2017r1.1.

If I store a figure like “0003” into a string column of a SQLite database the leading zeros disappears.

The record set contains “0003” and after insert i get into the database “3”.

I’ve thought until half an hour ago the content of a string is stored without any change???

I’ve tested this behavior with actual versions of “SQLPro for SQLite” and “Tadpole”: Same result - Input “0003”, content after saving “3”.

How can I keep the leading zeros?

The column should be typed “TEXT”. A “STRING” column has a type affinity of “NUMERIC”. See Datatypes In SQLite Version 3.

i use VARCHAR sometimes

Beware of the application you use to check what is in your Data Base file.

Why ?

Because some of them may check the data you ask and return them the way the programmer thing these data have to be returned.

Example to be clear on what I THINK:
Imagine a Column Name who holds the word Date. If you put something that is not a SQLIte Date, you may be in trouble because the Reader Application may try to understand a SQL Date from the data stored in that field.
You may have declared that Date Field as DATE: the Reader Application may want to get a .DateValue and may return from your field.

No point. The only types that it is worth using with SQLite are those mentioned in the SQLite docs (see Eli Ott’s answer above). You should read it carefully.

If you use VARCHAR it will be treated as TEXT. And any length you supply, as in VARCHAR(200), is completely ignored.

[quote=342233:@Tim Streater]No point. The only types that it is worth using with SQLite are those mentioned in the SQLite docs (see Eli Ott’s answer above). You should read it carefully.

If you use VARCHAR it will be treated as TEXT. And any length you supply, as in VARCHAR(200), is completely ignored.[/quote]

i have no idea the 200 in VARCHAR is ignore… will start using TEXT then

Richard:

Did you checked that information ?

Please read about SQLite Affinity…
any datatype that contains “CHAR” is treated like “text”
however, ANY affinity “can” contain “ANY” data…
not to say that a given application may not over-ride the loose-ness of SQLite datatyping

but if you said

CREATE table xyz(abc Integer);
Insert into xyz values("fred");
select * from xyz;

you get “fred” even though the “datatype” was Integer

[quote=342342:@Dave S]Please read about SQLite Affinity…
any datatype that contains “CHAR” is treated like “text”
however, ANY affinity “can” contain “ANY” data…
not to say that a given application may not over-ride the loose-ness of SQLite datatyping

but if you said

CREATE table xyz(abc Integer);
Insert into xyz values("fred");
select * from xyz;

you get “fred” even though the “datatype” was Integer[/quote]

i know about that… i guess i use the varchar with a number to let me know that the field is a small field with max number is 10 or 255 or 4000.

i assume using CubeSQL as the database server, the VARCHAR thingy still apply