SQLite + large database

[Warlock051]

Hi,

I have pretty large SQLite database (around 4.5MB, 30k of rows in 5 columns). And i'm opening that database with:

Sub App_Start
   CreateConnection
End Sub
   
Sub CreateConnection
   Connection.New1
   Connection.Open("Data Source = " & AppPath & "\data.db")
   Command.New1("", Connection.Value)
   Reader.New1
   Connection.BeginTransaction
   Command.CommandText = "SELECT * FROM Items" 
   Command.ExecuteTable("Order", 0)
   Connection.EndTransaction
   frmList.Show
End Sub

It works fine desktop (when starting with F5). When i compile it and try to run, i'm getting

"An error occured on sub __main_creatconnection.
An attempt was made to load a program with an incorrect format. (Exception from HRESULT:0x....) Continue?" Yes/No

And on my device it won't start... just loading and loading and loading...

Is it 4.5MB too much for SQLite on WM6.5 HTC?

Items are organized in groups... should i create separate database (.db) for each group?

Thank you in advance.

 

[Erel]

This error is pretty strange.
Does it work if you remove the ExecuteTable line?

4.5mb is not too large. However in almost all cases you should not use ExecuteTable to load the complete databases. SQL is much more powerful than the Table control.
You should only query for the currently relevant items.

Note that there is no need to call BeginTransaction / EndTransaction in this case. It is only needed when you do many updates (Insert queries for example) one after another.

 

[Warlock051]

Yes, the problem persist after i changed program to:

Sub CreateConnection
   Connection.New1
   Connection.Open("Data Source = " & AppPath & "\roba.db")
   Command.New1("", Connection.Value)
    Reader.New1
   Command.CommandText = "SELECT * FROM Artikli" 
   'Command.ExecuteTable("Narudzba", 0)
   frmList.Show
End Sub

Without Begin and EndTransaction program worked fine in development mode (F5), but not on device.

When i put comment mark on ExecuteTable (and recompiled) program started on device (i waited only 5 sec) - of course with blank table.

Some thoughts... do you think that will be fine (if we manage to run my program on device) to operate with whole database, but to show only the part i need (most of my need i can get trough "SELECT something FROM database WHERE...").

EDIT:
I've tried to set range of records in database (to show items from only one group) and it worked on device. I took whole database but in the Table just showed about 500 of them according to my query. Does it mean that 4.5MB and 30.000 rows in table are too much to handle?

 

[Erel]

Can you please contact me by mail? You appear as an unlicensed user for some reason.