This is probably an old issue, but I thought I'd share it. I'm writing code to support exporting BlogCFC data into WDDX packets. The flip side of this is code to import the WDDX packets.
The export works just fine - but I ran into a weird issue when importing. I have some columns that are BIT type and allow nulls. When doing the database inserts, I got error saying it couldn't convert the bit value to a null.
Well, I figured - no big deal - I'll check to see if my value is boolean, and if not, I'll use null="true" in my cfqueryparam.
But get this - the CFIF threw an error! That was insane. How can I check if X is a boolean if the actual check throws 'X is not boolean' itself!
I ended up having to use try/catch to rewrite the data:
<cftry>
<cfif not isBoolean(moderated)>
<!--- nothing --->
</cfif>
<cfcatch>
<cfset querySetCell(data, "moderated", "", currentRow)>
</cfcatch>
</cftry>
Then I do this in my query:
<cfif not isBoolean(moderated)>
<cfqueryparam cfsqltype="cf_sql_bit" null="true">
<cfelse>
<cfqueryparam cfsqltype="cf_sql_bit" value="#moderated#">
</cfif>
For a simple test script (and this is what I'll provide to Adobe), create a table with two columns, name and cool, where cool is a bit. Then insert a few rows and make cool null for one row. Then run this:
<cfquery name="getit" datasource="test">
select *
from test
</cfquery>
<cfdump var="#getit#">
<cfwddx action="cfml2wddx" input="#getit#" output="packet">
<cfoutput>#htmlcodeformat(packet)#</cfoutput>
<cfwddx action="wddx2cfml" input="#packet#" output="gigo">
<cfoutput query="gigo">
#name#
<cfif isBoolean(cool)>
#yesnoformat(cool)#
</cfif>
<br>
</cfoutput>
You will then get:
cannot convert the value "''" to a boolean
As a side note - it also appears to apply to ints as well. I wonder if it is an issue with any non-varchar column?
Archived Comments
Isn't this more a problem with how the data is inserted in to the db in the first place? If you insert a null value into a boolean field then CF will bomb out because, obviously, null is not true or false. I've run across this a few times while working on other programmers code and some error trapping before or during the data insert keeps this from happening.
No - a column can be boolean and allow nulls. That's why I wrote code to say, 'if the value isn't boolean, insert null'. The problem was that CF barfed on the cfif itself. It also barfs if you just output it. The issue seems to be in the conversion from wddx to cf.
Ray,
I come across this a lot, Usually recommend using
<cfif YesNoFormat()>
</cfif>
Thanks
-Hem
Guys - I don't think yall get it. Maybe my post wasn't clear.
The data is corrupt. If you do ANYTHING with it - CF throws an error.
If I just do this:
<cfloop query="fromwddx">
#foo#
</cfloop>
I will get the error. I can't CFIF it. Just using the variable throws an error.
Make sense now?
I get that but aren't you just creating more work for yourself? As you explained it, the CFIF barfed because it couldn't convert a null value to a boolean, which is true. I've done that myself a few times. The fix is to make sure the field does have a true/false value before it hits the CFIF. CFIF will always choke in this scenario because, again, null is not true or false. Telling the query to accept nulls is only compounding the problem, IMHO.
Sorry, I don't mean to rant. This is a problem with the data, not CFIF. That worked the way it's supposed to. It just seems that a little data checking first would have prevented the problem altogether.
In this case, I have no control over the data. It is what it is. But consider this Chris - CF has no problem selecting data from a query with nulls. If you output it, you get an empty string. You can easily do isBoolean.
The only issue is in the CF to WDDX and WDDX to CF conversion. That is where it breaks down. THe resultant data is so corrupt you can't touch it w/o a try/catch.
As for the data - well, if a field is optional and boolean, null seems like a reasonable value.
Ray,
I would agree that this appears to be a problem with cfwddx Interestingly enough, in your example, if you change the initial cfquery to
<cfquery name="getit" datasource="test">
select *
from test
order by cool desc
</cfquery>
It works fine. In other words, if the WDDX, your nulls appear first, the conversion back works fine. Otherwise you get the error. I'm assuming that this won't help you, however since you stated that you have no control over the data coming in. I also tried manually creating a query (queryNew, querySetCell, etc...) and was unable to replicate the behavior that is occurring with cfwddx.
Hmm. When CF creates the WDDX, I believe it checks the column types. If null is first, I wonder if it considers the column to be... I don't know what. :) Try this - wddxify both versions and tell me what the wddx looks like (I could do this - just busy)
I think the comments seem to be missing the boat - this isn't necessarily (though it is specifically) about a NULL in the database - it is related to how ColdFusion actually ends up mapping a NULL from Java into the CF world... two pieces of interesting evidence culled from memory so I don't have exactly the right CF code perhaps:
1) I was writing some .NET web services that CF was consuming. Most of them returned strings and as an error condition I made the design decision to return NULL to indicate the operation had failed - good or bad decision, it doesn't really matter - and what happened was when I called <cfinvoke> and got the NULL back, it was AS IF THE CODE NEVER EXECUTED - my variable was not set, it didn't even exist in the scope. Very strange behavior I thought.
2) So I decided to duplicate the "bug" a little differently. I created a Java object and called some method that returned NULL. Again, the same behavior: It was like that line of code had never executed. The variable I was trying to assign had no value, and didn't even exist in the scope.
Now granted, NULL as a SELECT in <cfquery> tends to effectively come out as an empty string, so that to me is a special case - but - it would appear from my limited investigation that ColdFusion doesn't really have a concept of NULL, or at least the concept is to not define the variable. So if you might be dealing with an actual NULL, testing for the existence of your variable would be the correct course of action to detect NULL.
I hope this helps, or perhaps someone else can correct me? I never really chased this issue very far and just changed my error state to something more meaningful to just avoid the issue completely. I would love to be more deeply educated in this.
This seems a lot like the data type conversion problem in query of queries. I have run into it before, but Ben Nadel has the best explanation of the issue that I have seen:
http://www.bennadel.com/blo...
I didn't read any of the other data but wtf are nulls doing in a bit field anyways??? Boolean/bit is either true or it's false there no such thing as "unsure" - you are either male or female, dead/alive etc - nulls in DB are lame...
The only time when you should actualy need them is in some sort of wizard or something where you may commit the data unknowing a user's answer/result if they haven't completed all questions or fields, where is maybe not suitable to insert something else for them as it would not be right.
What about a case where gender is an optional field?
Anyway - this blog entry is REALLY not about proper DB practices, but a bug w/ cfwddx. Can we focus on that please?
I've had an issue with this. I just got frustrated and did exactly what you did with the try/catch. I thought it was something I was doing wrong but couldn't figure it out. Hope this gets fixed!