[SOLVED] Integer Overflow Problem with TSQLQuery + SQLite3 lazarus / 16-Databases ---------------------------------------------------------------------- Hi im Pascal · 2024-03-30 23:03 Hi all, tl;dr: If you are using a pre-existing SQLite3 database with INTEGER type, use the following workaround to ensure ftLargeInt and size 8 bytes: "SELECT ..., CAST(field as LARGEINT) as field_2 ..." SQLite3: "INTEGER. The value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value."Since I use values, e.g., 500000000000 (five hundred billion), a typical 4-byte integer is not enough which ends at roughly 4 billion.  I assume my database is using 6 or 8 bytes for storage.FPC "Currency - Range: (-922337203685477.5808 .. 922337203685477.5807)"Presumably, this should hold values comfortably up to hundreds of trillions After selecting from the database / table, which I hope I'm doing correctly since I've done it quite a bit in the past, I attempt to read such SQLITE3.INTEGER values by the following means: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i value := Query.FieldByName('value').AsCurrency;Whereby `Query` is a `TSQLQuery` and I presume the FieldByName function is actually from `TDataSet`. If I assign a larger value manually as opposed to getting it from the query, it works as intended. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i value := 564772000000; > My conclusion is that somewhere from selecting from the database to using FieldByName, there is an unintended integer overflow happening e.g. through a 4-byte integer which shouldn't be one. Does anyone have an idea what could be going wrong, or do you need more code examples? I've done quite a bit of wrapping around `TSQLite3Connection`, `TSQLTransaction`, `TSQLQuery` but ultimately there should be nothing there causing the issue (famous last words, I know), since I directly use the TSQLQuery object, e.g.: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Query.Clear// Some logic to prepare query statement, e.g., "SELECT ... FROM ... [WHERE ...] [ORDER BY ...] [LIMIT ...]", followed by assigning ParamByName for each "Where" parameter.Query.OpenQuery.FirstI've also tried adding another column to the table with the values scaled down by 1E3 and then it works again as intended (presumably because it fits in a four byte int). - - -  Edit  - - - Hmm doing some more reading: QuoteNote that the items in the Fields property are not all of type TField: they are descendents of TField, a descendent exists for each native database type. When fetching data from the database, each TDataset will map the native database type for each field in the recordset to a TField descendent class, and will set the DataType property to a value that describes best the type of the data in the database. Several standard TField descendents are availablehttps://www .freepascal.org/~michael/articles/startlaz7/startlaz7.pdf Well... seems like I found the root cause: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i ShowMessage (Fieldtypenames[DB.ResultSet.FieldByName('value').DataType]);ShowMessa ge(IntToStr(DB.ResultSet.FieldByName('value').DataSize));> Yields "Integer" and "4", but I'm not sure why or how to fix this... I guess I'd need "ftLargeInt" and DataSize "8" being applied in the TField / TSQLQuery. - - -  Edit 2  - - - Oh, I did to search quite a bit but just now found also this thread: https://forum.lazarus.freepascal.org/index.php?topic=18681.0 > CAST to LARGEINT / BIGINT is a workaround. ---------------------------------------------------------------------- Thaddy · 2024-03-30 23:48 If you defined your fieldtype to an int64 that is a correct assumption. Afaik, tkInt64 maps to ftLargeInt. ---------------------------------------------------------------------- Hi im Pascal · 2024-03-30 23:58 I don't know if it's possible to define / assign somehow a fieldType prior to executing the select query, so that a different data type is applied. This could be another way to manually "override" the default data type chosen based on SQLite3 INTEGER type. Since while SQLite3 dynamically choses a different byte size for INTEGER, apparently TSQL... components do not detect that from the database and always assume a 4-byte Integer. I suppose the CAST(...) solution achieves the similar thing. ---------------------------------------------------------------------- Thaddy · 2024-03-31 00:04 No what I mean that if you defined your sqlite field as an sqlite integer of size 8, you should map to ftLargeint on the Pascal side. That is all. do not mix up sqlite and pascal types. ---------------------------------------------------------------------- Fibonacci · 2024-03-31 00:18 Quote from: Thaddy on March 31, 2024, 01:04:29 amif you defined your sqlite field as an sqlite integer of size 8 QuoteINTEGER. The value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. You dont choose the size, its done automatically. ---------------------------------------------------------------------- cdbc · 2024-03-31 07:10 Hi Also remember this little trick up TSQLite3Connection's sleeve:Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Db:= TSQLite3Connection.Create(nil);Db.AlwaysUseBigint:= true; { !!!VERY IMPORTANT when using ptrint/ptruint fields in database!!! }...and then use "TField.AsLargeInt" to read and write values. edit: Oh and this is also important, that one uses type BIGINT as the datatype for ptrint/ptruint fields!!!!! ~ affinity = integer, in the defining sql statement. Regards Benny ---------------------------------------------------------------------- Hi im Pascal · 2024-03-31 09:19 Cool, that also works, thanks for the hint! I suppose this will affect any INTEGER field, so if a large number of other INTEGER fields are used, it would increase memory usage, but that's probably bound to be negligible for the largest amount of cases.