From derby-user-return-6628-apmail-db-derby-user-archive=db.apache.org@db.apache.org Wed Apr 18 08:55:47 2007 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 52654 invoked from network); 18 Apr 2007 08:55:47 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 18 Apr 2007 08:55:47 -0000 Received: (qmail 56087 invoked by uid 500); 18 Apr 2007 08:55:51 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 56060 invoked by uid 500); 18 Apr 2007 08:55:51 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Delivered-To: moderator for derby-user@db.apache.org Received: (qmail 22453 invoked by uid 99); 18 Apr 2007 08:38:39 -0000 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests=UNPARSEABLE_RELAY,UPPERCASE_25_50 X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: local policy) Date: Wed, 18 Apr 2007 10:38:09 +0200 Message-Id: <214051410@web.de> MIME-Version: 1.0 From: Sameer Deshpande To: derby-user@db.apache.org Subject: How to use bind variables with IN clause in SQL statement Organization: http://freemail.web.de/ Content-Type: text/plain; charset=iso-8859-15 Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org 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 _______________________________________________________________ SMS schreiben mit WEB.DE FreeMail - einfach, schnell und kostenguenstig. Jetzt gleich testen! http://f.web.de/?mc=021192