How to use bind variables for IN clause in SQL statement

[sdeshpande <sa...@web.de>]

Hello,

How do I use bind variables for the SQL statements having IN clause. F.ex

SELECT id, name FROM t
WHERE id in (10,20,30)

As the IN list will have 'n' number of values, I am not able to specify
fixed number of bind variables like

SELECT id, name FROM t
WHERE id in (?,?,?....)

As our product supports various other RDBMS I use following solution in case
of Oracle

SELECT Id, SYSDATE
FROM t
WHERE Id IN (SELECT * FROM THE (select CAST( fn_vostrtbl (:1) AS voTableType
) FROM dual))

CREATE OR REPLACE TYPE voTableType AS TABLE OF NUMBER;
/

CREATE OR REPLACE FUNCTION fn_vostrtbl ( p_str in varchar2 ) RETURN
voTableType
AS
l_str LONG DEFAULT p_str || ',';
l_n NUMBER;
l_data voTableType := voTableType();
BEGIN
LOOP
l_n := instr( l_str, ',' );
exit when (nvl(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
l_str := substr( l_str, l_n+1 );
END LOOP;
RETURN l_data;
END;
/


-------------------------------------------------------------

SELECT id, SYSDATE
FROM t
WHERE id IN ( SELECT * FROM THE ( SELECT CAST( fn_vostrtbl( :1 ) AS
VOTABLETYPE ) FROM dual ) )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0 0
Execute 13 0.00 0.00 0 0 0 0
Fetch 17 0.00 0.00 0 39 156 91
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 31 0.00 0.00 0 39 156 91

-------------------------------------------------------------


With the use of above code, I can parse SQL statement with variable length
IN clause only once and execute it #N number of times.

How do I implement the same in Derby.

Thanks in advance

Sameer Deshpande
-- 
View this message in context: http://www.nabble.com/How-to-use-bind-variables-for-IN-clause-in-SQL-statement-tf3599725.html#a10054807
Sent from the Apache Derby Developers mailing list archive at Nabble.com.