tracker issue : CF-3040214

select a category, or use search below
(searches all categories and all time range)
Title:

Bug 80196:An empty list passed to <cfqueryparam cfsqlype="cf_sql_integer" value="#list#" list="true"/> causes an exception to be thrown (because "" is not a valid integer)

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/

Reporter/Name(from Bugbase): Sean Corfield / Sean Corfield (Sean Corfield)

Created: 09/26/2009

Components: Database, General

Versions: 9.0

Failure Type: Unspecified

Found In Build/Fixed In Build: 9,0,0,251028 / 261107

Priority/Frequency: Minor / Unknown

Locale/System: English / Platforms All

Vote Count: 1

Problem:

An empty list passed to <cfqueryparam cfsqlype="cf_sql_integer" value="#list#" list="true"/> causes an exception to be thrown (because "" is not a valid integer). This is plain silly! WHERE x in ( <cfqueryparam cfsqltype="cf_sql_integer" value="#emptylist#" list="true"/> ) should work and yield WHERE x in ( ) which is valid SQL and matches no rows.
Method:

See code example above. You end up having to test trim(list) is not "" and conditionally doing something different in your query which is a mess.
Result:

'' invalid CF_SQL_INTEGER value (or something like that)

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3040214

External Customer Info:
External Company:  
External Customer Name: Sean Corfield
External Customer Email: 479B4EDC43F3A88B992016B6
External Test Config: 09/26/2009

Attachments:

Comments:

-1 vote. Disagree that there is a problem here. WHERE col IN () ain't valid SQL where I come from. It doesn't work on SQL Server 2008 or OracleXE (the freebie one based on 10g, I think). And from memory it doesn't work on SQL Server 7.0, 2000, 2005 or Oracle 8i or 9i either. I know this because it's the sort of thing I've had developers muck up in the past if they don't check to see if the list has any elements before passing it. "A trap for young players", one could say. Which SQL system does this work on? Maybe it's fine in the SQL spec but just not really supported? -- Adam
Vote by External U.
22794 | November 11, 2011 12:37:29 AM GMT