Problem with TSQLQuery
lazarus / 16-Databases

----------------------------------------------------------------------
sch61 · 2025-12-05 15:29

SQLQuery has the following parameters:
    SQL.Strings = (
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      
‘SELECT’      ‘  trans_request.id, ’      ‘  trans_request.req_date,
’      ‘  trans_request.customer, ’      ‘  trans_request.trans_type,
’      ‘  trans_request.comment, ’      ‘  trans_request.vehicle_id,
’      ‘  trans_request.start_kms, ’      ‘  trans_request.finish_kms,
’      ‘  trans_request.driver_id, ’      ‘
 trans_request.driver_report, ’      ‘  trans_request.path_photo, ’  
  ‘  trans_request.vehicle_type, ’      ‘  trans_request.start_dtime,
’      ‘  trans_request.finish_dtime, ’      ‘
 trans_request.distance’      ‘FROM trans_request’      ‘ORDER BY
trans_request.req_date DESC’     )
    InsertSQL.Strings = (
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      
‘INSERT INTO trans_request’      ‘  (’      ‘  trans_request.req_date,
’      ‘  trans_request.customer, ’      ‘  trans_request.trans_type,
’      ‘  trans_request.comment, ’      ‘  trans_request.vehicle_id,
’      ‘  trans_request.start_kms, ’      ‘  trans_request.finish_kms,
’      ‘  trans_request.driver_id, ’      ‘
 trans_request.driver_report, ’      ‘  trans_request.path_photo, ’  
  ‘  trans_request.vehicle_type, ’      ‘  trans_request.start_dtime,
’      ‘  trans_request.finish_dtime’      ‘  )’      ‘VALUES’      ‘
 (:id, ’      ‘  :req_date, ’      ‘  :customer, ’      ‘
 :trans_type, ’      ‘  :comment, ’      ‘  :vehicle_id, ’      ‘
 :start_kms, ’      ‘  :finish_kms, ’      ‘  :driver_id, ’      ‘
 :driver_report, ’      ‘  :path_photo, ’      ‘  :vehicle_type, ’   
 ‘  :start_dtime, ’      ‘  :finish_dtime, ’      ‘  :distance’      ‘
 )’     )
    UpdateSQL.Strings = (
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 trans_request’      ‘SET’      ‘  trans_request.req_date =
:req_date,’      ‘  trans_request.customer = :customer, ’      ‘
 trans_request.trans_type = :trans_type, ’      ‘
 trans_request.comment = :comment, ’      ‘  trans_request.vehicle_id
= :vehicle_id, ’      ‘  trans_request.start_kms = :start_kms, ’     
‘  trans_request.finish_kms = :finish_kms, ’      ‘
 trans_request.driver_id = :driver_id, ’      ‘
 trans_request.driver_report = :driver_report, ’      ‘
 trans_request.path_photo = :path_photo, ’      ‘
 trans_request.vehicle_type = :vehicle_type, ’      ‘
 trans_request.start_dtime = :start_dtime, ’      ‘
 trans_request.finish_dtime = :finish_dtime’      ‘WHERE’      ‘  
(trans_request.id = :OLD_id)’      ‘  ’     )
    Options = [sqoKeepOpenOnCommit, sqoAutoApplyUpdates,
sqoAutoCommit]
    ParseSQL = False

When executing Post after fields  edited, an error occurs:
Operation not allowed, dataset ‘tbTransReq’ is not in an edit or
insert state.

If you write only SQL.Strings, leave UpdateSQL.Strings,
InsertSQL.Strings and all other SQL empty, and set ParseSQL = True,
everything works fine.
   
This is a simple SQL query, and I can use the second method, but I
want to make a complex query that the parser cannot handle.
Then I need UpdateSQL.Strings, InsertSQL.Strings, and all other SQL.
   
    What am I doing wrong?

----------------------------------------------------------------------
LemonParty · 2025-12-05 16:34

QuoteWhen executing Post after fields  edited, an error occurs:
Operation not allowed, dataset ‘tbTransReq’ is not in an edit or
insert state.
Accordingly to this you didn't call nor Edit nor Insert before doing
Post. Check if your code have an appropriate calls.

----------------------------------------------------------------------
sch61 · 2025-12-05 18:02

I switch to edit mode by pressing the corresponding button on
DBNavigator. Posts are also made by pressing a button on DBNavigator.

----------------------------------------------------------------------
rvk · 2025-12-05 20:31

Check if tbTransReq.State is dsEdit after pressing that button.
Check if tbTransReq.State is dsEdit before pressing the post button.

If it's not, somewhere along the way, the state changed.
(Maybe you have transaction.commit somewhere in code (which changes
the state).

Otherwise show more code.
Are you sure the d navigator is working on the data source connected
to tbTransReq?

BTW. Your insert statement is missing the id AND distance field in the
first part.
But that should have gotten you a different error.
(You have :is and :distance in values but not in the fields above)
Are you sure you're not eating an exception somewhere (hiding it with
try/except)?

----------------------------------------------------------------------
sch61 · 2025-12-06 12:34

Hi, everyone.

Thank you for your comments. I saw the errors myself and corrected
them.
Nothing has changed.
I did a lot of experiments and established the following.
If there are no TEXT (Mariadb) fields in SELECT,
FieldDefs.DataType=ftMemo, then everything works fine.
If there is at least one such field in SELECT, TSQLQuery breaks and
the error I wrote about appears.
The most interesting thing is that the changes are made to the
database, i.e. ApplayUpdates and Commit are executed. But further work
with the data set in its current form is impossible. So using try...
makes no sense.
If anyone has any ideas on how to solve this problem, I would be
grateful.

----------------------------------------------------------------------
rvk · 2025-12-06 13:35

Did you check is tbTransReq.State is in dsEdit or dsInsert state just
before pressing Post?
Do you have any beforePost etc events or is this just clean code?

There is somewhere in your code where the state switches to dsBrowse
after which you can't use Post. You can build in checks on multiple
places to check for that.

----------------------------------------------------------------------
sch61 · 2025-12-06 13:53

Quote from: rvk on December 06, 2025, 02:35:07 pmDid you check is
tbTransReq.State is in dsEdit or dsInsert state just before pressing
Post?

Yes. I show tbTransReq.State on Form.
Quote from: rvk on December 06, 2025, 02:35:07 pmDo you have any
beforePost etc events or is this just clean code?

There is somewhere in your code where the state switches to dsBrowse
after which you can't use Post. You can build in checks on multiple
places to check for that.

No

The same code works if there are no TEXT fields in SELECT.
I changed the TEXT fields in the table to VARCHAR(4000) and everything
worked.
But that's not good.

----------------------------------------------------------------------
Zvoni · 2025-12-08 07:36

Since when is this supposed to work?
Spot the Mistake(s) (i see 2...)
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ‘INSERT
INTO trans_request’      ‘  (’      ‘  trans_request.req_date, ’     
‘  trans_request.customer, ’      ‘  trans_request.trans_type, ’     
‘  trans_request.comment, ’      ‘  trans_request.vehicle_id, ’      ‘
 trans_request.start_kms, ’      ‘  trans_request.finish_kms, ’      ‘
 trans_request.driver_id, ’      ‘  trans_request.driver_report, ’   
 ‘  trans_request.path_photo, ’      ‘  trans_request.vehicle_type, ’ 
    ‘  trans_request.start_dtime, ’      ‘
 trans_request.finish_dtime’      ‘  )’      ‘VALUES’      ‘  (:id, ’ 
    ‘  :req_date, ’      ‘  :customer, ’      ‘  :trans_type, ’      ‘
 :comment, ’      ‘  :vehicle_id, ’      ‘  :start_kms, ’      ‘
 :finish_kms, ’      ‘  :driver_id, ’      ‘  :driver_report, ’      ‘
 :path_photo, ’      ‘  :vehicle_type, ’      ‘  :start_dtime, ’     
‘  :finish_dtime, ’      ‘  :distance’      ‘  )’
EDIT: Ahh... rvk already spotted them.... ah, well....

----------------------------------------------------------------------
rvk · 2025-12-08 09:08

Quote from: sch61 on December 06, 2025, 02:53:10 pmThe same code works
if there are no TEXT fields in SELECT.
I changed the TEXT fields in the table to VARCHAR(4000) and everything
worked.
But that's not good.
The type affinity for VARCHAR(xx) is TEXT. So when you change that,
it's still TEXT.

The only thing I can imagine is the way you fill the fields before
posting or that you have predefined fields in your TSQLQuery (which I
wouldn't recommend).

You can make a small test project showing the problem. Just a small
form with a few fields where you reproduce the problem.
(usually when you do that... in a lot of cases, you find out what
causes the problem by yourself. And if not, you can post the full
test-project)

----------------------------------------------------------------------
Zvoni · 2025-12-08 09:12

Quote from: rvk on December 08, 2025, 10:08:46 amQuote from: sch61 on
December 06, 2025, 02:53:10 pmThe same code works if there are no TEXT
fields in SELECT.
I changed the TEXT fields in the table to VARCHAR(4000) and everything
worked.
But that's not good.
The type affinity for VARCHAR(xx) is TEXT. So when you change that,
it's still TEXT.

The only thing I can imagine is the way you fill the fields before
posting or that you have predefined fields in your TSQLQuery (which I
wouldn't recommend).

You can make a small test project showing the problem. Just a small
form with a few fields where you reproduce the problem.
(usually when you do that... in a lot of cases, you find out what
causes the problem by yourself. And if not, you can post the full
test-project)
From where do you derive "affinity"?
OP is using MariaDB, not SQLite
From Reply #4
QuoteIf there are no TEXT (Mariadb) fields in SELECT,
FieldDefs.DataType=ftMemo, then everything works fine.

----------------------------------------------------------------------
rvk · 2025-12-08 09:15

Quote from: Zvoni on December 08, 2025, 10:12:16 amFrom where do you
derive "affinity"?
OP is using MariaDB, not SQLite
From Reply #4
O, sorry. OP mentioned TEXT. I didn't know MariaDB has TEXT fields in
its DDL. I never used those there.
(I thought TEXT was a SQLite thing  )

The other remarks still stand (about predefined fields in TSQLQuery
and making a small test-project showing the problem).

----------------------------------------------------------------------
Zvoni · 2025-12-08 09:38

Quote from: rvk on December 08, 2025, 10:15:17 amQuote from: Zvoni on
December 08, 2025, 10:12:16 amFrom where do you derive "affinity"?
OP is using MariaDB, not SQLite
From Reply #4
O, sorry. OP mentioned TEXT. I didn't know MariaDB has TEXT fields in
its DDL. I never used those there.
(I thought TEXT was a SQLite thing  )

The other remarks still stand (about predefined fields in TSQLQuery
and making a small test-project showing the problem).
Eh?
MariaDB actually does have a TEXT-DataType -->
https://mariadb.com/docs/server/reference/data-types/string-data-types
But it's more like a BLOB-Type, apparently

So, it's more a question of: Does OP actually have the correct
Datatypes in his Table-Design?

----------------------------------------------------------------------
rvk · 2025-12-08 09:41

Quote from: Zvoni on December 08, 2025, 10:38:06 amSo, it's more a
question of: Does OP actually have the correct Datatypes in his Table-
Design?
Yeah, that was also one of my remarks...
Don't use predefined data-fields in TSQLQuery. Just let the component
create the fields on connect (if possible). Lot's of mistakes are made
with predefined fields.

----------------------------------------------------------------------
sch61 · 2025-12-08 11:40

Hi everyone.
Thank you for the discussion.
I created a project with a simple form and a simple data table for
experimentation and came to the conclusion that I posted earlier.
1. With very simple tables that do not have fields that accept the
FieldDefs.DataType=ftMemo type, everything works great.
2. If you set ParseSQL = True and remove all SQL except the simplest
SELECT, then everything also works with ANY field types.
But I want to use a  SELECT with JOIN, which the parser cannot
understand and makes the data set unmodifiable. Therefore, I have to
write my own INSERTSQL, UPDATESQL, DELETESQL. And in this case, the
events that started the discussion occur.

If I have time, I'll look at the source code and see what's going on
there.

----------------------------------------------------------------------
Zvoni · 2025-12-08 13:01

You still haven't explained why you need Binary Text Data ("VARCHAR is
no good")
https://www.freepascal.org/docs-html/fcl/db/tfieldtype.html
QuoteftAutoInc, Auto-increment integer value (4 bytes)
ftBlob,    Binary data value (no type, no size)
ftMemo,    Binary text data (no size)
ftGraphic, Graphical data value (no size)
"JOIN"-ed SELECTS are near to impossible to be
"updateable/insertable".
Don't use UpdateSQL/InsertSQL in that case.
Use a Stored Procedure on the MariaDB-Server, where you just pass the
values.
Can be called from Connection-Object with ExecuteDirect-Method (or
whatever it's called)

----------------------------------------------------------------------
sch61 · 2025-12-08 14:33

Quote from: Zvoni on December 08, 2025, 02:01:52 pmYou still haven't
explained why you need Binary Text Data ("VARCHAR is no good")
https://www.freepascal.org/docs-html/fcl/db/tfieldtype.html

Let me explain the situation.
I wanted to use a TEXT type field for user comments in a MariaDB
table. This would allow users to enter comments of virtually unlimited
length (64K). A VARCHAR(4000) field can hold 1000 characters in UTF8
encoding.
I don't know if this will be enough for users.
I have nothing against the VARCHAR type  I just don't like having to
replace other types with it.

Quote from: Zvoni on December 08, 2025, 02:01:52 pm"JOIN"-ed SELECTS
are near to impossible to be "updateable/insertable".
Don't use UpdateSQL/InsertSQL in that case.
Use a Stored Procedure on the MariaDB-Server, where you just pass the
values.
Can be called from Connection-Object with ExecuteDirect-Method (or
whatever it's called)

I believe that UpdateSQL/InsertSQL were created specifically for my
case. And so it is written in the documentation.
It is a pity that the implementation has a bug.
For now, I have found a solution to the problem using VARCHAR.
Thank you for the discussion.

----------------------------------------------------------------------
rvk · 2025-12-08 14:40

Quote from: sch61 on December 08, 2025, 03:33:47 pmIt is a pity that
the implementation has a bug.
Can you share the test-project?
(that's why I asked to create a test-project. It's then easier for
others to check and maybe fix of submit a big request)

----------------------------------------------------------------------
Zvoni · 2025-12-08 14:44

Quote from: sch61 on December 08, 2025, 03:33:47 pmI believe that
UpdateSQL/InsertSQL were created specifically for my case. And so it
is written in the documentation.
Yes.
UpdateSQL/InsertSQL/DeleteSQL have been introduced specifically for
the usecase of DB-Bound-Controls in conjunction with DBNavigator
The documentation even states, that if you do not specify those 3
Properties, Lazarus is trying to "guess" the Statements.

BUT: You still have the option to "ignore" them entirely, and execute
your SQL-Statements directly.
And Insert/Update/Delete are the Statements that DON'T return a
Recordset, which means they can be executed using the Connection-
Object (which actually IS what happens under the hood)
You just have to refresh your SQLQuery-Object, which holds the SELECT-
Statement.

Doesn't change the Fact, that those 3 Properties are not "usable" for
(complex) "JOIN"-ed SELECT-Statements

EDIT: btw: I understand now your trouble with TEXT vs VARCHAR
Have you tried CASTING your TEXT-Column (DataType TEXT) to VARCHAR in
your SELECT-Statement?

----------------------------------------------------------------------
sch61 · 2025-12-08 15:16

QuoteEDIT: btw: I understand now your trouble with TEXT vs VARCHAR
Have you tried CASTING your TEXT-Column (DataType TEXT) to VARCHAR in
your SELECT-Statement?

No. I didn't try to convert anything.
Here is the current SQL
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 SELECT   
tr.id,    tr.req_date,    tr.customer,    tr.trans_type,   
tr.vehicle_type,    tr.comment,    tr.vehicle_id,    sv.reg_number AS
vcl_reg_number,    sv.fuel_consumption AS vcl_cons,    svt.name AS
vcl_type_name,    tr.start_dtime,    tr.finish_dtime,   
tr.driver_id,    se.fname AS dr_fname,    se.surname AS dr_surname,  
tr.start_kms,    tr.finish_kms,    tr.distance,    cf.vstart_fuel AS
fuel_start,    cf.vrefill_fuel AS fuel_refill,    cf.vconsumption
 fuel_cons,    cf.vend_fuel AS fuel_end,    tr.path_photo,   
tr.driver_report,    vp.rpath AS req_rpathFROM trans_request trLEFT
JOIN sp_customer sc      ON tr.customer    = sc.idLEFT JOIN
sp_trans_type stt   ON tr.trans_type  = stt.idLEFT JOIN sp_employees
se     ON tr.driver_id   = se.idLEFT JOIN sp_vehicles sv       ON
tr.vehicle_id = sv.idLEFT JOIN sp_vehicle_type svt    ON sv.vtype    
 = svt.idLEFT JOIN view_path vp            ON vp.id_req      =
tr.idLEFT JOIN view_trans_fuel cf      ON cf.id = tr.idORDER BY
tr.req_date DESC, tr.id DESC;  
And this is UpdateSQL:
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
trans_requestSET  trans_request.req_date = :req_date, 
trans_request.customer = :customer,   trans_request.trans_type =
:trans_type,   trans_request.comment = :comment,  
trans_request.vehicle_id = :vehicle_id,   trans_request.start_kms =
:start_kms,   trans_request.finish_kms = :finish_kms,  
trans_request.driver_id = :driver_id,   trans_request.driver_report =
:driver_report,   trans_request.path_photo = :path_photo,  
trans_request.vehicle_type = :vehicle_type,  
trans_request.start_dtime = :start_dtime,   trans_request.finish_dtime
= :finish_dtimeWHERE   (trans_request.id = :OLD_id) 
During testing, it became apparent that the TEXT field type caused an
error, while the VARCHAR type functioned correctly. Therefore, I had
to change the field type in the database table to VARCHAR.

Quote from: rvk on December 08, 2025, 03:40:25 pmQuote from: sch61 on
December 08, 2025, 03:33:47 pmIt is a pity that the implementation has
a bug.
Can you share the test-project?
(that's why I asked to create a test-project. It's then easier for
others to check and maybe fix of submit a big request)

I can share a test project on Github tomorrow. But you will need a
database to run it. I'll see what I can do with my MariaDB.
I can't promise it will be ready tomorrow

----------------------------------------------------------------------
sch61 · 2025-12-08 17:04

https://github.com/sch61/LazTextTest.git

I created a public project on GitHub.
I cannot grant access to my MariaDB server.
I don't have access to my home router to forward a port from the
external interface to the mariadb host.
Please try it on your own server. The scripts for creating test tables
are available in the project.

----------------------------------------------------------------------
rvk · 2025-12-08 17:24

Before I try this... I see you are working with pre-defined field in
the query.
Have you tried it without?
(I see you have size 100 for one field)

Try it without so the program automatically created those field
depending on the result set on open.

----------------------------------------------------------------------
sch61 · 2025-12-08 18:07

SELECT
  TableText.id,
  TableText.comment
FROM TableText

It`s SELECT from test project.

I don`t add fields from FieldDefs in test project.

----------------------------------------------------------------------
sch61 · 2025-12-08 18:18

TForm1 = class(TForm)
    DBGrid1: TDBGrid;
    DBGrid2: TDBGrid;
    dsTestText: TDataSource;
    DBNavigator1: TDBNavigator;
    DBNavigator2: TDBNavigator;
    dsTestVarchar: TDataSource;
    MySQL80Connection1: TMySQL80Connection;
    PageControl1: TPageControl;
    Panel1: TPanel;
    Panel2: TPanel;
    tbTestText: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    tbTestVarchar: TSQLQuery;
  private

  public

  end;

There are no declared variables of type field in the form.

----------------------------------------------------------------------
rvk · 2025-12-08 18:22

Quote from: sch61 on December 08, 2025, 07:18:10 pmThere are no
declared variables of type field in the form.
They are defined if I look in the .lfm (and Object inspector).
(I can't get my mysql 8 library up at the moment)

Right click the TSQLQuery and choose Edit fields. Are there any fields
defined??
(because I see some in the .lfm and the object inspector if you look
under the TSQLQuery components)

I also normally don't use autoconnect for a database connection.
I usually set Connected to false and set it in the FormCreate. That
way I have the chance to change the Database connection string to
point to another location for the database (so it's not hardcoded in
the program). I never design fields and columns at design-time. You
can do it but sometimes it goes wrong.

----------------------------------------------------------------------
rvk · 2025-12-08 18:45

I think I found the probably cause.

As a test I added a button on the second tab (for TEXT grid) with this
code:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button1Click(Sender: TObject);begin  tbTestText.Insert; 
tbTestText.FieldByName('id').asInteger := Random(1000); 
tbTestText.FieldByName('comment').asString := 'abcd'; 
tbTestText.Post;  tbTestText.Next;end;Just to make sure the Insert
worked.

I got the following error when pressing that button:
QuoteProject project1 raised exception class 'EDatabaseError' with
message:
tbTestText : Refresh SQL resulted in empty result set.
 In file 'db.pas' at line 2546
In your TDBNavigator, this exception probably gets eaten and the grid
never goes in actual edit mode (you can see that because you don't get
the highlighted "Delete" button, so you know your not in Edit mode).

And if you do a Post, then it gives you the error it isn't in edit
mode (which is logical because of that eaten exception).

I removed the complete Refresh.SQL and after that it worked fine.

I can't really see a difference in the Refresh.SQL from TableString
and TableText, so I'm not sure why it failed for TEXT.
But it gives you something to search for (maybe some option
difference?).

----------------------------------------------------------------------
sch61 · 2025-12-08 18:49

QuoteThey are defined if I look in the .lfm (and Object inspector).
(I can't get my mysql 8 library up at the moment)

Right click the TSQLQuery and choose Edit fields. Are there any fields
defined??
(because I see some in the .lfm and the object inspector if you look
under the TSQLQuery components)

The field editor is empty for all tables.

QuoteI also normally don't use autoconnect for a database connection.
I usually set Connected to false and set it in the FormCreate. That
way I have the chance to change the Database connection string to
point to another location for the database (so it's not hardcoded in
the program). I never design fields and columns at design-time. You
can do it but sometimes it goes wrong.

 This is how it is done in a real project.

----------------------------------------------------------------------
rvk · 2025-12-08 18:51

Quote from: sch61 on December 08, 2025, 07:49:03 pmQuoteThey are
defined if I look in the .lfm (and Object inspector).
(I can't get my mysql 8 library up at the moment)

Right click the TSQLQuery and choose Edit fields. Are there any fields
defined??
(because I see some in the .lfm and the object inspector if you look
under the TSQLQuery components)

The field editor is empty for all tables.
Yeah, but the field definitions are there. But I already saw you have
Auto connect and Auto Activate in design time. Then those fields are
automatically generated.
As I said... I never use auto connect in design time. I do everything
at runtime.

But read my previous post. It explains the problem and how to fix it
temporarily (by removing the Refresh.SQL).

----------------------------------------------------------------------
sch61 · 2025-12-08 18:54

Thank you, I'll try that. I suspected that something was happening
during Refresh because all changes were made in the database.
The SQLQuery parameters are the same for all tables.
I don't see any difference.

----------------------------------------------------------------------
sch61 · 2025-12-08 19:26

Thank you, I will make use of your discovery.

I would be interested to know whether this is a bug or a feature?

----------------------------------------------------------------------
rvk · 2025-12-08 21:10

I think it might be a bug.
The problem is after applyupdates (in Post) the code wants to do a
refresh (if refreshsql is set). When a TEXT fields is involved, I
think the record doesn't contain the correct last_id (from the
AutoInc). So the refreshsql doesn't return a result. Maybe this is
because TEXT is handled differently from VARCHAR and by the time TEXT
is stored (after the normal fields) the result for AutoInc is gone
(just a guess).

You can leave refreshsql empty or set option sqoRefreshUsingSelect.

I'm not sure if this bug is already reported (haven't looked yet).

----------------------------------------------------------------------
Zvoni · 2025-12-09 08:08

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT   
tr.id,    tr.req_date,    tr.customer,    tr.trans_type,   
tr.vehicle_type,    TRIM(CAST(tr.comment AS VARCHAR(65535))) AS
comment,  -- THIS ONE    tr.vehicle_id,    sv.reg_number AS
vcl_reg_number,    sv.fuel_consumption AS vcl_cons,    svt.name AS
vcl_type_name,    tr.start_dtime,    tr.finish_dtime,   
tr.driver_id,    se.fname AS dr_fname,    se.surname AS
dr_surname,....

----------------------------------------------------------------------
rvk · 2025-12-09 09:27

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 · 2025-12-09 11:04

Quote from: rvk on December 08, 2025, 10:10:51 pmYou can leave
refreshsql empty or set option sqoRefreshUsingSelect.

Thank you. I did just that.

----------------------------------------------------------------------
sch61 · 2025-12-09 11:19

Quote from: Zvoni on December 09, 2025, 09:08:38 ami 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT   
....    TRIM(CAST(tr.comment AS VARCHAR(65535))) AS comment,  -- THIS
ONE.... 
ftMemo

----------------------------------------------------------------------
sch61 · 2025-12-09 11:31

Quote from: rvk on December 09, 2025, 10:27:48 am
1)
The id field is set as ReadOnly and excluded from InsertSQL and
UpdateSQL.

2)
Thank you for clarifying the details.

----------------------------------------------------------------------
Zvoni · 2025-12-09 11:53

Quote from: sch61 on December 09, 2025, 12:19:07 pmQuote from: Zvoni
on December 09, 2025, 09:08:38 ami 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT   
....    TRIM(CAST(tr.comment AS VARCHAR(65535))) AS comment,  -- THIS
ONE.... 
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 · 2025-12-09 12:46

Quote from: Zvoni on December 09, 2025, 12:53:29 pmUse VARCHAR(65535)
instead of TEXT for DataType in the Table.

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

----------------------------------------------------------------------
Zvoni · 2025-12-09 12:56

Quote from: sch61 on December 09, 2025, 01:46:55 pmQuote from: Zvoni
on December 09, 2025, 12:53:29 pmUse 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 · 2025-12-09 16:15

I agree with you.