SQLite Locate RunError(203) problems

[bobonwhidbey]

With smaller tables, say < 15,000 rows, SQLite runs perfectly for me. It seems to stumble when trying to do certain operations on larger files. The app crashes with a RunError(203) For example:

Code: Pascal  [Select][+][-]

1. if MyDB.Query.Locate('ID', Edit1.Text,[]) then  

This works great for smaller IDs, not so for larger ones.

In a similar vein, using the "Bottom" feature on a DBGrid scroll bar doesn't work with a large file. See below.

Similar (the same) problems occur when using Query.Last.

Is there a solution to these problems?

[molly]

On what exact environment are you running your code ? A virtual environment perhaps ?

RunTime Error 203 informs that you're running out of memory.

[bobonwhidbey]

I'm on Win10. Perhaps I don't understand your question.

[molly]

Are you running windows 10 _natively_ on your hardware or do you 'abuse' virtualization software (Like virtualbox, vmware, etc) to run windows 10 ?

In case the latter you have to make sure to 'provide' enough memory for lazarus and your application to be able to run properly. There are also hosts that are not really suited (such as attempting to run win10 on a arm device such as a pi, then start lazarus and expect to be able to debug your application).

I strikes me as odd running out of memory, but everything is possible  . (Assuming that you did not run into a bug or use faulty code)

edit: ah, sorry i just noticed your sig. still, the virtualization question remains  I assume that you have enough memory installed ? enough free diskspace etc ?

[bobonwhidbey]

I have 16 GB of memory.  Not using any virtualization software. Don't know what it is.

I get these errors when running the program within Laz from the IDE and also when running the compiled EXE.  For example, When I "Query.Locate" the 5,000th or the 10,000th record in my db file - no problem. Trying to locate 15,000 is a problem. Same with small and big files when executing a Query.Last. If the file is small enough, I can execute the Last command.

[GAN]

From the wiki: http://wiki.freepascal.org/locate

Note: Locate is only implemented in non-unidirectional datasets, i.e. you must be able to move back and forwards through the dataset.

Locate and lookup act at low level inside a dataset to search for records. Often it is more efficient to filter/limit what gets into the dataset in the first place, e.g. using SQL WHERE clauses.

I use locate but only in small tables and the classic Select * from table.

[jamie]

Well, you are using the 32 bit version of lazarus by the looks of it..

 Being on a 64 bit OS does not give you all that memory for your 32 bit app.

 I don't know if SQLite comes in 64 bit but I would try installing the 64 bit
version of lazarus and get the 64 bit of SQLite, if it exist.

 32 bit apps are limited to 2 G's last time I looked.



 

[bobonwhidbey]

One approach that would work for me is to create a smaller dataset - say 200 records that SHOULD include the desired record; e.q.

    s := IntToStr(strtointdef(Edit1.Text, 100) - 100);
    Q.Close;
    Q.SQL.Text :='Select * From MyTable Where ID>' + s + ' LIMIT 200';
    Q.Open;  // picks up about 100 records on either side of the desired record
    if Q.Locate('ID', Edit1.Text, []) then
        etc. // ID is the primary key

This will successfully locate the desired ID within the smaller dataset "Q" without running into memory problems.  How do I then move (change the cursor) to the appropriate record in the larger dataset "Query"?

[GAN]

How do I then move (change the cursor) to the appropriate record in the larger dataset "Query"?

Something like that:

Code: Pascal  [Select][+][-]

1. if Q.Locate('ID', Edit1.Text, []) then
2.   begin
3.     etc...;
4.     nPosition:=Q.RecNo;
5.   end;
6.  
7. //To move the cursor
8. Q.SetRecNo(nPosition);

TDataSet.RecNo http://lazarus-ccr.sourceforge.net/docs/fcl/db/tdataset.recno.html
TDataSet.GetRecNo http://lazarus-ccr.sourceforge.net/docs/fcl/db/tdataset.getrecno.html
TDaraSet.SetRecNo http://lazarus-ccr.sourceforge.net/docs/fcl/db/tdataset.setrecno.html

[bobonwhidbey]

I thought the Locate function also moved the cursor. In this case, it moves it to the position in the Q dataset (with about 200 records).  I want to move to that same record in the Query dataset (with many 1,000s of records).

[GAN]

I thought the Locate function also moved the cursor. In this case, it moves it to the position in the Q dataset (with about 200 records).  I want to move to that same record in the Query dataset (with many 1,000s of records).

Yes, you're right. Close the small query then open the big query and set the pointer.

Code: Pascal  [Select][+][-]

1. BigQuery.SetRecNo(nPosition);

Note: I don't know if it's works...

[bobonwhidbey]

No it doesn't work

BigQuery.Recno := 50000;  // or any large-ish number

results in the same 203 error.

[bobonwhidbey]

Even a brute force approach like this

  MyID := '0';
  while (MyID <> Edit1.Text) and not BigQuery.EOF do
  begin
    BigQuery.Next;
    MyID := BigQuery.FieldByName('ID').AsString;
  end;

runs into the same RunError.

Is there a way to free up memory, perhaps every "n" (10,000 ?)  reads?

[Thaddy]

You may try  SetProcessWorkingSetSize(MainHandle, $FFFFFFFF, $FFFFFFFF) ; after a certain number, but the heap may still be fully locked in which case that does not help too much.
But in effect your design is wrong for a BigQuery, because you use Pascal operations on the buffers instead of SQL operations.
Next and Locate work on the Pascal level and on internal buffer. If you simply use Select queries and views instead there is no heap problem, even with millions of records.
In effect: that's what Sql is designed for.