[Solved]How to use ptruint & ptrint in sqlite3?!?

[cdbc]

Hi
With my little app, that's using sqlite3 db, I have this problem:
1) I store a datetime value converted to a 64bit PtrUInt, in sqlite using:

Code: Pascal  [Select][+][-]

1. Exec.ParamByName('pdate').AsLargeInt:= aMemItem.DateTime.AsInteger;

where the "AsInteger" prop is ptruint.
2) I check in sqlite3 manager app, that the value is very well written, OK.
3) I then retrieve said largeint value like this:

Code: Pascal  [Select][+][-]

1. lMemItem.DateTime.AsInteger:= fDb.Query.FieldByName('date_mt').AsLargeInt;

again, where the "AsInteger" prop is ptruint.
4) However, when I break in the setter "AsInteger" and check the value, I only get a 32bit value covering the first 4 bytes in the ptruint, the last 4 bytes are =0 ?!?
5) So, here's my question: Where the H*ll have my data gone off to???  ...and don't tell that it's them christmas-elves that took it 
6) I specifically use "AsLargeInt" to fetch the value, which returns a LargeInt => int64 => signed 64bit, but enough to last me to year 9999 
7) "ParambyName() puts hte right value in the database, I checked.
I only get (the right) 4 bytes back, where are the other 4?
A little bit of help would be appreciated...

Code: SQL  [Select][+][-]

1.   cretblMemTitles = 'CREATE TABLE memtitles('+
2.                       'id_mt integer primary key, '+
3.                       'date_mt unsigned big int, '+   { ~ affinity = INTEGER }
4.                       'title_mt varchar(512), '+
5.                       'spare_mt varchar(512)'+
6.                     ');';

Pretty simple, huh?!?
edit: the system in question is:
PCLinuxOS 64bit(rolling release) -> KDE5 -> FPC 3.2.2 -> Lazarus 2.2.6
Regards Benny

[kupferstecher]

Why would you use PtrUInt here? You want to have the database entry always 64 bit regardless of the cpu or exe-bitness, right?
Did you try using Int64 instead?

[MarkMLl]

Why would you use PtrUInt here? You want to have the database entry always 64 bit regardless of the cpu or exe-bitness, right?
Did you try using Int64 instead?

No, the U means that it should be a qword.

There's multiple issues here. First, if you (OP) had read the documentation at https://www.freepascal.org/docs-html/current/rtl/system/ptruint.html you would see "PtrUInt is an unsigned integer type which has always the same size as a pointer". And I would point out that you've not told us what platform you're running on.

Second, you will see that that same page claims that a PtrUInt is equivalent to a DWord i.e. 32-bits. Now that might be correct in your case (again I would point out that you've not told us what platform you're running on) but it's not correct in the general case: Marco pointed out in a related context a few days ago that the documentation is prepared for i386 even though this is not stated anywhere.

Now it might be that your program is dropping bits because at some point it's casting a PtrUInt to something smaller. But we can't be sure of that since I would point out that you've not told us what platform you're running on >:-)

Pointer and sizes are odd things with their own set of operations, but in database terms they're analogous to timestamps and intervals respectively. Only use PtrUInt (and possibly PtrInt) where the overriding requirement is for a scalar with the same size as a pointer on whatever platform you're running, in other cases refer to e.g. https://www.freepascal.org/docs-html/current/ref/refsu4.html#x26-26003r2

MarkMLl

[cdbc]

Hi
Thanks for the replies.
@MarkMLl: If you look at my signature, it says loud and clear, that I'm on PCLinuxOS(that only do 64bit) running kde/qt5 with fpc 3.2.2 & laz 2.2.6 
1) You're right, it has to do with pointers & pointersized integers, namely a record of 8 bytes, the pointer to it and a PPtrUInt, so I need the 8 bytes a QWord occupies. (could've used ptrint, but then you would hear Thaddy screaming) 
2) I've left 32bit programming behind, since my OS' no longer support it. Yes, I'm aware that ptruint is 4 bytes on 32bit... longword.
3) Again, see my signature!
4) I'm aware of the different types and their behaviour, it's necessary when working with C-libraries, to know the equivalent types in pascal...
5) Anyway, thanks for the refresher, after sleeping on it, I pulled the sqlite3 units out of the rtl and put them in a tmp location where I can debug them, ...and lo'n behold, I found the solution in 2 steps:
5,1) The creating SQL statement had a mistake:

Code: SQL  [Select][+][-]

1. // the broken one:
2.   cretblMemTitles = 'CREATE TABLE memtitles('+
3.                       'id_mt integer primary key, '+
4.                       'date_mt unsigned big int, '+   { ~ affinity = INTEGER }
5.                       'title_mt varchar(512), '+
6.                       'spare_mt varchar(512)'+
7.                     ');';
8. <- - - ->
9.   cretblMemTitles = 'CREATE TABLE memtitles('+
10.                       'id_mt integer primary key, '+
11.                       'date_mt bigint, '+ //<--- this is correct
12.                       'title_mt varchar(512), '+
13.                       'spare_mt varchar(512)'+
14.                     ');';

5,2) And one has to set a property to true if one wants to handle big integers. Like so:

Code: Pascal  [Select][+][-]

1.  fDb:= TSQLite3Connection.Create(nil);
2.   fDb.AlwaysUseBigint:= true; //<--- this one matters

After correcting these 2 problems, I reverted to using the RTL versions(no debug info) and everything is fine and dandy 
Thank you for your time.
Regards Benny

[MarkMLl]

@MarkMLl: If you look at my signature, it says loud and clear, that I'm on PCLinuxOS(that only do 64bit) running kde/qt5 with fpc 3.2.2 & laz 2.2.6 

How should I know that and why should I care? :-)

More seriously: DO NOT put that sort of thing in a sig. The problem is that as soon as you change your sig it will affect every single message that you've posted in the past, so if somebody comes across one of your (many) nuggets of wisdom they won't know what version of FPC etc. it applied to when you wrote it.

MarkMLl

[cdbc]

Hi
Good point, about the sig...
Regards Benny