Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: FireDAC interprets largeint as int



Permlink Replies: 6 - Last Post: Sep 3, 2015 7:14 AM Last Post By: Tom Roberts
Tom Roberts

Posts: 102
Registered: 6/21/05
FireDAC interprets largeint as int
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 2, 2015 8:32 AM
My database has several large tables with single column int64 primary keys. I was reading the sqlite rowid documentation earlier and noticed that if a single column primary key was defined exactly by the word INTEGER (any case) then sqlite used that key in place of the rowid key that it normally creates and such indexes were faster than normal. Knowing that changing the columns to INTEGER wouldn't affect the data (in sqlite at least) I did so in the hope I'd reduce the size of the database and that I'd speed up my queries.

My database size dropped by ~70 MB and all the data was intact. Everything worked as expected in SQLiteExpert but when I tried a few queries in FireDAC I found the 64 bit data being returned as 32 bit integers. No doubt there will be a workaround for this but I'm beginning to wonder if we need a FireDAC wrapper.

PS select ID as 'ID::int64'; returns ID as a largeint rather than an int although I can't see me going through my entire code to implement that.

One other gripe while I'm at it. Even if ID is defined as an int64 in the table FireDAC queries never allow enough column width to display the whole value.

Edited by: Tom Roberts on Sep 2, 2015 8:59 AM to add PS

Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC interprets largeint as int
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 2, 2015 10:50 AM   in response to: Tom Roberts in response to: Tom Roberts
1) What is your C++ Builder and FireDAC versions ?
2) How exactly is defined ID field ? Please provide your table DDL.

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Tom Roberts

Posts: 102
Registered: 6/21/05
Re: FireDAC interprets largeint as int
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 2, 2015 12:08 PM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
1) What is your C++ Builder and FireDAC versions ?
2) How exactly is defined ID field ? Please provide your table DDL.

--
With best regards,
Dmitry Arefiev / FireDAC Architect

Hi Dimitry, I'm using c++ builder xe6 and firedac 20.0.15596.

Try this

CREATE TABLE [MyTbl] (
[ID] INTEGER,
[Str] CHAR(4),
CONSTRAINT [] PRIMARY KEY ([ID]));

insert into MyTbl values ((1<<56)+1,'X');

select * from MyTbl;

Returns 1, 'X' in FDQuery and 72057594037927937, 'X' in SQLiteExpert.
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC interprets largeint as int
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 2, 2015 10:55 PM   in response to: Tom Roberts in response to: Tom Roberts
The problem of Delphi / SQLite is that SQLite is type less (to some degree),
but FireDAC data access core classes require a DB source to be strong typed.
For most applications mapping of INTEGER to ftInteger (32 bit) works correctly
and optimal. Still if you need to get ftLargeInt (64 bit), then you should use
FireDAC data type mapping rules. Add the following to your DFM:
  object FDConnection1: TFDConnection
......
    FormatOptions.AssignedValues = [fvMapRules]
    FormatOptions.OwnMapRules = True
    FormatOptions.MapRules = <
      item
        TypeMask = 'INTEGER'
        TargetDataType = dtInt64
      end>
......
  end


--
With best regards,
Dmitry Arefiev / FireDAC Architect
Tom Roberts

Posts: 102
Registered: 6/21/05
Re: FireDAC interprets largeint as int
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 3, 2015 3:02 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
The problem of Delphi / SQLite is that SQLite is type less (to some degree),
but FireDAC data access core classes require a DB source to be strong typed.
For most applications mapping of INTEGER to ftInteger (32 bit) works correctly
and optimal. Still if you need to get ftLargeInt (64 bit), then you should use
FireDAC data type mapping rules. Add the following to your DFM:
  object FDConnection1: TFDConnection
......
    FormatOptions.AssignedValues = [fvMapRules]
    FormatOptions.OwnMapRules = True
    FormatOptions.MapRules =        item
        TypeMask = 'INTEGER'
        TargetDataType = dtInt64
      end>
......
  end


--
With best regards,
Dmitry Arefiev / FireDAC Architect

I can't get that to work at all Dimitry. I'm only using the one connection

object FormbookConnect: TFDConnection
Params.Strings = (
'Database=D:\SQLiteFormbook\Formbook.db'
'DriverID=SQLite')
FetchOptions.AssignedValues = [evAutoFetchAll]
FormatOptions.AssignedValues = [fvMapRules]
FormatOptions.OwnMapRules = True
FormatOptions.MapRules = <
item
NameMask = 'INTEGER'
TargetDataType = dtInt64
end>
LoginPrompt = False
Left = 324
Top = 88
end
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC interprets largeint as int
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 3, 2015 4:23 AM   in response to: Tom Roberts in response to: Tom Roberts
I checked, sorry, TypeMask was introduced in XE8. Then you should
replace "TypeMask = 'INTEGER'" with "SourceDataType = dtInt32".

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Tom Roberts

Posts: 102
Registered: 6/21/05
Re: FireDAC interprets largeint as int
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 3, 2015 7:14 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
I checked, sorry, TypeMask was introduced in XE8. Then you should
replace "TypeMask = 'INTEGER'" with "SourceDataType = dtInt32".

--
With best regards,
Dmitry Arefiev / FireDAC Architect

That works now Dimitri. I did try setting SourceDataType to dtInt32 but I did so without clearing the NameMask which I now see is related to column names rather than data types.

I'm relieved I don't have to do this for every query and I think I've just learned I can set this in the FDManager and presumably it will cover all connections as well as queries.

Many thanks.
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02