Lazarus
Programming => LCL => Topic started by: emhartner 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
-
I'll never understand people insisting, SQLite uses those DataTypes, nevermind "enforcing" Fieldsizes
https://www.sqlite.org/datatype3.html#affinity
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.
-
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.
-
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
-
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)
-
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.
-
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
Field class to handle ansistring fields
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.
-
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.
-
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
-
I still don't understand why you need to set the Fieldsizes, considering SQLite ignores any sizerestrictions on Table-Columns
-
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)
-
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.
-
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)