Inspiring

Question

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

Forum|Forum|14 years ago
September 8, 2011
3 replies
2733 views

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.

Database access

This topic has been closed for replies.

S

storserverAuthor

Inspiring

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\

S

storserverAuthor

Inspiring

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.

J

JR__Bob__Dobbs

Inspiring

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

S

storserverAuthor

Inspiring

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.

J

JR__Bob__Dobbs

Inspiring

Does your datasource have CLOB and BLOB options enabled?

See:

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded