Recent

Author Topic: SQLite3 change all records on a field?  (Read 4757 times)

Robz

  • New Member
  • *
  • Posts: 38
SQLite3 change all records on a field?
« on: September 29, 2015, 01:53:47 pm »
Hi,
I am trying to switch from xbase to sqelite3 with Lazarus. I have one database with 2,573,517 records. I want to change one field but all records. The data for the field is from several sources. One source is an xbase file, another source is an edit box, a third is from an ini file and a fourth is from another two fields from the same SQLite table I want to write the result.
I had no problem with xbase; but the same method accessing one field, but all records does not seem to work with SQLlite3.
Specifically,  the problem is to calculate the distance between a broadcast transmitter antenna site and a fixed reception location for every licensed broadcaster.
The basic data is from the FCC CDBS web site.
Suggestions are greatly appreciated.

 

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: SQLite3 change all records on a field?
« Reply #1 on: October 01, 2015, 09:22:32 am »
Please provide more details, code fragments etc.

Robz

  • New Member
  • *
  • Posts: 38
Re: SQLite3 change all records on a field?
« Reply #2 on: October 02, 2015, 05:45:43 am »
Please provide more details, code fragments etc.

Thank you for your reply,
I have tried the following code to write to one field, but all records.

procedure TForm4.Distance(p: TlatLon);
var
  i, n, rc: integer;
  lat, lon: double;
  fLat, fLOn, fDist: Tfield;
  da : TDistance;
begin

  n := 0;
  with SQLquery1 do
  begin
    DisableControls;
    try
      fLat := FieldByName('LATITUDE');
      fLOn := FieldByName('Longitude');
      fDist := FieldByName('Dist');
      First;
      while not EOF do
      begin
        lat := fLat.AsInteger / 10000;  //10000 to convert
        lon := -fLon.AsInteger / 10000;   
        da := getDistance(p, getVect(lat,lon));
        edit;
        fDist.AsFloat := da.dist;
        //post;
        Next;
      end;

    finally
      UpdateMode := upWhereAll;
      ApplyUpdates;
      EnableControls;
    end;
  end;
end;


1. While the code seems to work, there are gaps. It seems, randomly, that the fDist field is null. Most of the time,though, the fDist field holds the correct value.

2. If I access a larger table (i.e. tv_eng_data from CDBS website) and try the same procedure as above, the application self terminates after about 30 seconds.
I use the same procedure with XBASE and never had a problem, even with much larger tables.

The GetDistance (not to be confused with the Distance procedure) is a simple spherical trigonometry function that returns both distance and angle between two points on the Earth's surface. Only the distance parameter is used to test the function, but will use both parameters if a solution can be found. I will be happy to provide additional code if needed.
Thank you, Rob
« Last Edit: October 02, 2015, 05:50:05 am by Robz »

skiy1337lazarus

  • New Member
  • *
  • Posts: 34
Re: SQLite3 change all records on a field?
« Reply #3 on: October 02, 2015, 11:10:05 am »
Looking at:
fDist.AsFloat := da.dist;

When I use floating point numbers, I ALWAYS use EnsureRange...
Maybe this will help:
fDist.AsFloat := EnsureRange(da.dist,Min,Max);

You will need to declare:
const
Min=
Max=

Choose smallest numbers possible.

sky_khan

  • Guest
Re: SQLite3 change all records on a field?
« Reply #4 on: October 02, 2015, 04:01:59 pm »

SQLQuery caches all rows. Thats probably why your code terminates on (very) big table.
You may try use SQLQuery1.Unidirectional = true before opening it and calling ApplyUpdates for every row
but dbgrid wont work with this setting.

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: SQLite3 change all records on a field?
« Reply #5 on: October 05, 2015, 08:49:10 pm »
1. While the code seems to work, there are gaps. It seems, randomly, that the fDist field is null. Most of the time,though, the fDist field holds the correct value.
What is original value of "Dist" field? Is it NULL ? If yes, then probably record is not updated at all. It can be caused by your condition: UpdateMode := upWhereAll.
If this is set then for SQL UPDATE is constructed WHERE where all columns are included. May be, that problem is with floating-point numbers, where rounding can take place.
It is always better use upWhereKeyOnly if your table has primary key.

2. If I access a larger table (i.e. tv_eng_data from CDBS website) and try the same procedure as above, the application self terminates after about 30 seconds.
What is your sql server ? What type of SQLConnection do you use ? (SQLQuery1 is connected to?)

Robz

  • New Member
  • *
  • Posts: 38
Re: SQLite3 change all records on a field?
« Reply #6 on: October 06, 2015, 08:13:56 am »

What is original value of "Dist" field? Is it NULL ?
 

Yes the value is null.

Quote

If yes, then probably record is not updated at all. It can be caused by your condition: UpdateMode := upWhereAll.
If this is set then for SQL UPDATE is constructed WHERE where all columns are included. May be, that problem is with floating-point numbers, where rounding can take place.
It is always better use upWhereKeyOnly if your table has primary key.

Currently the table does not have a primary key. I will try your suggestion.
I have tried a recasting from float to string, however no change.

field.asFloat := da.dist to
field.asString := format('%4.2f',[da.dist]);




Quote
What is your sql server ? What type of SQLConnection do you use ? (SQLQuery1 is connected to?)

SQLite3Connection1

I appreciate your reply, thank you.
Rob

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: SQLite3 change all records on a field?
« Reply #7 on: October 06, 2015, 08:07:20 pm »
If it does not help, then it will require debuging and identify updates which does not apply to database. Then look in detail what SQL is constructed and why data does not seems be updated.

 

TinyPortal © 2005-2018