Problem with TSQLQuery

[sch61]

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

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.

"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]

It 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]

I 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]

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?

No. I didn't try to convert anything.
Here is the current SQL

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

1. SELECT
2.     tr.id,
3.     tr.req_date,
4.     tr.customer,
5.     tr.trans_type,
6.     tr.vehicle_type,
7.     tr.comment,
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.     tr.start_kms,
18.     tr.finish_kms,
19.     tr.distance,
20.     cf.vstart_fuel AS fuel_start,
21.     cf.vrefill_fuel AS fuel_refill,
22.     cf.vconsumption  fuel_cons,
23.     cf.vend_fuel AS fuel_end,
24.     tr.path_photo,
25.     tr.driver_report,
26.     vp.rpath AS req_rpath
27. FROM trans_request tr
28. LEFT JOIN sp_customer sc      ON tr.customer    = sc.id
29. LEFT JOIN sp_trans_type stt   ON tr.trans_type  = stt.id
30. LEFT JOIN sp_employees se     ON tr.driver_id   = se.id
31. LEFT JOIN sp_vehicles sv       ON tr.vehicle_id = sv.id
32. LEFT JOIN sp_vehicle_type svt    ON sv.vtype       = svt.id
33. LEFT JOIN view_path vp            ON vp.id_req      = tr.id
34. LEFT JOIN view_trans_fuel cf      ON cf.id = tr.id
35. ORDER BY tr.req_date DESC, tr.id DESC;
36.  
37.  

And this is UpdateSQL:

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

1. UPDATE trans_request
2. SET
3.   trans_request.req_date = :req_date,
4.   trans_request.customer = :customer,
5.   trans_request.trans_type = :trans_type,
6.   trans_request.comment = :comment,
7.   trans_request.vehicle_id = :vehicle_id,
8.   trans_request.start_kms = :start_kms,
9.   trans_request.finish_kms = :finish_kms,
10.   trans_request.driver_id = :driver_id,
11.   trans_request.driver_report = :driver_report,
12.   trans_request.path_photo = :path_photo,
13.   trans_request.vehicle_type = :vehicle_type,
14.   trans_request.start_dtime = :start_dtime,
15.   trans_request.finish_dtime = :finish_dtime
16. WHERE
17.    (trans_request.id = :OLD_id)
18.  

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.

It 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]

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]

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]

SELECT
  TableText.id,
  TableText.comment
FROM TableText

It`s SELECT from test project.

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

[sch61]

  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]

There 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]

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][+][-]

1. procedure TForm1.Button1Click(Sender: TObject);
2. begin
3.   tbTestText.Insert;
4.   tbTestText.FieldByName('id').asInteger := Random(1000);
5.   tbTestText.FieldByName('comment').asString := 'abcd';
6.   tbTestText.Post;
7.   tbTestText.Next;
8. end;

Just to make sure the Insert worked.

I got the following error when pressing that button:

Project 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]

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)

The field editor is empty for all tables.

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.

 This is how it is done in a real project.

[rvk]

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)

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]

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]

Thank you, I will make use of your discovery.

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

[rvk]

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).