SQLite truncating values?

[giomach 1]

Using SQLiteTable3 in Delphi XE update 1 under Windows 10.  I'm a complete novice with SQLite.

 

I have set up a database with a table called FormSenses with the first two fields called Form and Sense.  I can load this table into a Delphi ListView and display it.  I show a section of the ListView in the attachment.

There are five records with Form = 'sean' and they have Sense values of «2», «3.1», «3.2», «5» and «6».

 

 

The following code however only yields four records, with Sense values of «2», «3», «5» and «6»:

 

     sltb1 := sldb.GetTable ('SELECT Form, Sense FROM FormSenses where Form = ''sean''');
     for i := 0 to sltb1.Count-1 do
       begin
       ShowMessage (sltb1.FieldAsString (1));
       sltb1.next
       end;

Why are the values of Sense being truncated like this?

 

In other records, «2.8.4» is truncated to «2.8»,   «2.1.11» is truncated to «2»  but values like «2.3a» are not truncated.

 

Thanks for your help.

[Stano 143]

I do not know and do not use SQLite. I know one thing. All values are always stored in the DB as string / VarChar. Remember that and look for a mistake here.
Defining the field type is only to make your work easier. As far as I know, this will not ensure that you write incorrect data to table.
When I found SQLite and found how complicated it was to work with dates, I quickly rejected it.

[aehimself 396]

4 hours ago, Stano said:

When I found SQLite and found how complicated it was to work with dates, I quickly rejected it.

Use UNIX timestamp and convert in the application. SQLite will be more than happy to store that number for you.

Lately I'm even storing dates as UNIX timestamps in local settings files too.

 

It's really convenient.

[giomach 1]

Problem solved.  I had two copies of the database in different directories, and the copy being accessed by this program was out of date.  Ah well 😞

[Fr0sT.Brutal 900]

On 1/21/2022 at 10:19 PM, aehimself said:

Use UNIX timestamp and convert in the application. SQLite will be more than happy to store that number for you.

Lately I'm even storing dates as UNIX timestamps in local settings files too.

And don't forget to make it 64-bit otherwise rollover will bite very hard

[aehimself 396]

13 minutes ago, Fr0sT.Brutal said:

And don't forget to make it 64-bit otherwise rollover will bite very hard

I started to use databases after the 32bit UNIX timestamp panic was already all over the Internet. Therefore I quickly learned to define all UNIX timestamps as UInt64s 🙂

Nonetheless very true.

 

From Wiki if someone is interested:

Quote

At 03:14:08 UTC on Tuesday, 19 January 2038, 32-bit versions of the Unix timestamp will cease to work, as it will overflow the largest value that can be held in a signed 32-bit number (7FFFFFFF16 or 2147483647). Before this moment, software using 32-bit time stamps will need to adopt a new convention for time stamps, and file formats using 32-bit time stamps will need to be changed to support larger time stamps or a different epoch. If unchanged, the next second will be incorrectly interpreted as 20:45:52 Friday 13 December 1901 UTC. This is referred to as the Year 2038 problem.

Quote

At 06:28:15 UTC on Sunday, 7 February 2106, Unix time will reach FFFFFFFF16 or 4294967295 seconds, which, for systems that hold the time on 32-bit unsigned integers, is the maximum attainable. For some of these systems, the next second will be incorrectly interpreted as 00:00:00 Thursday 1 January 1970 UTC. Other systems may experience an overflow error with unpredictable outcomes.

 

[Joseph MItzen 251]

16 hours ago, aehimself said:

herefore I quickly learned to define all UNIX timestamps as UInt64s

Bah; now you've just replaced your Year 2038 Problem with a Year 292,277,026,596 Problem.

[Joseph MItzen 251]

On 1/21/2022 at 10:07 AM, Stano said:

When I found SQLite and found how complicated it was to work with dates, I quickly rejected it.

Is it really complicated? SQLite can store datetimes as ISO8601 (string) , Julian day numbers (real), or Unix time (integer). It has functions for working with all three of these types. Most SQLite drivers I've seen in various languages automatically convert between one of these and the language's own datetime type, and some offer the user the choice of storing the native datetime in any of three types.

 

For those who are interested, SQLite very recently added static type support, which may be of use to those for whom this was a dealbreaker.

[Stano 143]

For me, a layman, yes. The argument was made: why do you want to solve something that other DBs have solved? That decided.

[aehimself 396]

As far as I am aware, SQLite was designed to be a small, "engineless", flatfile data storage which can be queried just like any RDBMS. It never meant to have all the functionality of those, it simply provided an alternate, standardized way to store, and quickly access information.

Therefore, limitations. Quite few, and easily workaround-able, imo, though.

[John Terwiske 4]

On 1/21/2022 at 10:07 AM, Stano said:

All values are always stored in the DB as string / VarChar. 

That isn't true.  https://www.sqlite.org/datatype3.html

 

SQLite's storage class idea is a different.  That might be what you mean when you speak of string and varchar?