db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sameer Deshpande <sameer_deshpa...@web.de>
Subject How to use bind variables with IN clause in SQL statement
Date Wed, 18 Apr 2007 08:38:09 GMT
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

<code>

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

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

</code>


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

_______________________________________________________________
SMS schreiben mit WEB.DE FreeMail - einfach, schnell und
kostenguenstig. Jetzt gleich testen! http://f.web.de/?mc=021192


Mime
View raw message