mORMot Open Source

larand54

Member

Registered: 2018-12-25

Posts: 104

How to avoid exception from fields containing null values?

How should I deal with fields that sometimes contain NULL-values when using variants
The code below shows how I tried to deal with it.
You can't even check if the field is null before using it.
The only way I know is to have a

Try
except

around each assignment but that doesn't taste good.
I'm sure there is a better solution but I'm lost.

type
  TNames = packed record
      field1: integer;
      theNullField: RawUTF8;
      field3: RawUTF8;
  end;

var
  I: isqldbrows;
  row: variant;
  names: TNames;
begin
  I := Props.execute('select * from dbo.mytable where name = ?', ['Peter']);
  if I.Step(true) then begin
  I.RowDocVariant(row,[]);
  names.fImportDir := row.Field1;
  if not row.theNullField. = null then               // This raise an exception
      names.theNullField := row.theNullField      // This also raises an exception if executed.
  else
      names.theNullField := '';
  names.Field3 := row.Field3;
  I.ReleaseRows;
end;

Last edited by larand54 (2023-03-30 10:20:35)

---

Delphi-11, WIN10

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,653

Website

Re: How to avoid exception from fields containing null values?

First keep JSON_FAST default option to the RowToDocVariant() method.

Try perhaps (not tested):

If VarIsEmptyOrNull(row.theNullField) then 

Last time I checked, VarIsNull() from variants.pas does not properly check for by-reference variants.

BTW how is "names" defined in your code?

larand54

Member

Registered: 2018-12-25

Posts: 104

Re: How to avoid exception from fields containing null values?

Ok, I took a snap from my code and removed a lot of irrelevant code and changed the names in hope that it would be easier to read.
The "names" is just a packed record. I included it in the snap now.

Well, your suggestion worked perfectly.
I remembered when I saw it that I used it before but my memory is just a... memory now I'm afraid.

---

Delphi-11, WIN10