[SOLVED] SQlite3: Booleans supported under macOS/Windows but not under Linux lazarus / 16-Databases ---------------------------------------------------------------------- Hansaplast · 2020-01-08 14:11 Let me start by saying that I'm not sure if this is Lazarus or FPC related - but maybe somebody here has an insight that could help resolve this issue. I have an application which I compile under macOS, Windows and Linux. The application uses SQLite3 (SQlite3Connection). Under macOS and Windows, I can use "false" and "true" in queries for booleans (even though SQlite does not have a datatype for Boolean). When running the application under Linux however, it recently started failing by stating that it doesn't know the column "false" (or "true). Of course; When using 0/1 instead of the false/true, things work just fine under Linux - just makes the queries harder to read, and I'd have to change all the queries even though they have worked before. I've made sure Lazarus and FPC (from SVN: Lazarus 2.1.0 r62510 FPC 3.3.1) are the exact same version on all 3 platforms. The only difference I could find are the SQLite versions: MacOS: 3.28.0 (default on system) Linux: 3.22.0 (Mint Linux 19 - default on system) Windows: 3.25.2 (distributed with the application) On that note; it has never been an issue in the past and I used to compile the Linux version on Mint 18, an even older version (presuming the SQL library could have been even older). I've been maintaining my application for about 2 years now, using false/true in SQL statements without issue, even under Linux. Does anyone have a suggestion what could be causing this? I've been tinkering with this for 3 days now  ... ---------------------------------------------------------------------- Hansaplast · 2020-01-08 14:17 Never mind, I found the answer to my own question; QuoteBeginning with SQLite version 3.23.0 (2018-04-02), SQLite also recognizes TRUE and FALSE keywords as aliases for integer values 1 and 0, respectively. This provides better compatibility with other SQL implementations. But to retain backwards compatibility, if there are columns named TRUE or FALSE, then the keywords are treated as identifiers referencing those columns, rather than BOOLEAN literals. Source ---------------------------------------------------------------------- dsiders · 2020-01-08 14:24 Quote from: Hansaplast on January 08, 2020, 03:11:24 pm Let me start by saying that I'm not sure if this is Lazarus or FPC related - but maybe somebody here has an insight that could help resolve this issue. I have an application which I compile under macOS, Windows and Linux. The application uses SQLite3 (SQlite3Connection). Under macOS and Windows, I can use "false" and "true" in queries for booleans (even though SQlite does not have a datatype for Boolean). When running the application under Linux however, it recently started failing by stating that it doesn't know the column "false" (or "true). Of course; When using 0/1 instead of the false/true, things work just fine under Linux - just makes the queries harder to read, and I'd have to change all the queries even though they have worked before. I've made sure Lazarus and FPC (from SVN: Lazarus 2.1.0 r62510 FPC 3.3.1) are the exact same version on all 3 platforms. The only difference I could find are the SQLite versions: MacOS: 3.28.0 (default on system) Linux: 3.22.0 (Mint Linux 19 - default on system) Windows: 3.25.2 (distributed with the application) On that note; it has never been an issue in the past and I used to compile the Linux version on Mint 18, an even older version (presuming the SQL library could have been even older). I've been maintaining my application for about 2 years now, using false/true in SQL statements without issue, even under Linux. Does anyone have a suggestion what could be causing this? I've been tinkering with this for 3 days now  ... 3.23 had changes to how TRUE and FALSE are handled as constants and columns in the Sqlite libraries. My recommendation is get the versions syncd on the different platforms. ---------------------------------------------------------------------- Hansaplast · 2020-01-08 14:30 Thanks dsidesr! I guess I had not expected Mint 19 to have an older SQLite3 library than Mint 18. Since none of the Linux users (of my application) complained about the error, with previous versions, I'll continue using at least 3.23 and do a version check when starting to use the database. Seeing that the "boolean" question arises frequently (having searched this forum and the internet), it occurred to me that the SQLite3 unit may have implemented a workaround for those wanting to use "true" or "false". But I was wrong. At least now I know where to look. Thanks for chiming in! ---------------------------------------------------------------------- chrnobel · 2020-01-09 11:30 I have also fought with boolean and SQLite. As far as I see (at least using Zeoslib) 0 and 1 does not work correctly, but N and Y does. Also I have discovered some strange issues with text fields - if there is a text field in a dataset, it is by some odd reasons considered as a blob field, but if the definition of the SQLite table instead says it is a varchar(xx) it works, even though SQLite in it self does not know about varchar. ---------------------------------------------------------------------- Hansaplast · 2020-01-09 11:58 Oh boy ... not what I wanted to hear  I've wasted an entire day now, tinkering with the boolean disaster and it only seems to affect LinuxMint for now as it comes with an ancient SQLite3 version. macOS delivers the right version with the OS, and for Windows I include the SQLite DLL. For Linux, since I'm not an expert, I tried looking into an external library to be distributed with my application, but that went over my head ... (suggestions are welcome) As for the text fields; I do recall from MySQL (back in the day) that when a text gets too large, the database makes it a blob. I'm not 100% sure but I believe 256 Characters was the threshold, and I'm not sure this is the same with SQLite. If I recall correctly, you can resolve this by doing a typecast in your query. Something like this; Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i UPDATE MyTableSET MyColumn = CAST(MyColumn AS TEXT)WHERE typeof(MyColumn) = 'blob'; ---------------------------------------------------------------------- Hansaplast · 2020-01-09 12:00 p.s. I did not (yet) run into the 0/1/Y/N issue yet with SQLite3Connection. ---------------------------------------------------------------------- chrnobel · 2020-01-09 12:12 Quote from: Hansaplast on January 09, 2020, 12:58:35 pmI do recall from MySQL (back in the day) that when a text gets too large, the database makes it a blob. I'm not 100% sure but I believe 256 Characters was the threshold, and I'm not sure this is the same with SQLite. By default SQLite consider everything as text, where MySQL is much more rigid. I have now, to avoid any problems in the future, made it a habit always to define text fields as varchar(xx) when I create a new table (at least for Zeoslib it seems like it reads the definition, and acts from that). I can even be done if one is using DB Browser for SQLite, which by default only can select from Integer, text, blob, real and numeric, but the contents of the dropdown can be overwritten with eg. VARCHAR(100) (remember to press enter, otherwise it is not updated), and then it is written in the create sentence of the table. ---------------------------------------------------------------------- Hansaplast · 2020-01-09 12:24 Thanks for the tip - I did not know that. On that note: I use Valentina Studio (free version) on Mac/Windows/Linux for working with my SQLite databases. Works pretty good and allows to to use either TEXT or VARCHAR. I did notice an ancient post (2011) of you here, concerning static linking of SQLIte. Did you ever find a good way to distribute SQLite with your application under Linux? ---------------------------------------------------------------------- chrnobel · 2020-01-09 13:02 Quote from: Hansaplast on January 09, 2020, 01:24:15 pmDid you ever find a good way to distribute SQLite with your application under Linux? Not really. I ended up with accepting that the user have to install SQLite before using my programs - in most cases it is fairly simple to tell them to do a "sudo apt install sqlite3" or install it from the GUI's program manager. And most of the programming I do now is actually web programming, where I control the server(s) and therefore I have no problems in installing SQLite. ---------------------------------------------------------------------- Hansaplast · 2020-01-09 18:10 Yeah, I've accepted the boolean issue for what it is, and have spend most of the day running through all my code. To make sure I won't run into issues, I've made two constants(one for False and one for True) - just in case I run into the issue you ran into with Zeos,.. I noticed that not all Linux distros allow to upgrade to SQLite3 3.23.00 or newer (like LinuxMint 19.3 - it simply won't allow you to go there without serious work and potential issues on the user's system, which I don't want my users to experience). I've even tried their software repository and apt-get - no success, not even after adding another repository. Thanks for getting back to me and the help is much appreciated!