Problem with TSQLQuery

[sch61]

SQLQuery has the following parameters:
    SQL.Strings = (

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

1.       ‘SELECT’
2.       ‘  trans_request.id, ’
3.       ‘  trans_request.req_date, ’
4.       ‘  trans_request.customer, ’
5.       ‘  trans_request.trans_type, ’
6.       ‘  trans_request.comment, ’
7.       ‘  trans_request.vehicle_id, ’
8.       ‘  trans_request.start_kms, ’
9.       ‘  trans_request.finish_kms, ’
10.       ‘  trans_request.driver_id, ’
11.       ‘  trans_request.driver_report, ’
12.       ‘  trans_request.path_photo, ’
13.       ‘  trans_request.vehicle_type, ’
14.       ‘  trans_request.start_dtime, ’
15.       ‘  trans_request.finish_dtime, ’
16.       ‘  trans_request.distance’
17.       ‘FROM trans_request’
18.       ‘ORDER BY trans_request.req_date DESC’
19.  

    )
    InsertSQL.Strings = (

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

1.       ‘INSERT INTO trans_request’
2.       ‘  (’
3.       ‘  trans_request.req_date, ’
4.       ‘  trans_request.customer, ’
5.       ‘  trans_request.trans_type, ’
6.       ‘  trans_request.comment, ’
7.       ‘  trans_request.vehicle_id, ’
8.       ‘  trans_request.start_kms, ’
9.       ‘  trans_request.finish_kms, ’
10.       ‘  trans_request.driver_id, ’
11.       ‘  trans_request.driver_report, ’
12.       ‘  trans_request.path_photo, ’
13.       ‘  trans_request.vehicle_type, ’
14.       ‘  trans_request.start_dtime, ’
15.       ‘  trans_request.finish_dtime’
16.       ‘  )’
17.       ‘VALUES’
18.       ‘  (:id, ’
19.       ‘  :req_date, ’
20.       ‘  :customer, ’
21.       ‘  :trans_type, ’
22.       ‘  :comment, ’
23.       ‘  :vehicle_id, ’
24.       ‘  :start_kms, ’
25.       ‘  :finish_kms, ’
26.       ‘  :driver_id, ’
27.       ‘  :driver_report, ’
28.       ‘  :path_photo, ’
29.       ‘  :vehicle_type, ’
30.       ‘  :start_dtime, ’
31.       ‘  :finish_dtime, ’
32.       ‘  :distance’
33.       ‘  )’
34.  

    )
    UpdateSQL.Strings = (

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.       ‘  ’
19.  

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

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

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

[rvk]

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]

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]

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]

Did you check is tbTransReq.State is in dsEdit or dsInsert state just before pressing Post?

Yes. I show tbTransReq.State on Form.

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.

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]

Since when is this supposed to work?
Spot the Mistake(s) (i see 2...)

Code: Pascal  [Select][+][-]

1. ‘INSERT INTO trans_request’
2.       ‘  (’
3.       ‘  trans_request.req_date, ’
4.       ‘  trans_request.customer, ’
5.       ‘  trans_request.trans_type, ’
6.       ‘  trans_request.comment, ’
7.       ‘  trans_request.vehicle_id, ’
8.       ‘  trans_request.start_kms, ’
9.       ‘  trans_request.finish_kms, ’
10.       ‘  trans_request.driver_id, ’
11.       ‘  trans_request.driver_report, ’
12.       ‘  trans_request.path_photo, ’
13.       ‘  trans_request.vehicle_type, ’
14.       ‘  trans_request.start_dtime, ’
15.       ‘  trans_request.finish_dtime’
16.       ‘  )’
17.       ‘VALUES’
18.       ‘  (:id, ’
19.       ‘  :req_date, ’
20.       ‘  :customer, ’
21.       ‘  :trans_type, ’
22.       ‘  :comment, ’
23.       ‘  :vehicle_id, ’
24.       ‘  :start_kms, ’
25.       ‘  :finish_kms, ’
26.       ‘  :driver_id, ’
27.       ‘  :driver_report, ’
28.       ‘  :path_photo, ’
29.       ‘  :vehicle_type, ’
30.       ‘  :start_dtime, ’
31.       ‘  :finish_dtime, ’
32.       ‘  :distance’
33.       ‘  )’

EDIT: Ahh... rvk already spotted them.... ah, well....

[rvk]

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.

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]

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.

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

If there are no TEXT (Mariadb) fields in SELECT, FieldDefs.DataType=ftMemo, then everything works fine.

[rvk]

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

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

So, 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]

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]

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

ftAutoInc, 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)