how to check if null value in single line

Question

I am getting a value from db, and when the value is NULL I get an error

Could not convert variant of type (null) into type (Integer)

If I fill in the db with 0 instead of nothing (NULL) the error goes away

So say I have this

  OneSpell.PerCent     :=              FQuery.Recordset.Fields[ DB_FLD_PER_CENT    ].Value;
  OneSpell.Plus        := TCardPlus  ( FQuery.Recordset.Fields[ DB_FLD_PLUS        ].Value );
  OneSpell.Quantity    :=              FQuery.Recordset.Fields[ DB_FLD_QUANTITY    ].Value;

is there a way to say if the value is NULL then make it 0? Or does this have to be done via the db.

FQuery is an adoquery and db is access

I know I could do

if  .... = null then 
  onespell.plus := 0 
else
   .........

But I want to do it in one line for each value of onespell

Answer 1

Interesting Post and interesting answers, anyhow, what was not mentioned, at least clearly throughout all the answers, and might seem very obvious, but not so much for newcomers on Delphi/Firedac is that you need to include (Add to the uses of the module) the interface SYSTEM.VARIANTS in order to ask if a datafield.value = NULL or <> NULL.

Answer 2

Would try to show two more variants, that are going out of the box somewhat.

1: use TDataSet native access

OneSpell.Plus := TCardPlus  ( FQuery.FieldByName( 'DB_FLD_PLUS' ).AsInteger ); 

or

OneSpell.Plus := TCardPlus  ( FQuery.Fields[ 2 ].AsInteger ); 

assuming "DB_FLD_PLUS" was a third column in your query. TField.AsInteger returns zero for empty (NULL) columns.

2: use Delphi advanced records

type 
   TSpellPlus = record
       Value : TCardPlus;
       class operator Implicit( const from: TCardPlus ):  TSpellPlus; inline;
       class operator Implicit( const from: Variant ):    TSpellPlus;
       class operator Implicit( const from: TSpellPlus ): TCardPlus;  inline;
   end;

   TOneSpell = record
     private
       function GetCardPlus: TCardPlus; inline;
       procedure SetCardPlus( const Value: TCardPlus ); inline;
     public   
       var PerCent : integer;  
       var PlusVar : TSpellPlus;
       property Plus : TCardPlus read GetCardPlus write SetCardPlus;
       var Quantity : cardinal;
   end;

 function TOneSpell.GetCardPlus: TCardPlus;
 begin
    Result := Self.PlusVar;
 end;

 procedure TOneSpell.SetCardPlus( const Value: TCardPlus );
 begin
    Self.PlusVar := Value;
 end;

 class operator TSpellPlus.Implicit( const from: TSpellPlus ): TCardPlus;
 begin
    Result := From.Value; 
 end;

 class operator TSpellPlus.Implicit( const from: TCardPlus ):  TSpellPlus;
 begin
    Result.Value := From;
 end;

 class operator TSpellPlus.Implicit( const from: Variant ):    TSpellPlus;
 var i: integer;
 begin
    if VarIsNull( From )
       then i := 0 
       else i := From;
    Result.Value := TCardPlus( i );
 end;


   ....

 OneSpell.PlusVar := FQuery.Recordset.Fields[ DB_FLD_PLUS        ].Value; 

Then those three lines would be equivalent:

var cp: TCardPlus;

cp := OneSpell.Plus;
cp := OneSpell.PlusVar;
cp := OneSpell.PlusVar.Value;

Answer 3

Because you're using the ADOQuery.Recordset, which returns a reference to the underlying _Recordset (which is not from Delphi, but is an ADO object reference directly), your choices are limited to those that ADO directly support. Delphi's TADOQuery has wrapper methods that hide a lot of the complexity of dealing with ADO at a lower level, and using a Recordset instead severely limits your options.

AFAICT, the only way to do it in one line would be using a long line that utilizes both the VarIsNull function and the Math.IfThen function (which defaults to 0 if you omit the AFalse paremeter):

OneSpell.PerCent := IfThen(not VarIsNull(FQuery.Recordset.Fields[ DB_FLD_PER_CENT].Value), FQuery.Recordset.Fields[DB_FLD_PER_CENT].Value);

You can do it in two lines more readably with an intermediate OleVariant variable (still requiring the Math unit):

var
  Val: OleVariant;
...
  Val := FQuery.Recordset.Fields[DB_FLD_PER_CENT].Value;
  OneSpell.PerCent := IfThen(not VarIsNull(Val), Val);

The easiest alternative (from a Delphi code standpoint, anyway) would be to handle this in your SQL statement itself, using IsNull or Coalesce or its equivalent in MS Access, so you don't have to worry about it in your application code; you can simply access the Value and know it contains an integer instead.

Actually, the easiest alternative is not to use RecordSet at all if you don't actually need to do so (because there are multiple recordsets in your TADOCommand results). If you simply use Delphi's TADOQuery directly, you can simply use the native TField.AsXXX properties, which will handle the conversions for you automatically:

OneSpell.PerCent := FQuery.Fields[DB_FLD_PER_CENT].AsInteger; // Or AsFloat

Answer 4

Stop doing the work in delphi code, your query should handle this. Most DB backends support the COALESCE function. So in your query, just use : SELECT COALESCE(MyIntegerField, 0) FROM Foo. If MyIntegerField contains a NULL value, COALESCE will return the first non-NULL value, in this case 0

EDIT

Just realized that your DB backend is MS Access, in that case use the IIF function:

SELECT Percent= IIf([Percent] Is Null, 0, [Percent]) FROM Foo

Answer 5

If you know that you will always want the Null interpreted as a false Boolean, 0 numeric value, or empty string, you can set System.Variants.NullStrictConvert = false.

(Strictly speaking, for the Null to string conversion the value of System.Variants.NullAsStringValue will be taken).

Answer 6

That is the common way to go:

Extract the repeating parts to keep the code DRY and to increase readability

function IfNull( const Value, Default : OleVariant ) : OleVariant;
begin
  if Value = NULL then
    Result := Default
  else
    Result := Value;
end;

...
OneSpell.Plus := IfNull( FQuery.Recordset.Fields[ DB_FLD_PER_CENT].Value, 0 );