Recent

Author Topic: TSQLQuery with SQLITE3 DB - mysterious results  (Read 1051 times)

wittbo

  • Full Member
  • ***
  • Posts: 150
TSQLQuery with SQLITE3 DB - mysterious results
« on: March 29, 2020, 10:07:31 pm »
I have been working with Lazarus, SQLITE and the query components for many years. But what I have experienced in the last days is very mysterious. The displayed content of columns depends on whether the first row of the result set contains data in these fields (not null) or not (null). If they do not contain any data, all records are displayed normally; however, if I change 2 records in the database so that their data is in the first row, all fields in the affected column that contain data will display binary garbage.

I have a test program with the test database attached; when you translate it, please adjust the database path in the SQLITECONNECTION. Use the button to change the database content accordingly, so you can see the result. Windows users could have to copy the appropriate SQLite drivers sqlite3.def and sqlite3.dll into the program folder.

i have tested the program on Lazarus Mac 2.0.6, LazMac 2.0.2 and LazWin10 2.0.4; it behaves the same on all platforms
I have no idea what I might have done wrong; I use SQLITE in every one of my programs, I have never experienced anything like it.
I hope that someone in the forum has a good hint.
« Last Edit: March 29, 2020, 10:09:18 pm by wittbo »
-wittbo-
MBAir with MacOS 10.14.6 / Lazarus 2.2.4
MacStudio with MacOS 13.0.1 / Lazarus 2.2.4

wittbo

  • Full Member
  • ***
  • Posts: 150
Re: TSQLQuery with SQLITE3 DB - mysterious results
« Reply #1 on: March 30, 2020, 10:56:16 am »
Addition:

It seems to be a problem of the DBGrid in combination  with SQLQuery. If I run the query alone and push the data into a stringgrid, then everything is shown correctly. So this will be my way: skip DBGrid, replace it with a standard stringgrid and fill the grid manually. Not very elegant, but it works.

When there is more time for me, I will report it to bugtracker. I found, if you have this mysterious combination of data in the database at design time, connect dbgrid and query at design time, and then set in the object inspector the active property of the query to true, the whole Lazarus IDE crashes.
-wittbo-
MBAir with MacOS 10.14.6 / Lazarus 2.2.4
MacStudio with MacOS 13.0.1 / Lazarus 2.2.4

chrnobel

  • Sr. Member
  • ****
  • Posts: 283
Re: TSQLQuery with SQLITE3 DB - mysterious results
« Reply #2 on: April 03, 2020, 11:24:45 am »
It migth be OT, but it could be a hint.

I have discovered that when using SQLite (at least with ZeosDB) it is an advantage to make a "real" definition af all database fields the same way as if it was MySQL (this also makes it easier if one want to change database).

By full definition, I mean defining eg. a text field as a VARCHAR(XX), instead of just defining it as a text field - on the surface this seems like nonsense as SQLite only operates with text, not varchar, but I have found out in practise that if not doing this, the dataset for some odd reason interprets a text field as a blob, thus giving weird results.

Another bonus of doing it this way is, that when using a DBGrid, the columns are not a kilometre wide, but adjusts in accordance to the field definition.

So make a full definition like this:

Code: Pascal  [Select][+][-]
  1. CREATE TABLE "customer" (
  2.         `custid`        VARCHAR(20),
  3.         `custcvr`       VARCHAR(10) DEFAULT (''),
  4.         `custgroup`     INTEGER DEFAULT (0),
  5.         `custname`      VARCHAR(20) DEFAULT (''),
  6.         `custphone`     VARCHAR(20) DEFAULT (''),
  7.         `custemail`     VARCHAR(20) DEFAULT (''),
  8.         `custadr1`      VARCHAR(20) DEFAULT (''),
  9.         `custadr2`      VARCHAR(20) DEFAULT (''),
  10.         `custzip`       VARCHAR(10) DEFAULT (''),
  11.         `custcity`      VARCHAR(20) DEFAULT (''),
  12.         `newsletter`    BOOL NOT NULL DEFAULT ('N'),
  13.         `other`         BOOL NOT NULL DEFAULT ('N'),
  14.         `custcontact`   VARCHAR(30) DEFAULT (''),
  15.         PRIMARY KEY(custid)
  16. )

I know it sounds a little absurd, but it could maybe give you a clue.

Awkward

  • Full Member
  • ***
  • Posts: 134
Re: TSQLQuery with SQLITE3 DB - mysterious results
« Reply #3 on: April 03, 2020, 11:38:07 am »
Can that be related of text encoding? i has some problems with release Lazarus version while worked with SQLite when table has fixed text width and UTF8 text (with chars NOT in #32-#127 range)

 

TinyPortal © 2005-2018