On Thu, Feb 12, 2015 at 2:04 AM, Richard Hipp <d...@sqlite.org> wrote:
> On 2/11/15, Igor Tandetnik <i...@tandetnik.org> wrote: > > On 2/11/2015 5:46 PM, Jono Poff wrote: > >> I wonder if anybody could give me a simple example in C to bind an array > >> of values to a prepared statement? > >>> > > These approaches use stock capabilities of SQLite and don't require > > heroic efforts (like patching source or implementing a virtual table). > > That virtual table you mentioned is implemented at > https://www.sqlite.org/src/artifact/9dc57417fb65bc78 (for integer > values, at least). In Oracle, you can do it via the TABLE() operator and a collection type, either VARRAY or Nested Tables (the former is much faster) select * from Things where thing1 in (select * from TABLE(:1)) We fill the collection client-side, bind it, execute that 1 statement, and get back many rows, all in a single round-trip to the server (thanks to prefetching on the select side). If thing1 happens to be your primary key (or indexed), this is very fast too. What Igor says is true, but would involve filling a temporary table first, adding an additional round-trip (if doing bulk inserts, otherwise many round-trips). SQLite unfortunately does not support table-valued functions or the equivalent of the Oracle table() operator. I admit this is a specialized use case, but as discussed in other threads, table-valued functions have other important uses related to JSON, XML, or extracting multi-valued information out of blob values. SQLite can approximate table-values functions with virtual tables, but nowhere near as convenient. FWIW. --DD _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
