Jump to content
giomach

SQLite truncating values?

Recommended Posts

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».

 

ListView.jpg.162747b9e66c8dd0e61ca70d49e5d1a0.jpg

 

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.

Share this post


Link to post

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.

Share this post


Link to post
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.

Share this post


Link to post

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 😞

Share this post


Link to post
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

  • Like 1

Share this post


Link to post
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.

 

Share this post


Link to post
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. :classic_biggrin:

  • Haha 2

Share this post


Link to post
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.

Share this post


Link to post

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

Share this post


Link to post

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.

  • Like 1

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×