CFQUERY results return a null value for a varchar(MAX) field in SQL

[storserver]

I'm using ColdFusion 9 and SQL Server 2008.

One the fields in a table is defined as a varchar(MAX). Other fields in the table are ints, varchar(8000) varchar(256), etc.

When doing a query for records in the table and displaying the results using the cfdump tag, I see values in all fields in a row EXCEPT for the data in the varchar(MAX) field. The varchar(MAX) field shows a null value.  Yet, I can verify through the SQL interface or through Microsoft Access querying the database that the varchar(MAX) fields have data.

How can I retrieve data from this field type?

Thanks in advance for your help.

[JR__Bob__Dobbs]

Does your datasource have CLOB and BLOB options enabled?

See:

http://help.adobe.com/en_US/ColdFusion/9.0/Admin/WSc3ff6d0ea77859461172e0811cbf364104-7fe5.html

[storserver]

Sadly, that didn't fix it. I went to ColdFusion Administrator, Data Sources and clicked on Advanced Settings. For the first time, I added

checks to the CLOB and BLOB settings, saved the settings and double checked that checkmarks remained. Even after rebooting the

server, however, when I use the <CFQUERY> tag to return records for the table with the varchar(MAX) in it, all fields except the

varchar(MAX) contain data. The value for the varchar(MAX) is still [empty string].

This datasource uses an ODBC driver. Could that cause the problem? I use the same ODBC driver to retrieve data from the table into

Microsoft Access. Access queries retrieve the information correctly.

[JR__Bob__Dobbs]

Have you tried using the JDBC driver bundled with ColdFusion? Use of JDBC is generally recommended over ODBC.

[storserver]

I'm not sure what you mean by using the JDBC driver.

In the past, to create a datasource used by ColdFusion, I've followed two steps.

     1. Go to Control Panel/Administrative Tools/Data Bases (ODBC). Click on the System DSN tab. Create a datasource there using the SQL Server driver.

     2. Browse to the ColdFusion Administrator/Data Sources/Add Datasource. Choose the ODBC socket as the driver type when creating the datasource.

The process is incorrect, right? So,

     a. Do I even create a datasource on the local machine outside of CF Administrator as described in item 1 above? I only see ODBC as a datasource entry point there.

     b. Within ColdFusion Administrator's drop down menu of driver types, I don't see anything named JDBC.

This may seem obvious to most people. I apologize for my lack of understanding.

[JR__Bob__Dobbs]

The drivers bundled with CF are JDBC drivers.  Using these is generally preferable to the ODBC socktet driver.  The CF documentation states, about using ODBC: "Adobe does not recommend this driver type unless your application requires DBMS-specific features."

Instructions for creating a database connection (JDBC) to SQL Server can be found in the CF documentation.  Your step 1 is not required when using JDBC.

References:

"About JDBC"
http://help.adobe.com/en_US/ColdFusion/9.0/Admin/WSc3ff6d0ea77859461172e0811cbf3639b1-7ffd.html

"Connecting to Microsoft SQL Server"
http://help.adobe.com/en_US/ColdFusion/9.0/Admin/WSc3ff6d0ea77859461172e0811cbf364104-7fe5.html

[storserver]

Your message was very helpful. The driver change fixed the query results problem. Do you want me to try to explain this in the forum message? I'm not sure it would be useful for anyone to read as it is. Or, should I delete the thread?

Thank you so very much.

[JR__Bob__Dobbs]

I would describe the steps that fixed your problem here in the forum in so it can help someone else with a similar problem.

[storserver]

I'm wrong. I AM getting query results, but not what I expect. I had maxrows set as a variable in my original query, so when it returned 5 rows, the first row always showed as [empty string]. I've tried numerous queries now (where UID >100, where UID >200, where UID> 300, etc.) When I use <CFDUMP> to return the query, the first records always shows empty strings.

Here's an example of some records retrieved from this simple query:

<CFQUERY NAME="BuildBack" DATASOURCE="STORBASE">
SELECT *

FROM CallUnit

WHERE CCU_ID>172

</CFQUERY>

Any ideas?

173	P	tuse	322	340
174	E		323	340
175	P	tuses.	324	341
176	E		325	341
177	P	tuses.SCStat_ID) á àh\  (9~ Hbb l_Products ON SupCall_MetaData.Product = SupCall_Products.SCPL_ID) LEFT JOIN SupCall_Priorities ON SupCall_MetaData.Priority = SupC	326	342
178	P	tuses.SCStat_ID) á àh\  (9~ Hbb l_Products ON SupCall_MetaData.Product = SupCall_Products.SCPL_ID) LEFT JOIN SupCall_Priorities ON SupCall_MetaData.Priority = SupCa	327	343
179	E		328	344
180	E		329	345
181	P	tuses.SCStat_ID) á àh\  (9~ Hbb l_Products ON SupCall_MetaData.Product = SupCall_Products.SCPL_ID) LEFT JOIN SupCall_Priorities ON SupCall_MetaData.Priority = SupCall_	330	346
182	P		331	347
183	E		332	348
184	P		333	349
185	P	tuses.SCStat_ID)	334	350
186	P	tuses.SCStat_ID) á àh\  (9~ Hbb l_Products ON SupCall_MetaData.Product = SupCall_Products.SCPL_ID) LEFT JOIN SupCall_Priorities ON SupCall_MetaData.Priority = SupCall_Prior	335	352
187	E		336	353
188	P		337	354
189	P	tuses.SCStat_ID) á àh\  (9~ Hbb l_Products ON SupCall_MetaData.Product = SupCall_Products.SCPL_ID) LEFT JOIN SupCall_Priorities ON SupCall_MetaData.Priority = SupCall_Priorities.SCPriority_ID) LEFT JOIN SupCall_CommTypes ON SupCall_MetaData.CommunicationType = SupCall_CommTypes.SCComm_ID) INNER JOIN SupCall_CallStages ON SupCall_	338	355
190	P	tuses.SCStat_ID) á àh\  (9~ Hbb l_Products ON SupCall_MetaData.Product = SupCall_Products.SCPL_ID) LEFT JOIN SupCall_Priorities ON SupCall_MetaData.Priority = SupCall_Priorities.SCPriority_ID) LEFT JOIN SupCall_CommTypes ON SupCall_MetaData.CommunicationType = SupCall_CommTypes.SCComm_ID) INNER JOIN SupCall_CallStages ON SupCall_M	339	356
191	P	tuses.SCStat_ID) á àh\  (9~ Hbb l_Products ON SupCall_MetaData.Product = SupCall_Products.SCPL_ID) LEFT JOIN SupCall_Priorities ON SupCall_	340	357
192	P	tuses.SCStat_ID) á àh\  (9~ Hbb l_Products ON SupCall_MetaData.Product = SupCall_Products.SCPL_ID) LEFT JOIN SupCall_Priorities ON SupCall_MetaData.Priority = SupCall_Priorities.SCPriority_ID) LEFT JOIN SupCall_CommTypes ON SupCall_MetaData.CommunicationType = SupCall_CommTypes.SCComm_ID) INNER JOIN SupCall_CallSta	341	358
193	P	tuses.SCStat_ID) á àh\