CFQueryParam and the "null" attribute

(Note, for the following blog entry, the system in question is Windows XP Pro, ColdFusion 7, Microsoft SQL Server 2000, and the stock ODBC SQL drivers. Your mileage may vary, and I really hope you have better luck than I did)... Earlier today I ran into a really nasty situation with the CFQueryParam tag... I've been experimenting with CFQueryParam and conditional handling of NULLs via this technique. So all of my insert and update statements look like so: <cfqueryparam cfsqltype="cf_sql_integer" value="#trim(form.iAge)#" maxlength="3" null="#yesNoFormat(NOT len(trim(form.iAge)))#" /> ...which essentially says "if the form.iAge variable is an empty string, turn it into NULL before executing this SQL statement, and if it happens to be a valid integer, trim the whitespace off and then execute the SQL. All of my SQL code is handled via DAO classes, which are auto-generated by an in-house tool I've been tweaking over the last few days. All DAOs are generated using the same tool, so any bugs related to how the DAO works should appear in either all of my classes, or none at all, right? Today I had a case where that wasn't true. The above noted null="#yesNoFormat(NOT len(trim(form.iAge)))#" code worked just fine on one table update, yet the very next page in my app was returning this error: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver]Optional feature not implemented After more debugging and yelling of profanities than I care to admit, I discovered something: the code was running fine, except on tables that included a column of type "datetime". If I so much as added a datetime column (and the corresponding code to my DAO's SQL statement), it would bomb with that error message. I've since altered my DAO Generator, so the code it produces now looks like so: <cfif StructKeyExists( arguments, "iAge" )> <cfif Len( Trim( arguments.iAge ) )> <cfqueryparam cfsqltype="cf_sql_integer" value="#trim(form.iAge)#" maxlength="3" /> <cfelse> NULL </cfif> </cfif> It's a bit more kludgey looking (IMHO), but it gets passed the error and seems to work fine (while still allowing me to use CFQueryParam to validate my SQL statements, which is really the main thing I care about. My suspicion is that this is database specific (maybe ODBC only?), as it's a pretty common practice to include some sort of "date_created" field in each table, and I haven't discovered much info on this problem via the usual searches and forums. Others have reported receiving this error, however the solution mentioned was always "switch to the JDBC driver", and had nothing to do with datetime data types. (Alas, I'm not in a position where I can switch database drivers right now without running serious risk to my app, so the aforementioned workaround will have to suffice). hth. UPDATE: I just ran into another variation of this. Apparently the database driver I'm currently using (stock w/ SQL Server 2000? i think so.) I can't even do this: <cfqueryparam cfsqltype="cf_sql_date" value="#trim(form.startDate)#" /> ...I get the SAME error as before! So it's definitely something to do with cfqueryparam trying to validate against "cf_sql_date", with certain drivers. As I get more info, I will post updates.