On 2019/02/12 7:29 PM, Ivan Krylov wrote:
I can supply a list of source_ids in order of decreasing priority from
an array in my application, though I lose cross-database portability
(is there a portable way to pass an array to a parameterized query,
though?)...
There is no fully portable way to pass a parameterized array to a query,
though nearly every RDBMS engine has /some/ way of doing it.
You could however construct query text to achieve the same, for example:
Imagine a query that lists some values from some tables, and needs to
match against the first bit of the Fibonacci sequence, which is simply
an array of say [1,1,2,3,5,8,13,21], so if you join a subquery using a
value constructed from the array of the form:
"JOIN (SELECT n1 AS y [UNION ALL SELECT n2] ... [UNION ALL SELECT nf]) AS s"
where s is the alias for the joined query and y is the alias for the
array value.
SQL example:
SELECT x, ...
FROM ...
JOIN (SELECT 1 AS Par UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 5 UNION ALL SELECT 8 UNION ALL SELECT 13 UNION
ALL SELECT 21) AS Fib
WHERE x = Fib.Par
Note that there are far more efficient ways of doing this in most
engines, such as using the VALUES () construct, but they all differ from
engine to engine, whereas the above way is fully portable between all
engines.
Note also that another fully portable and better method would be to
construct first a TEMP table populated with the array values, and then
simply join against that - but it does require an extra query, however,
if you have any chance of re-using the query or using the same array
more than once in other queries, then the TEMP table becomes exceedingly
more efficient.
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
