mORMot Open Source

hmmm, I think you meant to write (temp='0') ?

Hey no problem, it is a weird one I agree. And it doesn't happen with every empty string (in example above it happens with 2nd empty string) so perhaps add "sometimes" to the "ZDBC returns P<>nil " comment in code.

I am still on Mormot 1.8, I can paste the same code change there and give it a try tomorrow.

I wrote a post on Zeos forum but it seems to be still pending Admin approval... I think they should make sure to return nil/empty string from their code.

I am back on this Firebird project and this problem is still there for me... can someone please tell me how/what to investigate further? I cant believe a problem like this would go unnoticed by others which implies I did something wrong, but I dont see what could I have done wrong?

I dug down as low as TZInterbase6XSQLDAResultSet.GetPAnsiChar which returns bad data as well. But when called from the ZSimple zeos example project, the data is correct which means something "in between" is different and it is really hard for me to figure out what and why

if the example is confusing, a simpler example is that I have 4 records in DB, with two string fields and if I sprinkle some empty strings in the DB data, the result becomes incorrect.

 
actual data:

ID Name1 Name2
1  'A'     '1'
2  'B'     ''
3  ''     '3'
4  'D'     '4'

mormot (or zeos) returns:
{"ID":1,"Name1":"A","Name2":"1"}
{"ID":2,"Name1":"B","Name2":""}
{"ID":3,"Name1":"A","Name2":"3"} --- Name1 should be blank!
{"ID":4,"Name1":"D","Name2":"4"}

Also, the first time I encountered the problem, same code seemed to later work fine on Postgre so it seems a Firebird-only problem.

hmmm but even if it stopped at SockReceivePending it would still do a retry if I am not mistaken.

Thank you for the explanation on what is going on, but, what if we consider a scenario where client scans barcodes and adds products to the basket. In case of a timeout-induced retry, it would add two items to the basket instead of one. I suppose I could include some sort of non-repeatable client request ID so that the server knows that if an ID is repeated something has gone wrong, but then that would have to be part of every similar db-modifying operation if I'd want to be safe. Seems to me it would be easier for client side to handle occasional rare timeout errors (in the case above it would just mean "refresh the basket to see whats really in there before proceding") than to worry whether a successful request was actually executed twice.

after some digging the retry seems triggered from within THttpClientSocket.Request.

It sends the request, time passes, SockRecvLn comes back as empty string, it then calls DoRetry(STATUS_HTTPVERSIONNONSUPPORTED,Command)

Isn't this often unwanted, what if the call is making some changes like adding a record to the database? If for whatever reason server is slowed down it would add two records and the client program would not even know that a retry happened?

Maybe best to ignore me as I think I am going insane because now I am re-testing and the approach I wrote in post #7 seems to be working, except in one case where trying to start the transaction crashes with message "database should be connected" and I don't see whats different about that case... but I will try to figure it out.

I know it would not be atomic, it was meant to help me either ensure that the values are ok to put in the batch before I run the batch, or to use when batch fails in order to find which field caused the problem, as a diagnostic basically. But since batch doesn't help me I will have to rethink the whole thing.

Oops, Thank you for the info on Length<>Count, I was not aware and will alter my code

function TfunRestServer.CheckUniqueFields(rec: TSqlRecord): string;
var
  ID: TID;
  value: RawUTF8;
begin
  for var field in rec.RecordProps.Fields.List do
    if aIsUnique in field.Attributes then
      begin
        value := field.GetValue(rec, true);
        if field.SQLFieldType in STRING_FIELDS then value := '''' + value + '''';

        ID := OneFieldValueInt64(rec.RecordClass, 'rowID',
                                 FormatUTF8 ('(% = %)', [field.Name, value], []), 0);
        if (ID <> 0) and (ID <> rec.IDValue)
           then exit (field.Name);
      end;
  result := '';
end;

This seems to do the trick, though perhaps I missed an easier or more elegant way, in particular am not sure if there is a better way to convert field value to sql.

(code is part of a class that inherited TSQLRestServerDB)

It is not the primary key field, it is one of the "normal" fields marked as unique which caused this exception.

I think maybe I will add some generic code to "crawl" through all unique fields and check if their values are already present in the table before doing insert/update so that I can notify the user in which field he entered a duplicate value.

A modern neat alternative is to have this type of check for every unique field as soon as user types it, the way many website registrations work when you enter a username and it immediately adds a warning if it is already in use, but that requires way more code of course and there is always the chance that after doing the check some other user will enter that same value and the check becomes irrelevant and the operations fails after all.

Thank you, that generator can help me solve the problem by using batch instead of transactions, but if that is the path to take I think I will instead read the max ID for the table at the server's start and use my own generator to simply autoincrement (within a critical section ofc) instead.

@pvn0 I don't need a pivot table, it is a simple matter of one master record in one table and multiple many-many records in another table, all of which I would like to insert in one, "atomic", operation.

OK, I will try that, thanks

OK I agree that commit can legitimately raise an exception depending on what the database operations were, in which case I should catch the exception and do a rollback instead of halting.

But if an exception happens for transaction start or rollback that seems like something that should never happen? I am afraid of leaving the database is in some unexpected state such as having an open transaction that would never be commited so any later updates would "go into the wind".

It is truly terrible what is happening and I hope the madness will stop soon. Try to stay safe

This seems to work, but is it the correct/safe way to do it?

success := false;
DB.MainConnection.StartTransaction;
try
  success := do something with db and do something else etc...
  if success then DB.MainConnection.Commit;
finally
  if not Success
    then DB.MainConnection.RollBack;
end;

Thanks, I have just recently started to more seriously examine Mormot so I am not really familiar with version 2 and whether I should focus on that one instead, I think I will do some more testing and research with 1.18 but maybe re-think when it comes time to start with "heavy" development.