Recent

Author Topic: TDBGrid Issues Depending On SQLite Database Type  (Read 687 times)

emhartner

  • Jr. Member
  • **
  • Posts: 52
TDBGrid Issues Depending On SQLite Database Type
« on: September 13, 2022, 04:18:40 am »
I'm seeing inconsistencies in the TDBGrid based on the SQLite data type.  The attached program will create a table with three fields with the fields being one of the following data types:
- VarChar(2000)
- Text(2000)
- Text
- WideChar

The following is the behavior for each type:
VarChar(2000):
Everything works as it should.

Text(2000)
If "Insert Data Upon Creation" is checked, no data is shown when the table is created.
Upon posting a new row, you get EXC_BAD_ACCESS.
Upon putting data in a column in a new row, and then moving to a new column, you get EXC_BAD_ACCESS.

Text
Exactly the same as Text(2000)

WideChar
If "Insert Data Upon Creation" is checked - the test data is shown when the table is created as with VarChar(2000).
Upon posting a new row, you get EXC_BAD_ACCESS.

My database utilizes WideChar and this used to work without issue in Lazraus 1.8.  It also works fine in Delphi 7 and in Objective-C on iOS.

Can anyone tell me how make it work with the other data types I list?

Thank you,
Eric

Zvoni

  • Hero Member
  • *****
  • Posts: 1486
Re: TDBGrid Issues Depending On SQLite Database Type
« Reply #1 on: September 13, 2022, 09:47:03 am »
I'll never understand people insisting, SQLite uses those DataTypes, nevermind "enforcing" Fieldsizes
https://www.sqlite.org/datatype3.html#affinity
Quote
Note that numeric arguments in parentheses that following the type name (ex: "VARCHAR(255)") are ignored by SQLite - SQLite does not impose any length restrictions (other than the large global SQLITE_MAX_LENGTH limit) on the length of strings, BLOBs or numeric values.
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

wp

  • Hero Member
  • *****
  • Posts: 10250
Re: TDBGrid Issues Depending On SQLite Database Type
« Reply #2 on: September 13, 2022, 10:31:10 am »
Why do you create the fields yourself? TDataSet.Open normally does this automatically in case of an existing database. When I remove your call to "CreateFields" the crashes are gone.

emhartner

  • Jr. Member
  • **
  • Posts: 52
Re: TDBGrid Issues Depending On SQLite Database Type
« Reply #3 on: September 13, 2022, 01:11:55 pm »
Why do you create the fields yourself? TDataSet.Open normally does this automatically in case of an existing database. When I remove your call to "CreateFields" the crashes are gone.

Thank you for your reply wp.  Without creating the fields ahead of time, I have two problems:
(1) The column widths are incorrect.
(2) The field sizes are wrong.  Here are examples:
- VarChar(2000) is correct at 2000
- Text(2000) is 0 - treated as memo
- Text is 0 - treated as memo
- WideChar is incorrect at 255

Is there another way to ensure I get the field size I need?

Thank you,
Eric

Zvoni

  • Hero Member
  • *****
  • Posts: 1486
Re: TDBGrid Issues Depending On SQLite Database Type
« Reply #4 on: September 13, 2022, 02:59:54 pm »
Is there another way to ensure I get the field size I need?

Thank you,
Eric
Yes, store those "metadata" in a separate table (think INFORMATION_SCHEMA of MySQL)
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

emhartner

  • Jr. Member
  • **
  • Posts: 52
Re: TDBGrid Issues Depending On SQLite Database Type
« Reply #5 on: September 13, 2022, 11:54:00 pm »
Is there another way to ensure I get the field size I need?

Thank you,
Eric
Yes, store those "metadata" in a separate table (think INFORMATION_SCHEMA of MySQL)

I don't see how this will help tell the DBGrid what the proper sizes of the fields are.  Again - if I create the fields programmatically as I do in the CreateFields procedure, the field sizes and column widths are correct - but I end up getting the EXC_BAD_ACCESS upon posting an insert.  If I don't call the CreateFields, then the field sizes are too small.
« Last Edit: September 14, 2022, 04:57:38 am by emhartner »

Zvoni

  • Hero Member
  • *****
  • Posts: 1486
Re: TDBGrid Issues Depending On SQLite Database Type
« Reply #6 on: September 14, 2022, 10:58:43 am »
I still don't understand, why you're creating the TStringFields (or what you need them for)
https://lazarus-ccr.sourceforge.io/docs/fcl/db/tstringfield.html
Quote
Field class to handle ansistring fields
Quote
TStringField is the class used whenever a dataset has to handle a string field type (data type ftString). This class overrides some of the standard TField methods to handle string data, and introduces some properties that are only pertinent for data fields of string type. It should never be necessary to create an instance of TStringField manually, a field of this class will be instantiated automatically for each string field when a dataset is opened.
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

wp

  • Hero Member
  • *****
  • Posts: 10250
Re: TDBGrid Issues Depending On SQLite Database Type
« Reply #7 on: September 14, 2022, 11:26:31 am »
I don't see how this will help tell the DBGrid what the proper sizes of the fields are.  Again - if I create the fields programmatically as I do in the CreateFields procedure, the field sizes and column widths are correct - but I end up getting the EXC_BAD_ACCESS upon posting an insert.  If I don't call the CreateFields, then the field sizes are too small.
When your concern is the column width in the DBGrid, you could use DBGrid.Columns[colum_index].Width := some_pixel_value, or SQLQuery1.Fields[field_index].DisplayWidth := some_character_count.

rvk

  • Hero Member
  • *****
  • Posts: 5007
Re: TDBGrid Issues Depending On SQLite Database Type
« Reply #8 on: September 14, 2022, 11:31:01 am »
And if you get a (MEMO) in the TDBGrid field when you use TEXT you can change that in the DBGridOnGetText() event or you can set dgDisplayMemoText in the options of the TDBGrid.

See https://stackoverflow.com/questions/4809298/lazarus-dbgrid-showing-memo-as-the-value-of-string-fields-in-sqlite-3

Zvoni

  • Hero Member
  • *****
  • Posts: 1486
Re: TDBGrid Issues Depending On SQLite Database Type
« Reply #9 on: September 14, 2022, 12:31:15 pm »
I still don't understand why you need to set the Fieldsizes, considering SQLite ignores any sizerestrictions on Table-Columns
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Zvoni

  • Hero Member
  • *****
  • Posts: 1486
Re: TDBGrid Issues Depending On SQLite Database Type
« Reply #10 on: September 14, 2022, 12:35:59 pm »
When your concern is the column width in the DBGrid, you could use DBGrid.Columns[colum_index].Width := some_pixel_value, or SQLQuery1.Fields[field_index].DisplayWidth := some_character_count.
And that's why i said to store those "metadata" in a separate table (aka INFORMATION_SCHEMA)
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

emhartner

  • Jr. Member
  • **
  • Posts: 52
Re: TDBGrid Issues Depending On SQLite Database Type
« Reply #11 on: September 14, 2022, 07:20:41 pm »
I still don't understand why you need to set the Fieldsizes, considering SQLite ignores any sizerestrictions on Table-Columns

I'm fully aware that SQLite ignores the size restrictions in table columns.  But if you look at what I said yesterday, they are being created incorrectly if I don't manually create them:

(2) The field sizes are wrong.  Here are examples:
- VarChar(2000) is correct at 2000
- Text(2000) is 0 - treated as memo
- Text is 0 - treated as memo
- WideChar is incorrect at 255

I'm going to try rvk's suggestion later today.

Zvoni

  • Hero Member
  • *****
  • Posts: 1486
Re: TDBGrid Issues Depending On SQLite Database Type
« Reply #12 on: September 15, 2022, 08:14:17 am »
Then go the other way round.
As far as i understood it, the StringFields are actually created automatically based on the underlying Query.
Execute the Query, so that it creates everything, and THEN SET Fieldsize and whatever else (like wp told you)
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018