On 10/4/05, Deanna Schneider <[EMAIL PROTECTED]> wrote: > You know, I've been doing this CF/Oracle stuff for a long time, and I just > figured out something I'd not figured out before. You can't use the null > attribute of a cfqueryparam with a list with Oracle (at least not with our > drivers). I don't know about other dbs. In sql plus it's perfectly > acceptable to do: > > SELECT mycolumn > FROM mytable > WHERE id IN (null) > > But, if you do this in CF: > > SELECT mycolumn > FROM mytable > WHERE id IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#myvalue#" > list="yes" null="yes">) > > You'll get an error that says: [Macromedia][Oracle JDBC > Driver][Oracle]ORA-00936: missing expression. > > But, you can run the query in CF without the queryparam, passing in null and > it works just fine. This seems like a bug to me. Can anyone verify? > > We're on CF 6.1 still - so maybe this is fixed in 7? I tested in Oracle 8i > and 10g, with no difference. > > Here's a sample workaround, btw - which I find preferable to just not using > cfqueryparam: > WHERE a.productid <cfif listlen(client.productcart)>IN (<cfqueryparam > cfsqltype="cf_sql_varchar" value="#client.productcart#" list="yes">)<cfelse> > IS NULL</cfif> >
Actually, the bug (which is in both MX 6.1 and MX 7.0.1) isn't with the "null" attribute setting. The problem is if you pass in a blank string to the "value" attribute when you specify list="true". If you put in a dummy value for the "value" attribute while specifying list="true" and null="true", then you'll get back an empty record set, as you'd expect: <cfquery name="get" datasource="scott"> SELECT * FROM emp WHERE empno IN (<cfqueryparam value="7369" cfsqltype="cf_sql_numeric" list="true" null="true" />) </cfquery> <cfdump var="#get#" /> Nice find though. Regards, Dave. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220016 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
