Error: To Many Variables SQLite

hi peoples,
my name's Leonardo I have a problem in xamarin forms and I need a help in query using execute of SQLite.

I have a solution and one query "db.Execute("delete from table where OID in (? ? ? ? ? ? ?)", List.ToArray()), but this query get a exception "To many Variable",
I know that the SQLite exists a Max value of parameters and I need increase this value, I need help please... the that do?

Thanks

Sorry, my English is bad.

Answers

  • ManojkumarMaliManojkumarMali USMember ✭✭✭

    Hi,

    db.Execute("delete from table where OID in (? ? ? ? ? ? ?)", List.ToArray()), but this query

    Except deleting all records at the same time, try to use loop to delete records one by one.

  • hi @ManojkumarMali thanks for your return, but exists +56.000 records, if use loop the time of deleting is ten minutes, I Need this query for deleting the records in about 1 minute.
    You know what to do?

  • ManojkumarMaliManojkumarMali USMember ✭✭✭

    If you have records more than 56,000, then your database will also take more time than one min.

    Can you tell us, from where these 56k records comes?
    Can you give us some more details of your data?

  • @ManojkumarMali

    my application is as follows, it is connected to a server, this server brings 56000 in 1 table, out the other, through the synchronization, when the user synchronizes again it brings the data that has been updated on the server if the updated data on the server are all those +56000, it will bring them to update on the mobile. If I do loop it takes a lot longer, so we opted to do with a query "db.Execute (" delete from table where OID in (?????) ", List.ToArray ())" only when are many records it ends up giving the error "Many variables", I read a lot about the limits of SQLIte and saw that you have to increase these limits, would you know how to increase them or do you have some performance to do with loops? below is the method:

    try
    {
        List<ob ject> ListaObj = new List<ob ject>();
                var ListaDeletar = Lista.Select(Y => Y.OID.ToString()).ToList();
               ListaObj.AddRange(ListaDeletar);
               string IndicadorParametro = string.Join(",", ListaDeletar.Select(x => "?").ToList());
               int i = db.Execute("Delete from MedidaControle where OID in (" + IndicadorParametro + ")", ListaObj.ToArray());
    }catch(Exception Erro)
    {
        throw Erro.message;
    }
    

    if i use Loop, the performance's very slow :/

  • up

  • up

  • JohnHardmanJohnHardman GBUniversity mod
    edited October 2018

    @leonardoartuso2 said:
    hi peoples,
    my name's Leonardo I have a problem in xamarin forms and I need a help in query using execute of SQLite.

    I have a solution and one query "db.Execute("delete from table where OID in (? ? ? ? ? ? ?)", List.ToArray()), but this query get a exception "To many Variable",
    I know that the SQLite exists a Max value of parameters and I need increase this value, I need help please... the that do?

    Thanks

    Sorry, my English is bad.

    Rather than having db.Execute do parameter substitution, I suggest you create a single string that contains the entire query to be executed, and then use that query string. Doing this will allow you to check the query string in the debugger to ensure that it is valid. My guess, without having tried it, is that Execute is trying to replace each '?' with a parameter, but is finding a mismatch in the number of '?'s and parameters. If that is the case, using a simple query string instead of parameter substitution should make it clear.

  • hi @JohnHardman , the problem is that SQLite exist a max value of parameters that's 999, and when he surpassed this value, he went from error. I'll try to write everything in a single string, thanks for the feedback, I'll already give answers.

  • JohnHardmanJohnHardman GBUniversity mod

    @leonardoartuso2 said:
    hi @JohnHardman , the problem is that SQLite exist a max value of parameters that's 999, and when he surpassed this value, he went from error. I'll try to write everything in a single string, thanks for the feedback, I'll already give answers.

    If attempting to pass more than 999 parameters, I'd be impressed by the author(s) of SQLite in that they return an error rather than something worse happening. I would question whatever requirement you are trying to implement to see if handling more than 999 parameters is necessary or whether things can be reorganised to avoid that requirement. Your app is unlikely to be responsive if handling those sorts of numbers.

Sign In or Register to comment.