Recent

Author Topic: Help with TField.AsInteger  (Read 5645 times)

ertank

  • Full Member
  • ***
  • Posts: 244
Help with TField.AsInteger
« on: May 03, 2016, 10:51:04 pm »
Hi,

I am using SQLite3 on a WinCE6.0 device. Lazarus version is 1.6

I have below table in one of my databases:
Code: [Select]
CREATE TABLE TERMINAL_SAYIM(
 KayitId Integer NOT NULL PRIMARY KEY AUTOINCREMENT,
 AdresKodu Char(30) not null,
 BelgeNo Char(30) not null,
 BelgeTarihi DateTime not null,
 LokasyonKodu Char(30) not null,
 BolgeKodu Char(30) not null,
 GozKodu Char(30) not null,
 SSCC Char(30) not null,
 SSCCKapandi Char(1),
 Barkod Char(30) not null,
 Miktar Integer not null check(miktar > 0),
 OlcuBirimi Char(10),
 Kontrolsuz Char(1) not null,
 TekParca Char(1) not null,
 TekParcaIndex Integer not null,
 TerminalId Char(30),
 KullaniciKodu Char(30),
 OkutmaTarihSaati DateTime NOT NULL,
 SeriNo Char(25),
 Lot Char(25),
 SKT Char(10),
 IslemTuru Char(2),
 Eslesti Char(1),
 Nakledildi Char(1)
);
 
create index Idx_TERMINAL_SAYIM_AdresKodu    on TERMINAL_SAYIM(AdresKodu asc);
create index Idx_TERMINAL_SAYIM_BelgeNo      on TERMINAL_SAYIM(BelgeNo asc);
create index Idx_TERMINAL_SAYIM_LokasyonKodu on TERMINAL_SAYIM(LokasyonKodu asc);
create index Idx_TERMINAL_SAYIM_BolgeKodu    on TERMINAL_SAYIM(BolgeKodu asc);
create index Idx_TERMINAL_SAYIM_GozKodu      on TERMINAL_SAYIM(GozKodu asc);
create index Idx_TERMINAL_SAYIM_SSCC         on TERMINAL_SAYIM(SSCC asc);

My code below raise an exception saying "" is a invalid integer
Code: Pascal  [Select]
  1. procedure TfrmGiris.ToplamlariGoster(const GenelToplam: Boolean);
  2. var
  3.   q:TSQLQuery;
  4.   CountLokasyon,
  5.   CountGoz,
  6.   CountSSCC,
  7.   CountToplam : Integer;
  8. begin
  9.   // Eğer ekranda toplam gösterilmeyecek ise işlemciyi yorma
  10.   if not lblCount1.Visible then Exit;
  11.  
  12.   CountLokasyon := 0;
  13.   CountGoz      := 0;
  14.   CountSSCC     := 0;
  15.   CountToplam   := 0;
  16.  
  17.   q := TSQLQuery.Create(nil);
  18.   try
  19.     q.DataBase := DM.OKUMALAR;
  20.     q.Transaction := DM.OKUMALAR_Transaction;
  21.  
  22.     // Genel Toplam ise cihaz içindeki toplam kayıt sayısını göster
  23.     if GenelToplam then begin
  24.       q.SQL.Clear;
  25.       q.SQL.Add('select count(miktar) from terminal_sayim');
  26.       q.Prepare;
  27.       q.Open;
  28.       ShowMessage(q.Fields[0].AsString);  // <--- Here I read "0" in the message box when table is empty
  29.       CountToplam := q.Fields[0].AsInteger;  // <--- Here I get exception when table is empty
  30.     end;
  31.   finally
  32.     q.Free;
  33.   end;
  34. end;
  35.  

I read from help page about TField.AsInteger and there is this information there:
AsInteger can be used to read or write the contents of the field as a 32-bit signed integer value (of type Integer). If the native type of the field is not an integer value, then an attempt will be made to convert the field value from the native format to a integer value when reading the field's content. Likewise, when writing the property, the value will be converted to the native type of the field (if the value allows it). Therefor, when reading or writing a field value for a field whose native data type is not a 32-bit signed integer-compatible value (string values for instance), an exception may be raised.

I am positive that native data type for my miktar field is Integer, I am assuming count() and sum() native data type is integer, too. Moreover, AsString returns a 0 (zero) as a result. Can anybody advise as to why do I get an exception for my above code when table is empty?

Thanks.

GetMem

  • Hero Member
  • *****
  • Posts: 3495
Re: Help with TField.AsInteger
« Reply #1 on: May 04, 2016, 06:11:16 am »
Try this:
Code: Pascal  [Select]
  1.   //...
  2.   if GenelToplam then
  3.   begin
  4.     q.SQL.Clear;
  5.     q.SQL.Add('select count(miktar) as Total from terminal_sayim'); // or q.SQL.Add('select count(miktar) as "Total" from terminal_sayim');
  6.     q.Prepare;
  7.     q.Open;
  8.     if q.RecordCount > 0 then
  9.       ShowMessage(IntToStr(q.FieldByName('Total').AsInteger)) //or q.FieldByName('Total').AsString
  10.     else
  11.       ShowMessage('No data, empty table!')
  12.   end;      
  13.   //...

ertank

  • Full Member
  • ***
  • Posts: 244
Re: Help with TField.AsInteger
« Reply #2 on: May 04, 2016, 10:14:45 am »
Hi GetMem,

Actually, I have another workaround. My real intention was to have AsInteger to return zero if that's possible. Delphi function is just fine returning zero if my memory serves me fine.

Btw, my workaround:
Code: Pascal  [Select]
  1. begin
  2.     if GenelToplam then begin
  3.       q.SQL.Clear;
  4.       q.SQL.Add('select count(miktar) from terminal_sayim');
  5.       q.Prepare;
  6.       q.Open;
  7.       TryStrToInt(q.Fields[0].AsString, CountToplam);
  8.   end;
  9.  

Not to offend you, just my 2 cents: Since application is targeted for WinCE (low CPU and hardware) I am trying to code it to run as fast as possible. "FieldByName" in your suggestion, first searches the field name among active fields. When find it returns index number. Finally the result. So, few ticks slower than "Fields[0]" usage.

This problem I am facing will be only once/twice of the application usage. TryStrToInt will mostly work like IntToStr. Will raise an internal exception (which is controlled in function) very rarely (or none since AsString returns '0'). So, I'm fine with that at the moment.

« Last Edit: May 04, 2016, 10:53:07 am by ertank »

GetMem

  • Hero Member
  • *****
  • Posts: 3495
Re: Help with TField.AsInteger
« Reply #3 on: May 04, 2016, 10:51:31 am »
Quote
Not to offend you, just my 2 cents: Since application is targeted for WinCE (low CPU and hardware) I am trying to code it as fast as possible. "FieldByName" in your suggestion, first searches the field name among active fields. When find it returns index number. Finally the result. So, few ticks slower than "Fields[0]" usage.
Don't worry, I don't get offended so easily! :) Since count(miktar) is the only field returned, I doubt q.Fields[0] will be much faster then q.FieldByName(''), but ok I got your point.

Quote
his problem I am facing will be only once/twice of the application usage. TryStrToInt will mostly work like IntToStr. Will raise an internal exception (which is controlled in function) very rarely. So, I'm fine with that at the moment.
StrToIntDef will do it just fine, you still should check though why count() returns other then 0 when the table is empty? Is the field null somehow? I just checked(firebird 2.5), count returns 0 even if the table is empty.
Code: Pascal  [Select]
  1.  if SQLQuery1.Fields[0].IsNull then

ertank

  • Full Member
  • ***
  • Posts: 244
Re: Help with TField.AsInteger
« Reply #4 on: May 04, 2016, 10:59:11 am »
My tests on SQLite shows me returning zero. SqliteBrowser at least returns zero as a result on PC side, too. So, I really do not understand what problem might be.

My real wish is that AsInteger gets an internal control. If is null, return 0.

I can try solving it, too. However, I am kind of lost in details of Lazarus code. If someone knows internals of Lazarus, please direct me to the actual unit & function where I can write a line or two to make it return zero myself.

db.pas lines
Code: Pascal  [Select]
  1. line 337: function GetAsInteger: Longint; virtual;
  2. line 363: procedure SetAsInteger(AValue: Longint); virtual;
  3. line 399: property AsInteger: Longint read GetAsInteger write SetAsInteger;
  4.  


fields.inc lines
Code: Pascal  [Select]
  1. line 478: function TField.GetAsInteger: Longint;
  2. line 479:
  3. line 480: begin
  4. line 481:   raise AccessError(SInteger);
  5. line 482: end;
  6.  
  7. line 832: procedure TField.SetAsInteger(AValue: Longint);
  8. line 833: begin
  9. line 834:  raise AccessError(SInteger);
  10. line 835: end;
  11.  

db.pas lines (linked to AccessError above)
Code: Pascal  [Select]
  1. line 322: function AccessError(const TypeName: string): EDatabaseError;
  2.  

fields.inc lines (linked to AccessError above)
Code: Pascal  [Select]
  1. line 326: function TField.AccessError(const TypeName: string): EDatabaseError;
  2. line 327:
  3. line 328: begin
  4. line 329:   Result:=EDatabaseError.CreateFmt(SinvalidTypeConversion,[TypeName,FFieldName]);
  5. line 330: end;
  6.  

All I am seeing is raising an exception code. When I dig deeper, I find Format function in the end and that is main function possibly in one of pre-compiled files I suppose. So, I am stuck trying to fix my own patch of AsInteger since I do not know exactly where to look.
« Last Edit: May 04, 2016, 11:12:17 am by ertank »

GetMem

  • Hero Member
  • *****
  • Posts: 3495
Re: Help with TField.AsInteger
« Reply #5 on: May 04, 2016, 11:17:36 am »
Quote
My real wish is that AsInteger gets an internal control. If is null, return 0.
That would be wrong. You must somehow differentiate between null and 0. By the way, it's working fine at my side(Lazrus Trunk/FPC 3.0.0). The field is 0 even on empty table.
However, you still can do this:
Code: Pascal  [Select]
  1.   if q.Fields[0].IsNull then
  2.     CountToplam := 0
  3.   else
  4.     CountToplam := q.Fields[0].AsInteger;
  5.  

ertank

  • Full Member
  • ***
  • Posts: 244
Re: Help with TField.AsInteger
« Reply #6 on: May 04, 2016, 11:19:25 am »
My final test on WinCE device shows a zero return function, too. Please check attached file.

I am feeling that AsInteger as some kind of a bug?