Batch columns and values mismatch

rudiloos · 2022-11-14 00:41:14

Hi

The following code

https://gist.github.com/rudiloos/dcbc66 … a0edb7989f

produces the following error

Error SQLITE_ERROR (1) [insert into Test (RowID,CreatedOn,CreatedBy,EditedOn,EditedBy,Notes,RecStatus,File_Name,File_Type,File_Ext,File_Size,File_Date,File_MD5,ProjID,DoneOn,DoneBy,COCNo,COCType,SentOn,SentTo,DateBPM) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)] using 3.39.4 - 22 values for 21 columns, extended_errcode=1

Looking at the fields, it seems to not list the TRecordVersion field but wants to insert the value.

This only happens when the record count added is 199. No error if the record count is 198 or 200.
Changing the model by increasing or decreasing the field count by one also does not generate an error.
Adding the records one by one and not in batch mode produces no errors.

Tried to look through the code but way above my intellect and just gave me a headache.

Am I doing something wrong or is it a bug?

Synopse mORMot framework 2.0.4191, compiled with Free Pascal 3.2.2 32 bit, against SQLite 3.39.4, on Windows 10 64bit (10.0.19045)

ab · 2022-11-14 09:11:14

I will look into it when I will be back from holidays next week.
Please don't hesitate to post it again next Monday.

Perhaps someone else on the forum could look into it in the meantime.

rudiloos · 2022-11-14 14:51:32

Thanks ab,

Thanks for the reply.
I have just created a dummy field for now.
Still have a lot of other parts to keep me busy.

No worries. Enjoy your holiday.

pvn0 · 2022-11-14 18:39:57

Under certain conditions the internal encoding of a TRestBatch.add action changes.

Problem can be traced to mormot.orm.sqlite3, specifically to procedure TRestOrmServerDB.InternalBatchStop, specifically lines 2615-2628.
When the encoding is in BATCH_DIRECT_ADD it makes a call to EncodeMultiInsertSQLite3 with the SimpleFields parameter. Because TRecordVersion is not a "SimpleField" it's not accounted for and as such a discrepancy occurs between number of bits in SimpleFields (n) and actual fieldCount (n + 1) getting passed to this procedure.

Anyway, that's all from me, don't have the time right now but I hope this helps a bit.

Last edited by pvn0 (2022-11-14 18:59:10)

rudiloos · 2022-11-18 19:27:00

It does shed some light and I appreciate the effort, thank you.

The TRecordVersion, as you say, is not a simple field but as I understand it, only for for retrieval. Posting and updating should be transparent.
The main concern for me is when the record count is changed. That should have no bearing on the fields.

As I said, there is no rush and I will wait until ab is back from his well deserved vacation.

pvn0 · 2022-11-19 07:52:02

Because it only happens at specific record count it's probably a JSON buffer issue. This is more true because if you add/delete properties from your TOrm class then the problematic record count changes.

ab · 2022-11-19 19:47:43

It does not sound at all like a JSON buffer issue.

I will investigate further next week.

ab · 2022-11-21 14:56:15

TRecordVersion should not be manually set, nor retrieved.
They should not be part of the Batch data at all, but only set on the server side when inserting the data.

Currently, TRecordVersion fields are not supported in Batch mode when encoded as simple fields.
Disabling the encoding works:

Batch := TRestBatch.Create(restServer.Orm, TOrmTest, 10000,
      [boExtendedJson, boNoModelEncoding]);

Update: to be clear, for i := 1 to 198 works, to 200 works, but 199 fails - as was stated above.
So it is for the case when only a single insert is done in the 2nd part of the batch.
I will investigate further.

ab · 2022-11-21 16:34:42

My guess is that it was a one-liner fix.

Please try https://github.com/synopse/mORMot2/commit/34df4e21

rudiloos · 2022-11-26 07:17:50

Thanks ab,

Works perfectly.

mORMot Open Source

#1 2022-11-14 00:41:14

Batch columns and values mismatch

#2 2022-11-14 09:11:14

Re: Batch columns and values mismatch

#3 2022-11-14 14:51:32

Re: Batch columns and values mismatch

#4 2022-11-14 18:39:57

Re: Batch columns and values mismatch

#5 2022-11-18 19:27:00

Re: Batch columns and values mismatch

#6 2022-11-19 07:52:02

Re: Batch columns and values mismatch

#7 2022-11-19 19:47:43

Re: Batch columns and values mismatch

#8 2022-11-21 14:56:15

Re: Batch columns and values mismatch

#9 2022-11-21 16:34:42

Re: Batch columns and values mismatch

#10 2022-11-26 07:17:50

Re: Batch columns and values mismatch

Board footer