Re: Please increase the default for SQLITE_MAX _VARIABLE_NUMBER

On Tuesday, 4 February, 2020 17:23, J. King <[email protected]> wrote:

>Not everyone has access to carrays and intarrays, either, such as PHP
>users like myself.

Then you should probably be creating a temporary table and using that/

begin immediate;
create temporary table inlist(x primary key(x)) without rowid;
insert into temp.inlist values (?); -- for each value you want in your IN list, one after each
commit;
select ... from ... where x IN (select x from temp.inlist);
drop table temp.inlist;


In Python one would do something like:

bloodybiglist = [...]
cursor.execute('begin immediate;')
cursor.execute('create temporary table inlist(x primary key(x)) without rowid;')
cursor.executemany('insert into temp.inlist values (?);', list(tuple((x,)) for x in bloodybiglist))
cursor.execute('commit;')
cursor.execute('select ... from ... where x in (select x from inlist);')
cursor.execute('drop table temp.inlist;')

In other interface wrappers you would still have to bind the parameters one at a time.  Cannot you prepare a statement in PHP (eg, the insert statement) and loop though binding the parameters and executing the prepared statement one binding parameter by each?

eg:

stmt = prepare('insert into temp.inlist values (?);')
for item in bloodybiglist:
  stmt.bind(stmt, 1, item)
  stmt.execute()
  
Which is what executemany does behind the curtain ...

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users