Recent

Author Topic: SQLite with integers > 4 byte  (Read 10888 times)

CCRDude

  • Hero Member
  • *****
  • Posts: 596
SQLite with integers > 4 byte
« on: October 24, 2012, 01:16:17 pm »
I've recently started using databases with the included SQLite3 support (units sqldb, sqlite3conn).

sqlite3conn uses sqlite3.dll, which I've used in Delphi with a different wrapper before.

I now have a Trac database, with INTEGER fields that have values that do not fit into a simple 4 byte integer. The Delphi wrapper works fine, and returns the proper data (which is a unix timestamp in microseconds instead of seconds). Lazarus returns something different:

Database: 130442287200000
TSQLQuery.FieldValues: 1843499136

I assume this is broken somewhere in the Int64/UInt64 support.

The FieldType seems to be detected as ftInteger, not ftLargeint. Might be this is the issue, but since it's an external database, I cannot change that, and the Delphi sqlite3.dll wrapper shows that the dll is able to provide correct data anyway.

Workaround attempt 1: I replaced q.FieldValues['time'] with q.FieldByName('time').AsLargeInt, but this did not return the proper value as well.

Workaround attempt 2: I called GetFieldData, which failed the same way.

Workaround attempt 3: I wrote a tracker for TField that would allow me to update the DataType property, and set it to ftLargeint, then called GetFieldData. Failed again. Querying f.AsLargeInt resulted in another number, showing that treatment is a bit different now.


Question: Since this is an external database, I cannot change the database scheme to make those fields largeint instead of int. But since the sqlite3.dll itself is able to handle this, is there any other workaround dealing with Lazarus DB support to get the proper value from the table?

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: SQLite with integers > 4 byte
« Reply #1 on: October 24, 2012, 02:43:32 pm »
How did you define the fields in the database? TSQLite3Connection wants BIGINT or LARGEINT for ftlargeInt fields. The following test program works without any problem:
Code: [Select]
program testlargeint;

{$mode objfpc}{$H+}

uses
  {$IFDEF UNIX}{$IFDEF UseCThreads}
  cthreads,
  {$ENDIF}{$ENDIF}
  Classes ,sqldb, db, sqlite3conn;
var
    SQLite3Connection1: TSQLite3Connection;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;

begin
  SQLite3Connection1:=TSQLite3Connection.Create(nil);
  SQLite3Connection1.DatabaseName:='large.sq3';
  SQLTransaction1:= TSQLTransaction.Create(nil);
  SQLite3Connection1.Transaction:=SQLTransaction1;
  SQLQuery1:= TSQLQuery.Create(nil);
  SQLQuery1.DataBase:=SQLite3Connection1;
  SQLQuery1.SQL.Text:='create table if not exists test (id integer primary key, large LARGEINT,big BIGINT)';
  SQLQuery1.ExecSQL;
  SQLQuery1.SQL.Text:='insert into test values (1, 1304422872000001234,1304422872000001234)';
  SQLQuery1.ExecSQL;
  SQLQuery1.SQL.Text:='select * from test';
  SQLQuery1.Open;
  writeln(SQLQuery1.fields[1].AsLargeInt,',',SQLQuery1.fields[2].AsLargeInt);
end.
 

Tested with FPC 2.7.1 but afaik no reason why it would fail on 2.6.0.

CCRDude

  • Hero Member
  • *****
  • Posts: 596
Re: SQLite with integers > 4 byte
« Reply #2 on: October 24, 2012, 02:57:46 pm »
As I said, I did not define the fields, they're already there. The database is an external one, the standard format created by Trac (a project management and issue tracking system). And as suspected, the field is defined as "integer" there.

Quote
sqlite> .schema ticket
CREATE TABLE ticket (
    id integer PRIMARY KEY,
    type text,
    time integer,
    changetime integer,
    component text,
    severity text,
    priority text,
    owner text,
    reporter text,
    cc text,
    version text,
    milestone text,
    status text,
    resolution text,
    summary text,
    description text,
    keywords text
);
CREATE INDEX ticket_status_idx ON ticket (status);
CREATE INDEX ticket_time_idx ON ticket (time);

As far as the SQLite 3 documentation goes (here), SQLite3 has just the data type "integer" and no dedicated types for other sizes.

According to the documentation, BIGINT and LARGEINT are treated as INT anyway, and "only the size matters".

So while your example works, it should also work if the field is INTEGER but the datasize is 8 (which it is in my case, since other tools can show it).

TSQLite3Connection seems to ignore this independency of aliases and does not treat the data based on its size.

Is there any way I can trick it into believing the field would be defined as BIGINT or LARGEINT? My third workaround went into that direction, but failed.

KpjComp

  • Hero Member
  • *****
  • Posts: 680
Re: SQLite with integers > 4 byte
« Reply #3 on: October 24, 2012, 03:06:13 pm »
Do you have persistent fields on this project!!..

Eg.  If you double click the TSQLQuery do you get a fields designer?.  If so doing AsLargeInt would have no effect, the FieldDef would override this.

CCRDude

  • Hero Member
  • *****
  • Posts: 596
Re: SQLite with integers > 4 byte
« Reply #4 on: October 24, 2012, 03:21:33 pm »
I can't click anything since I'm doing this code-only, in a console application (background: parsing Trac databases and emailing people who've got critical open tickets that have been unattended for a specified span of time - here comes the time field into play).

I've attached the unit I use to retrieve the property, including temporary debug code. And the following is how I access the database:
Code: [Select]
   procedure TTracProgressReminder.ProcessDatabase(AFilename: string);
   var
      db: TSQLite3Connection;
      q: TSQLQuery;
      t: TTracTicket;
      sl: TStringList;
   begin
      db := TSQLite3Connection.Create(nil);
      q := TSQLQuery.Create(nil);
      t := TTracTicket.Create;
      sl := TStringList.Create;
      try
         DB.DatabaseName := AFilename;
         DB.Open;
         DB.Transaction := TSQLTransaction.Create(nil);
         try
            q.DataBase := db;
            q.SQL.Text :=
               'SELECT * FROM ticket WHERE status <> ''closed'' ORDER BY changetime DESC;';
            q.Open;
            try
               while not q.EOF do begin
                  t.FromQuery(q);
                  if (t.ChangedTime < (Now - 2)) // not updated in past two days
                     and (t.Status <> 'closed') // not closed
                     and (Pos('Workshifts', t.Milestone)=0) // not Workshifts related
                     and ((t.Priority='blocker') or (t.Priority='critical') or (t.Priority='major')) // important
                  then begin                   // output
                     sl.Clear;
                     sl.Add('--------------------------------------------------------');
                     t.ToShortSummary(sl);
                     sl.Add('');
                     WriteLn(sl.Text);
                  end;
                  q.Next;
               end;
            finally
               q.Close;
            end;
         finally
            DB.Close;
         end;
      finally
         DB.Free;
         q.Free;
         t.Free;
         WriteLn(sl.Text);
         sl.Free;
      end;
   end;[/quote]

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: SQLite with integers > 4 byte
« Reply #5 on: October 24, 2012, 03:39:35 pm »
Looking here:
http://sqlite.org/datatype3.html
perhaps
CAST (yourfield) AS REAL
may work... which returns an 8 byte floating point value but... well... it might work.

Alternatively, perhaps having a look in the sqlite3connection and underlying code to see if that can easily be modified may be worth it...
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: SQLite with integers > 4 byte
« Reply #6 on: October 24, 2012, 03:48:33 pm »
Quote
According to the documentation, BIGINT and LARGEINT are treated as INT anyway, and "only the size matters".
SQLite is a very loosely typed database. You can put any type in any column. The declaration only gives a hint and is used in the affinity algorithm. See http://www.sqlite.org/faq.html#q3 on storing strings in integer columns. Pascal is a strictly typed language and matching sqlite to pascal is a difficult task. Some assumptions need to be made otherwise sqlite becomes unworkable.
The type to field map is the following:
Code: [Select]
   (n:'INT'; t: ftInteger),
   (n:'LARGEINT'; t:ftlargeInt),
   (n:'BIGINT'; t:ftlargeInt),
   (n:'WORD'; t: ftWord),
   (n:'SMALLINT'; t: ftSmallint),
   (n:'BOOLEAN'; t: ftBoolean),
   (n:'REAL'; t: ftFloat),
   (n:'FLOAT'; t: ftFloat),
   (n:'DOUBLE'; t: ftFloat),
   (n:'TIMESTAMP'; t: ftDateTime),
   (n:'DATETIME'; t: ftDateTime), // MUST be before date
   (n:'DATE'; t: ftDate),
   (n:'TIME'; t: ftTime),
   (n:'CURRENCY'; t: ftCurrency),
   (n:'VARCHAR'; t: ftString),
   (n:'CHAR'; t: ftFixedChar),
   (n:'NUMERIC'; t: ftBCD),
   (n:'DECIMAL'; t: ftBCD),
   (n:'TEXT'; t: ftmemo),
   (n:'CLOB'; t: ftmemo),
   (n:'BLOB'; t: ftBlob),
   (n:'NCHAR'; t: ftFixedWideChar),
   (n:'NVARCHAR'; t: ftWideString),
   (n:'NCLOB'; t: ftWideMemo),
   (n:'VARBINARY'; t: ftVarBytes),
   (n:'BINARY'; t: ftBytes)
This has to be read as "every type name starting with xyz is mapped to n". NCHAR is the same as NCHARACTER. None of this is standard sqlite but allows TSQLite3Connection to work with different field types without doing conversions all the time. Relaxing this and use a LongInt for all integers is a possibility but I think users of wince or other memory limited systems won't be too happy with such a change. If you think this is too strict please do raise a bug report.

Quote
Is there any way I can trick it into believing the field would be defined as BIGINT or LARGEINT? My third workaround went into that direction, but failed.
AFAIK, no. Fielddefs defined before open are destroyed and re-created from above table. Changing fielddefs after open doesn't solve anything because only 32 bits have been retrieved from the database. Hence your weird results.

CCRDude

  • Hero Member
  • *****
  • Posts: 596
Re: SQLite with integers > 4 byte
« Reply #7 on: October 24, 2012, 03:51:13 pm »
Thank quite a lot BigChimp, that's quite what I was looking for :)

Code: [Select]
q.SQL.Text := 'SELECT *, CAST(time AS BIGINT) AS time2, CAST(changetime AS BIGINT) AS changetime2 FROM ticket WHERE status <> ''closed'' ORDER BY changetime DESC;';
Worked right away!

I was already trying to look at the underlying code, but didn't find an option like Delphi's "Use debug DCUs" yet, so I couldn't debug it, and by reading only, I didn't find the issue.

...

Thanks a lot as well ludob for the explanations! I agree that avoiding too many type conversions is a good goal indeed. I thought the matching could be done using the datasize, but of course that might be entry-wise and not field-wise, so on a different level.

With BigChimps method (that is kind of what I meant of tricking it into believing it has a different type) it works, and I can live with a different field name to query indeed :)

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: SQLite with integers > 4 byte
« Reply #8 on: October 24, 2012, 03:54:00 pm »
Looking here:
http://sqlite.org/datatype3.html
perhaps
CAST (yourfield) AS REAL
may work... which returns an 8 byte floating point value but... well... it might work.
Good idea!
Code: [Select]
cast(yourintfield as bigint) does work.


DikSoft

  • Newbie
  • Posts: 2
Re: SQLite with integers > 4 byte
« Reply #9 on: September 07, 2016, 04:44:25 pm »
... does work.
Lazarus 1.6 SQLite 3.13 x64 - not work! Neither throw TSQLQuery not ZSQLQuery. All component are in actual updated state, database is external. ((

Any ideas?

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: SQLite with integers > 4 byte
« Reply #10 on: September 08, 2016, 07:42:00 am »
Can you give more detailed description of your problem + probably attach any source program  / data , which will show error ?

eny

  • Hero Member
  • *****
  • Posts: 1634
Re: SQLite with integers > 4 byte
« Reply #11 on: September 10, 2016, 12:45:02 pm »
... does work.
Lazarus 1.6 SQLite 3.13 x64 - not work! Neither throw TSQLQuery not ZSQLQuery. All component are in actual updated state, database is external. ((

Any ideas?
D%&*mn... New Topic button broken again???
All posts based on: Win10 (Win64); Lazarus 2.0.10 'stable' (x64) unless specified otherwise...

DikSoft

  • Newbie
  • Posts: 2
Re: SQLite with integers > 4 byte
« Reply #12 on: September 23, 2016, 11:06:33 am »
Select Cast( FieldName as BIGINT) as FieldNameToShow - yes. It work now. I can read this value.
But, how to write ??

 

TinyPortal © 2005-2018