Jump to content
FranzB

read integer value from database, best practice ?

Recommended Posts

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 by FranzB

Share this post


Link to post

In my code I do the following:

 

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

Share this post


Link to post
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.

 

  • Like 2
  • Thanks 1

Share this post


Link to post

 

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.

  • Like 1

Share this post


Link to post
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 by Uwe Raabe

Share this post


Link to post
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. 

Share this post


Link to post
Posted (edited)
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 by Wloochacz

Share this post


Link to post
Posted (edited)

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 by Dany Marmur
specified string column type

Share this post


Link to post

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.

 

  • Like 1

Share this post


Link to post
Posted (edited)

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 by mvanrijnen
  • Like 1
  • Thanks 1

Share this post


Link to post
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 by mvanrijnen

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×