How to get the actual exception message if it occurs within a batch?

Milos · 2022-04-06 18:55:50

Unless if I am missing something, if an sql error happens during a batch operation there is no way to report that error back to the client because the batch masks the exception and then raises its own exception which does not contain the sql error?

Log shows both exceptions

20220406 18503808 EXC                   EZSQLException ("SQL Error: ERROR:  duplicate key value violates unique constraint \"article_ident_key\"\nDETAIL:  Key (ident)=(11111) already exists.") [HttpSvr 8889/FUNDUS THttpApiSvr] at b0df7a
20220406 18532930 EXC                   EORMBatchException {"Message":"TfunRestServer.EngineBatchSend: Results[0]=0 on POST@Article TSqlArticle"} [HttpSvr 8889/FUNDUS THttpApiSvr] at 75fb84

but if I wrap batch execution in my try except I will of course only be aware of the second (final) one. Any way around it?

Last edited by Milos (2022-04-06 18:57:02)

ab · 2022-04-07 06:56:55

No, there is no such mechanism.
You just know that it failed, then you have the information in the logs, for forensic.

The idea of the framework behavior is that DB process should always work, and enter a "panic state" if something is wrong on the DB side.
For instance, a duplicate key violation should never occur, unless the database state is corrupted. You should fix the logic which triggered this error.
Therefore, the ability to react explicitly to low-level DB error was never part of the framework workflow. If you rely on it, then you can run manual SQL, but this is not the purpose of the ORM, even less the purpose of its BATCH process which focuses on performance, to handle such cases.

Milos · 2022-04-07 10:14:34

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.

Milos · 2022-04-07 11:21:50

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)

ab · 2022-04-07 11:53:16

Yes, this is up to the logic to ensure that there would be no coherency problem.

Perhaps instead of several OneFieldValueInt64() calls, just call a single Add(), then check its result. It would fail if there was a constraint failure.
One INSERT will be faster than several SELECT + INSERT, especially if there is no constraint problem (the optimistic case).

Milos · 2022-04-07 12:55:51

Ah, but this scenario is about batch processing, so I can't use Add().

And I used batch only because I assumed it was a replacement for multi-table atomic DB transaction, but thats a separate issue in a separate topic.

ab · 2022-04-07 19:15:11

If you need something atomic, calling CheckUniqueFields() would not be.

Note that, in your code, "for var field in rec.RecordProps.Fields.List do" may be wrong, because as stated by the documentation:

    /// quick access to the TOrmPropInfo list
    // - note that length(List) may not equal Count, since is its capacity
    property List: TOrmPropInfoObjArray

(in mORMot 2, length(List)=Count, but it is not ensured in mORMot 1)

Milos · 2022-04-07 20:20:06

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

mORMot Open Source

#1 2022-04-06 18:55:50

How to get the actual exception message if it occurs within a batch?

#2 2022-04-07 06:56:55

Re: How to get the actual exception message if it occurs within a batch?

#3 2022-04-07 10:14:34

Re: How to get the actual exception message if it occurs within a batch?

#4 2022-04-07 11:21:50

Re: How to get the actual exception message if it occurs within a batch?

#5 2022-04-07 11:53:16

Re: How to get the actual exception message if it occurs within a batch?

#6 2022-04-07 12:55:51

Re: How to get the actual exception message if it occurs within a batch?

#7 2022-04-07 19:15:11

Re: How to get the actual exception message if it occurs within a batch?

#8 2022-04-07 20:20:06

Re: How to get the actual exception message if it occurs within a batch?

Board footer