tracker issue : CF-3043836

Problem:

Addressing the difficulties of passing a NULL value from cold fusion to a Database !How Cold Fusion could handle the passing of NULL values to a DataBase.IMHO it could be so much cleaner.  Instead we go through torture to achieve this.  I prefer to use <cfstored proc for a whole lot of reasons, one of which is it provides for independent testing of SQL and the page/CFC that calls it etc. (another is isolation and security)As we all know Cold Fusion does not provide an explicit state or value for a variable that is equivalent to the 'NULL' in databases,or for instance equivalent to the javascript NaN (not a number) !In a <cfproc structure one needs to test if each field isDefined (assuming null is valid in the context). This is the ONLY possible way to handle the passing of NULL values in this context.  Using any (other) value presents problems.So instead of writing this ..... which I loath, especially when there are say 20 parameters to pass...<cfif isDefined("arguments.productID")>  <cfprocparam type="in" value="#arguments.productID#" dbVarName="@productID" CFSQLType="cf_sql_integer"  /><cfelse><cfprocparam null="yes" dbVarName="@productID" CFSQLType="cf_sql_integer" /></cfif>It should be possible to write this ...<cfprocparam value="#arguments.productID#" CFSQLType="cf_sql_integer" ....................dbVarName="@productID" />AND rely on the <cfprocparam tag ITSELF to test and determine whether the field is defined and if not, pass a null string as the parameter.Furthermore the <cfprocparam tag should be enhanced with a new attribute called 'allowNull' (Yes/no) to accept or throw an error should the field be undefined, as the case may be.   The existing attribute null="yes" should be deprecated !FootnoteYes I realise that the CF parser/interpreter evaluates the #arguments.field# first, and would be bound to throw an error probably long before the <cfprocparam tag interpretation is done.  While this may present a problem, it is not insurmountable.  Perhaps the hashes ## could be omitted to delay evaluation, OR a new 'delay' function used as wrapper for the content similar to DE() e.g. <cfprocparam value="#isDefined(arguments.productID)#" allowNull="yes"...   OR  <cfprocparam value="#ifExists(arguments.productID)#"  allowNull="no"... It would make such a difference to have neat, tidy and readable code for such a central task - interface between CF and DB.Please show your support for this long overdue cleanup. =============================================================Is this how you would like to see your code ???<cfstoredproc procedure="" datasource="" debug="" returnCode="yes"><cfprocparam type="in" value="#arguments.s_supplier_data.isActive#" dbVarName="@isActive" CFSQLType="cf_sql_bit"  /><cfprocparam type="in" value="#arguments.s_supplier_data.company#" dbVarName="@company" CFSQLType="cf_sql_varchar" maxLength="100" /><cfif isDefined("arguments.s_supplier_data.phone")> <cfprocparam type="in" value="#arguments.s_supplier_data.phone#" dbVarName="@phone" CFSQLType="cf_sql_varchar" maxLength="50" /><cfelse><cfprocparam null="yes" dbVarName="@phone" CFSQLType="cf_sql_varchar" /></cfif><cfif isDefined("arguments.s_supplier_data.email")> <cfprocparam type="in" value="#arguments.s_supplier_data.email#" dbVarName="@email" CFSQLType="cf_sql_varchar" maxLength="200" /><cfelse><cfprocparam null="yes" dbVarName="@email" CFSQLType="cf_sql_varchar" /></cfif><cfif isDefined("arguments.s_supplier_data.emailText")> <cfprocparam type="in" value="#arguments.s_supplier_data.emailText#" dbVarName="@emailText" CFSQLType="cf_sql_varchar" maxLength="4000" /><cfelse><cfprocparam null="yes" dbVarName="@emailText" CFSQLType="cf_sql_varchar" /></cfif><cfif isDefined("arguments.s_supplier_data.bookingsEmail")> <cfprocparam type="in" value="#arguments.s_supplier_data.bookingsEmail#" dbVarName="@bookingsEmail" CFSQLType="cf_sql_varchar" maxLength="200" /><cfelse><cfprocparam null="yes" dbVarName="@bookingsEmail" CFSQLType="cf_sql_varchar" /></cfif><cfif isDefined("arguments.s_supplier_data.accountsEmail")> <cfprocparam type="in" value="#arguments.s_supplier_data.accountsEmail#" dbVarName="@accountsEmail" CFSQLType="cf_sql_varchar" maxLength="200" /><cfelse><cfprocparam null="yes" dbVarName="@accountsEmail" CFSQLType="cf_sql_varchar" /></cfif><cfif isDefined("arguments.s_supplier_data.fax")> <cfprocparam type="in" value="#arguments.s_supplier_data.fax#" dbVarName="@fax" CFSQLType="cf_sql_varchar" maxLength="50" /><cfelse><cfprocparam null="yes" dbVarName="@fax" CFSQLType="cf_sql_varchar" /></cfif><cfif isDefined("arguments.s_supplier_data.abnNumber")> <cfprocparam type="in" value="#arguments.s_supplier_data.abnNumber#" dbVarName="@abnNumber" CFSQLType="cf_sql_varchar" maxLength="100" /><cfelse><cfprocparam null="yes" dbVarName="@abnNumber" CFSQLType="cf_sql_varchar" /></cfif><cfif isDefined("arguments.s_supplier_data.bsbNumber")> <cfprocparam type="in" value="#arguments.s_supplier_data.bsbNumber#" dbVarName="@bsbNumber" CFSQLType="cf_sql_varchar" maxLength="100" /><cfelse><cfprocparam null="yes" dbVarName="@bsbNumber" CFSQLType="cf_sql_varchar" /></cfif><cfprocparam type="in" value="#arguments.s_supplier_data.accountName#" dbVarName="@accountName" CFSQLType="cf_sql_varchar" maxLength="100" /><cfprocparam type="in" value="#arguments.s_supplier_data.accountNumber#" dbVarName="@accountNumber" CFSQLType="cf_sql_varchar" maxLength="200" /><cfif isDefined("arguments.s_supplier_data.street")> <cfprocparam type="in" value="#arguments.s_supplier_data.street#" dbVarName="@street" CFSQLType="cf_sql_varchar" maxLength="100" /><cfelse><cfprocparam null="yes" dbVarName="@street" CFSQLType="cf_sql_varchar" /></cfif><cfif isDefined("arguments.s_supplier_data.state")> <cfprocparam type="in" value="#arguments.s_supplier_data.state#" dbVarName="@state" CFSQLType="cf_sql_varchar" maxLength="25" /><cfelse><cfprocparam null="yes" dbVarName="@state" CFSQLType="cf_sql_varchar" /></cfif><cfif isDefined("arguments.s_supplier_data.suburb")> <cfprocparam type="in" value="#arguments.s_supplier_data.suburb#" dbVarName="@suburb" CFSQLType="cf_sql_varchar" maxLength="100" /><cfelse><cfprocparam null="yes" dbVarName="@suburb" CFSQLType="cf_sql_varchar" /></cfif><cfprocresult name="result_1" resultset="1" /></cfstoredproc>
Method:


Result:

none

Attachments:

Comments: