4

This looks like limitation from Microsoft azure mobile client for offline sync service for android.
In my xamarin form application i have 40 azure tables to sync with remote. Whenever the particular request(_abcTable.PullAsync) has the more number record like 5K, PullAsync returns the exception saying that : Error executing SQLite command: 'too many SQL variables'.
That pull async URL goes like this : https://abc-xyz.hds.host.com/AppHostMobile/tables/XXXXXXResponse?$filter=(updatedAt ge datetimeoffset'2017-06-20T13:26:17.8200000%2B00:00')&$orderby=updatedAt&$skip=0&$top=5000&ProjectId=2&__includeDeleted=true.

But in postman i can see the same Url returning the 5K records and Works fine in iPhone device as well but failing only in android.
From the above PullAsync request if i change the "top" parameter value from 5000 to 500 it works fine in android but takes more time. Do i have any other alternatives without limiting the performance.

Package version:
Microsoft.Azure.Mobile.Client version="3.1.0"
Microsoft.Azure.Mobile.Client.SQLiteStore" version=“3.1.0”

Microsoft.Bcl version="1.1.10"

Microsoft.Bcl.Build version="1.0.21"


SQLite.Net.Core-PCL version="3.1.1"
SQLite.Net-PCL version="3.1.1" 

SQLitePCLRaw.bundle_green version="1.1.2"
SQLitePCLRaw.core" version="1.1.2"
SQLitePCLRaw.lib.e_sqlite3.android" version="1.1.2"

SQLitePCLRaw.provider.e_sqlite3.android" version="1.1.2"
Please let me know if i need to provide more information. Thanks

Suchith
  • 1,276
  • 17
  • 39

1 Answers1

0

Error executing SQLite command: 'too many SQL variables

Per my understanding, your sqlite may touch the Maximum Number Of Host Parameters In A Single SQL Statement mentions as follows:

A host parameter is a place-holder in an SQL statement that is filled in using one of the sqlite3_bind_XXXX() interfaces. Many SQL programmers are familiar with using a question mark ("?") as a host parameter. SQLite also supports named host parameters prefaced by ":", "$", or "@" and numbered host parameters of the form "?123".

Each host parameter in an SQLite statement is assigned a number. The numbers normally begin with 1 and increase by one with each new parameter. However, when the "?123" form is used, the host parameter number is the number that follows the question mark.

SQLite allocates space to hold all host parameters between 1 and the largest host parameter number used. Hence, an SQL statement that contains a host parameter like ?1000000000 would require gigabytes of storage. This could easily overwhelm the resources of the host machine. To prevent excessive memory allocations, the maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999.

The maximum host parameter number can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_VARIABLE_NUMBER,size) interface.

I refered Debugging the Offline Cache and init my MobileServiceSQLiteStore as follows:

var store = new MobileServiceSQLiteStoreWithLogging("localstore.db");

I logged all the SQL commands that are executed against the SQLite store when invoking pullasync. I found that after successfully retrieve response from mobile backend via the following request:

https://{your-app-name}.azurewebsites.net/tables/TodoItem?$filter=((UserId%20eq%20null)%20and%20(updatedAt%20ge%20datetimeoffset'1970-01-01T00%3A00%3A00.0000000%2B00%3A00'))&$orderby=updatedAt&$skip=0&$top=50&__includeDeleted=true

Microsoft.Azure.Mobile.Client.SQLiteStore.dll would execute the following sql statement for updating the related local table:

BEGIN TRANSACTION

INSERT OR IGNORE INTO [TodoItem] ([id]) VALUES (@p0),(@p1),(@p2),(@p3),(@p4),(@p5),(@p6),(@p7),(@p8),(@p9),(@p10),(@p11),(@p12),(@p13),(@p14),(@p15),(@p16),(@p17),(@p18),(@p19),(@p20),(@p21),(@p22),(@p23),(@p24),(@p25),(@p26),(@p27),(@p28),(@p29),(@p30),(@p31),(@p32),(@p33),(@p34),(@p35),(@p36),(@p37),(@p38),(@p39),(@p40),(@p41),(@p42),(@p43),(@p44),(@p45),(@p46),(@p47),(@p48),(@p49)

UPDATE [TodoItem] SET [Text] = @p0,[UserId] = @p1 WHERE [id] = @p2

UPDATE [TodoItem] SET [Text] = @p0,[UserId] = @p1 WHERE [id] = @p2

.

.

COMMIT TRANSACTION

Per my understanding, you could try to set MaxPageSize up to 999. Also, this limitation is from sqlite and the update processing is automatically handled by Microsoft.Azure.Mobile.Client.SQLiteStore. For now, I haven't find any approach to override the processing from Microsoft.Azure.Mobile.Client.SQLiteStore.

Community
  • 1
  • 1
Bruce Chen
  • 18,207
  • 2
  • 21
  • 35