ColdFusion Or MS-SQL Returning [Empty String] Value For NTEXT
Oh MicroSoft, why must you be the bane of my existence? Seriously, if it's not your buggy web browsers, then it's using another one of your products. And this time it's MS-SQL. Ok, in all seriousness, I'm really not 100% sure if this is MicroSoft's fault or perhaps Adobe's ColdFusion. But either way this one through me for a loop. (And on a side note, why does it seem like this crap always happens on a Monday)
If you have been forced to use MS-SQL on one of your projects. And perhaps you've been banging your head up against your computer monitor for the past three hour, because you've been trying to pull data from an
NTEXT column and it keeps coming back as an
[empty string], then boy do I have a solution for you.
First, let's take a look at the problem. Basically I had a database table that had a
NTEXT column called "test_data". I was using it to store JSON test result data for employees. I needed to create a report that would pull this data and then allow an administrator to see how their employees did on their test. It was a pretty simple query, nothing fancy at all. But the "test_data" column kept coming back empty in my queries.
<cfquery name="test1" datasource="MyDSN"> SELECT id, user_id, test_data, is_complete FROM test_scores WHERE id = 33 </cfquery>
cfdump, here's the results I received.
As you can see, the "test_data" field says
[empty String]. The problem is, that its not empty. I know this because I made a another simple query that checked the data length of the column and returned the top results. This query just had the "test_data" field, nothing else... this is when I started to see what was going on.
Apparently, MS-SQL (or ColdFusion, the jury is still out on this one) has a problem with the ORDER that you place your column names in your SQL query. That's right, you heard me correctly. When I did my simple query, I was able to pull data. It only came back empty when I added additional column names. So here's my solution...
<cfquery name="test2" datasource="MyDSN"> SELECT id, user_id, is_complete, test_data FROM test_scores WHERE id = 33 </cfquery>
... and the results...
Notice how I placed the "test_data" at the end of the
SELECT statement? That's all it took. Three hours wasted. Kudos, MicroSoft or Adobe, right when I was starting to believe that you guys couldn't suck anymore, you go and lower the bar just a tad bit more. I tip my hat to you sirs.