Problem with TSQLQuery

[Zvoni]

i take it "comment" in tr is your TEXT-Field (all others don't make sense)

As a test: Check what Datatype your Field receives here: ftString or ftMemo

Code: SQL  [Select][+][-]

1. SELECT
2.     tr.id,
3.     tr.req_date,
4.     tr.customer,
5.     tr.trans_type,
6.     tr.vehicle_type,
7.     TRIM(CAST(tr.comment AS VARCHAR(65535))) AS comment,  -- THIS ONE
8.     tr.vehicle_id,
9.     sv.reg_number AS vcl_reg_number,
10.     sv.fuel_consumption AS vcl_cons,
11.     svt.name AS vcl_type_name,
12.     tr.start_dtime,
13.     tr.finish_dtime,
14.     tr.driver_id,
15.     se.fname AS dr_fname,
16.     se.surname AS dr_surname,
17. ....

[rvk]

There are least 2 bugs in TSQLQuery regarding this topic. Both have to do with the TCustomSQLQuery.RefreshRecord procedure.

1)
When you have a TDataset.Insert on a ftAutoInc field, AND you set the field-value yourself, AND there is a RefreshSQL statement, it will always crap out with the message that there is no result set ("Refresh SQL resulted in empty result set").

You could argue that that is normal behavior but then the ftAutoInc field should have been set to readonly (it is in Delphi/IBX I think).
So... consider the ftAutoInc field as non-writable, if you have a RefeshSQL statement.

2)
Second problem is the TEXT field.

There are some problems in TCustomSQLQuery.RefreshRecord when dealing with memo-fields (TBlobField).
With the refresh there is always a TCustomBufDataset.CreateBlobStream done (to write the refreshed record to the buffer).
For assigning a TStringField this is no problem, there is no check if the TDataset.State is in [dsEdit,dsInsert].
For a TBlobField there is a check (in CreateBlobStream). So a TMemoField can never be "refreshed" if the TDataset.State is not in [dsEdit,dsInsert] (which it isn't after posting and refreshing the record buffer).

Conclusion... don't have TSQLQuery.RefreshSQL set when dealing with TEXT fields.

[sch61]

You can leave refreshsql empty or set option sqoRefreshUsingSelect.

Thank you. I did just that.

[sch61]

i take it "comment" in tr is your TEXT-Field (all others don't make sense)

As a test: Check what Datatype your Field receives here: ftString or ftMemo

Code: SQL  [Select][+][-]

1. SELECT
2.     ....
3.     TRIM(CAST(tr.comment AS VARCHAR(65535))) AS comment,  -- THIS ONE
4. ....
5.  

ftMemo

[sch61]

1)
The id field is set as ReadOnly and excluded from InsertSQL and UpdateSQL.

2)
Thank you for clarifying the details.

[Zvoni]

i take it "comment" in tr is your TEXT-Field (all others don't make sense)

As a test: Check what Datatype your Field receives here: ftString or ftMemo

Code: SQL  [Select][+][-]

1. SELECT
2.     ....
3.     TRIM(CAST(tr.comment AS VARCHAR(65535))) AS comment,  -- THIS ONE
4. ....
5.  

ftMemo

Crap.
Forgot that sqldb has problems interpreting the Metadata correctly when there is a CAST in the SELECT

Ah, well....
Use VARCHAR(65535) instead of TEXT for DataType in the Table.
Do your SELECT with a TRIM(tr.comment) as comment, and you will (should!) get ftString

[sch61]

Use VARCHAR(65535) instead of TEXT for DataType in the Table.

For MariaDB tables, the maximum length of VARCHAR fields is 16383.

[Zvoni]

Use VARCHAR(65535) instead of TEXT for DataType in the Table.

For MariaDB tables, the maximum length of VARCHAR fields is 16383.

Whatever gave you that impression????
https://mariadb.com/docs/server/reference/data-types/string-data-types/varchar

What you're talking about is 16383 characters in 4 byte-encoding (4 x 16383 = 65532)
VARCHAR(XX) --> The XX is for how many Bytes, not Characters
TEXT --> https://mariadb.com/docs/server/reference/data-types/string-data-types/text
even TEXT is capped at 65532 bytes (!!), and can store less characters, if a multi-byte encoding is used

Even if we go to the Max of 16383 Characters: Who the hell is going to write a comment, which averages between 5 to 9 pages A4/Letter-Sized in Word
(depending of course on Font-Size)?
Nevermind people writing in a language, that actually needs 4 Byte encoding??

Even chinese (3 bytes) or japanese (2 bytes) don't need 4 bytes

[sch61]

I agree with you.