SQLite3 change all records on a field? lazarus / 16-Databases ---------------------------------------------------------------------- Robz · 2015-09-29 11:53 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 · 2015-10-01 07:22 Please provide more details, code fragments etc. ---------------------------------------------------------------------- Robz · 2015-10-02 03:45 Quote from: LacaK on October 01, 2015, 09:22:32 amPlease 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       Upda]"]>Blockedde := 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 ---------------------------------------------------------------------- skiy1337lazarus · 2015-10-02 09:10 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 · 2015-10-02 14:01 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 · 2015-10-05 18:49 Quote from: Robz on October 02, 2015, 05:45:43 am1. 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: Upda]"]>Blockedde := 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. Quote from: Robz on October 02, 2015, 05:45:43 am2. 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 · 2015-10-06 06:13 Quote from: LacaK on October 05, 2015, 08:49:10 pm 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: Upda]"]>Blockedde := 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 · 2015-10-06 18:07 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.