array binding with 'IN' statement

[Ivan Smahin]

On Jul 25, 2012, at 9:32 PM, George Parkinson wrote:

> hello all,
> i'm not sure what to make of this...
> 
> "SELECT * FROM t WHERE t.AgencyID in (996,998,1195)"
> returns all records where AgencyID one of 996, 998, or 1195...no problem here.
> 
> now, let's say i have an array->    SqlBind(1) = "996,998,1195"
> and i use this format -> 	       "SELECT * FROM t WHERE t.agencyid IN (:1)"
> 
> when i run:
> db.SqlSelect(SQLQuery, kClientSide, kNoLocks, kForwardOnly, SQLBind)
> only records where t.AgencyID = 996 are returned.
> 
> is this normal?
> can't binding be used with "IN"?
> 
> thanks,
> george
> 
> 


Yes, it is correct.
Binding is not a simple substitution like a wildcards.
And you can not pass a list of values as a single value.

There are few options:

1. Concat. the query in your app - something like this one:
	String query = "select....IN(" + valueList + ");"
	db.SqlSelect( query);

2. Binding - "IN (:1, :2, :3 )" in case you know how many items in the list.

3. In case you get the list from the database in some way you should combine both queries in a single statement:
	"select * from t1 where f1 IN (select id from t2 where....)

-- 
Best regards,
Ivan Smahin
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macserve.net/pipermail/valentina/attachments/20120726/36d2793b/attachment.html>