The more I am noticing the need to support a null value and I am trying not to go in and write work arounds for each bean / dao / gateway CFCs to support nulls.
So, would defining a default bogus null value per bean be the way to go?
Example:
<cfset variables.NumericNull = -99999 />
<cfset variables.StringNull = "null" />
<cfset variables.DateNull = CreateDate(1877,01,01) />
<cfset variables.BinaryNull = toBinary(toBase64("null")) />
Now in the DAOs have something like:
<cfargument name="myBean" type="myBean" required="true" />
<cfset var myBeanUpdate = 0 >
<cfset var numericNull = arguments.myBean.getNumericNull() />
<cfset var DateNull = arguments.myBean.getDateNull() />
<cfset var StringNull = arguments.myBean.getStringNull() />
<cfset var BinaryNull = arguments.myBean.getBinaryNull() />
<cfquery name="myBeanUpdate" datasource="#variables.dsn#" >
UPDATE myBean
SET
myBeanValue1 =
<cfif arguments.myBean.getmyBeanValue1() NEQ stringNull>
<cfqueryparam cfsqltype="cf_sql_vchar" value="#arguments.myBean.getmyBeanValue1()#" maxlength="10" />
<cfelse>
<cfqueryparam cfsqltype="cf_sql_vchar" null="yes" />
</cfif>
WHERE
id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.myBean.getmyBeanID()#" />
</cfquery>
</cffunction>
I uploaded new transforms to the demo CFC Creator script to reflect these changes if you want to see how a fully generated CFC looks using the above.
Thoughts?
Think I should use the new transforms in the downloadable version of CFC Creator?
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.
I think adding the null support to the bean object would be nice.... add an optional second parameter to the set methods, isnull which defaults to false
and then rewriting the cfqueryparam's so there is only one and the null="arguments.myBean.isNullValue1()"
this is more explict as CF doesn't have nulls and different db's treat null differently.. ms sql treats an empty string as null unlike oracle which treats the two differently
But the above changes really makes the code cleaner.
Take a look at the demo to see the changes.
I just found out about this the other day... I am gonna have to load up a version of MS SQL and try to figure out what is going on.
For now, a Dany Lamberge gave me a workaround... Around line 160 in table.cfc, change to: <!--- <cfif isnumeric(indexqry.non_unique)> ---> <cfset comparestrprimary = "'0'"> <cfset comparestrnonprimary = "'1'"> <!--- <cfelse> <cfset comparestrprimary = "'false'"> <cfset comparestrnonprimary = "'true'"> </cfif> ---> Notice the 0 and 1 have a single quote around them.
It appears the JDBC driver returns a string of 0 or 1 for MS SQL instead of a number.
Would also love to hear a suggestion for a fix for this without having to check by database type.