Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (herse.apache.org: local policy)
Date: Wed, 18 Apr 2007 06:13:06 -0700
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: How to use bind variables with IN clause in SQL statement
In-reply-to: <214051410@web.de>
Sender: Richard.Hillegas@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <46261962.7000306@sun.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-15
Content-transfer-encoding: 7BIT
References: <214051410@web.de>
User-Agent: Thunderbird 1.5.0.5 (X11/20060828)

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
>
>