Handle the 'maximum number of sql variables in an expression is 1000' error about spine-database-api HOT 15 CLOSED

In GitLab by @manuelma on Nov 28, 2018, 17:37

mentioned in commit b4da5d8

from spine-database-api.

In GitLab by @manuelma on Nov 28, 2018, 17:38

@PvPer checkout last dev, might just work. At least committing small changes isn't badly broken.

from spine-database-api.

In GitLab by @manuelma on Nov 28, 2018, 17:39

changed title from Handle {-maximum number of sql variables in an expression is 1000-} to Handle {+ the 'maximum number of sql variables in an expression is 1000' error+}

from spine-database-api.

In GitLab by @PvPer on Nov 29, 2018, 13:29

mentioned in commit c293f2c

from spine-database-api.

In GitLab by @PvPer on Nov 29, 2018, 13:29

mentioned in commit c48e188

from spine-database-api.

In GitLab by @PvPer on Nov 29, 2018, 13:30

n = 999 seemed to do the trick.

Added two tests about this.

Every add method will have the same problem if you try to get all new parameter values. See test_insert_and_retrive_many_objects in the tests

from spine-database-api.

In GitLab by @fabianoP on Dec 4, 2018, 12:50

My limit is 499

from spine-database-api.

In GitLab by @manuelma on Dec 4, 2018, 12:56

ok, so we need to find a way to query the limit

from spine-database-api.

In GitLab by @fabianoP on Dec 4, 2018, 13:01

I think the problem is in the "IN" clause,the good workaround in this case is using a pagination technique. Basically what we do is we limit the data view by users dividing the views in pages of 100-400 records max. In this way we will never show more than the max number of record in a page.
A bit more like SQL workbench where the user in the interface cannot see more than 100 rows at the same time, then when it scrolls it will load the next 5 following pages.
Was I clear or was a messy explanation?

from spine-database-api.

In GitLab by @manuelma on Dec 4, 2018, 13:12

It's clear, although this issue is not about viewing data, but rather about querying data for other purposes. Take a look at the commit_session method in DiffDatabaseMapping class when you have the chance. We're trying to fetch all records that have been modified by the user using the diff tables, so we can apply the changes on the original tables. I think the current solution is fine as long as we can find out the right limit.

from spine-database-api.

In GitLab by @fabianoP on Dec 4, 2018, 13:24

I got it. Thanks!

from spine-database-api.

In GitLab by @manuelma on Dec 4, 2018, 13:33

Cool, and you have any idea how to find out this limit for a given engine/session/dialect?

from spine-database-api.

In GitLab by @fabianoP on Dec 4, 2018, 13:43

Need to do some test, but I think creating a temporary table and using an inner join could fix the problem:

https://stackoverflow.com/questions/47847984/how-to-extend-the-maximum-number-of-sql-variables-supported-by-sqllite3

from spine-database-api.

In GitLab by @manuelma on Dec 4, 2018, 15:47

I don't see how that solution works. We still need to select the items from the original table to insert them in the temporary table, and for that we need the IN clause. The solution we have now is almost good, we just need to figure out the right limit. We can hard-code the limit to 499 to be safe, or even to something like 99 if we want to be ultra-safe. Alternatively, we could run a mock-up query to figure out what the limit is for a particular case.

Edit: or am I missing something???

from spine-database-api.

@manuelma is this issue still valid?

from spine-database-api.