read integer value from database, best practice ?

[FranzB 0]

I use this  code  below to read an integer value  from an database

 

 

  aObject.IntValue := Fieldbyname('IntegerField').AsInteger;

 

 

my code is functional, if there is an integer value stored.  

Reading an  " "  empty field I get an exception failure. 

What is the  best coding style to  avoid  this error  in an application ?  

 

Edited September 19, 2021 by FranzB

[jnvoirol 3]

In my code I do the following:

 

if not FieldByname('IntergerField').isNull then aObject.IntValue :=FieldByname('IntegerField').AsInteger;

[Achim Kalwa 61]

2 hours ago, FranzB said:

I use this  code  below to read an integer value  from an database

Which Database system do you use? Which connection components?

Quote

my code is functional, if there is an integer value stored.  

Reading an  " "  empty field I get an exception failure. 

What is the  best coding style to  avoid  this error  in an application ?  

If the field in the database is declared as "Integer", it should never contain a Space character. It could be NULL, which means it doesn't contain any value. Most database access components will translate NULL to the value 0, so an empty field should not raise an exception.

 

Which value do you expect in case the field is NULL?

 

To be save, you can test for empty fields yourself:

var F : TField;

F := Qry.FindField('IntegerField');
if Assigned(F) and (not F.IsNull) then
  aObject.IntValue := F.AsInteger
else
  aObject.IntValue := -1; // or whatever value you choose in case of an empty field.

 

[Uwe Raabe 2057]

I suspect the field is a string field?

[corneliusdavid 214]

 

9 hours ago, FranzB said:

Reading an  " "  empty field I get an exception failure.

If you know there's a space in the field, then the code you listed, "Fieldbyname('IntegerField').AsInteger" is misleading as that is most definitely NOT an IntegerField as Integer fields cannot have spaces (well, SQLite might allow it). If your "integer" field might possibly contain values other than integers, you will have no choice but to test to make sure it actually contains an integer before trying to use it as one. The AsInteger function doesn't check the data type, it just assumes you know what you're doing with your database.

[Uwe Raabe 2057]

1 hour ago, corneliusdavid said:

The AsInteger function doesn't check the data type

That is not quite right. It handles some non-Integer data types and try to convert the value to Integer. Here are some common examples:

function TStringField.GetAsInteger: Integer;
begin
  Result := StrToInt(GetAsString);
end;

function TFloatField.GetAsInteger: Integer;
begin
  Result := Integer(Round(GetAsFloat));
end;

function TMemoField.GetAsInteger: Integer;
begin
  Result := StrToInt(GetAsString);
end;

 

Edited September 19, 2021 by Uwe Raabe

[corneliusdavid 214]

32 minutes ago, Uwe Raabe said:

That is not quite right. It handles some non-Integer data types and try to convert the value to Integer.

Within both the context of the original question and my response, we were talking specifically about Integer fields--not strings or floats or memos. 

[Wloochacz 2]

On 9/19/2021 at 11:52 AM, FranzB said:

I use this  code  below to read an integer value  from an database

  aObject.IntValue := Fieldbyname('IntegerField').AsInteger;

my code is functional, if there is an integer value stored.  

Reading an  " "  empty field I get an exception failure. 

What is the  best coding style to  avoid  this error  in an application ?  

 

For your "ugly" database, such code will be the simplest and error-free:

aObject.IntValue := StrToIntDef( Fieldbyname('IntegerField').AsString, 0);

 

Edited October 4, 2021 by Wloochacz

[Guest]

IMHO just think; RDBMS => handles Null (if you do not explicitely set "Not NULL" for the column.

Delphi variables (mostly) do not handle null (var a: integer).

Watch out for empty strings from nullable columns in the RDBMS - IMHO it is a bit of a special case.

Edited October 5, 2021 by Guest
specified string column type

[Lars Fosdal 1792]

If a column is nullable, it is a good practice (for T-SQL) to use stuff like ISNULL(MyString, '') or ISNULL(MyInt, 0) in queries, particularly if there are totals or min/max operators in the query or view.

 

[mvanrijnen 123]

I have a helper for this kind of stuff (in real the helper contains some more methods : ) :

 

TMyFieldHelper = class helper for TField
public
  function AsIntegerDef(const ADefault : integer) : integer;
end; 

function TMyFieldHelper.AsIntegerDef(const ADefault: integer): integer;
begin
  Result := ADefault;
  try
    if (not Self.IsNull) then
       Result := Self.AsInteger;
  except
    {TODO -oOwner -cGeneral : check which to swallow ... }
    // swallow any exception
  end;
end;

Edited October 5, 2021 by mvanrijnen

[mvanrijnen 123]

On 10/5/2021 at 10:58 AM, Lars Fosdal said:

If a column is nullable, it is a good practice (for T-SQL) to use stuff like ISNULL(MyString, '') or ISNULL(MyInt, 0) in queries, particularly if there are totals or min/max operators in the query or view.

 

or use COALESCE
 

In interbase we don't have ISNULL(MyInt, 0) function, but we do have COALESCE which seems to do the same thing ?
 

Edited October 13, 2021 by mvanrijnen

[Lars Fosdal 1792]

@mvanrijnen - I don't use Interbase, so I can't say for sure, but in SQL Server they differ slightly.