How to use bind variables with IN clause in SQL statement

[Sameer Deshpande <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

<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

Re: How to use bind variables with IN clause in SQL statement

[Rick Hillegas <Ri...@Sun.COM>]

Hi Sameer,

You might try using a temporary table to hold the values in the IN list. 
For more details, see the section in the Derby Reference Manual titled 
"DECLARE GLOBAL TEMPORARY TABLE statement". Here's an example of this 
technique.

Regards,
-Rick

autocommit off;
ij> drop table t;
0 rows inserted/updated/deleted
ij> create table t( id int, name varchar(50) );
0 rows inserted/updated/deleted
ij> declare global temporary table session.tempIDs( tmpID int ) not logged;
0 rows inserted/updated/deleted
ij> commit;
ij> insert into t( id, name )
values
( 1, 'foo1' ),
( 10, 'foo10' )
;
2 rows inserted/updated/deleted
ij> commit;
ij> insert into session.tempIDs( tmpID ) values ( 10 ), ( 20 ), ( 30 );
3 rows inserted/updated/deleted
ij> select id, name
from t, session.tempIDs
where id=tmpID;
ID         |NAME
--------------------------------------------------------------
10         |foo10

1 row selected
ij> commit;
ij> select id, name
from t, session.tempIDs
where id=tmpID;
ID         |NAME
--------------------------------------------------------------

0 rows selected
ij> commit;




Sameer Deshpande wrote:
> 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
>
>