Skip to main content
G
GregK
Participant
April 10, 2006
Question

Oracle CLOB Columns Failing With ColdFusion 7

  • April 10, 2006
  • 2 replies
  • 2198 views
I am using ColdFusion 7 Enterprise, updater 1 with DataDirect version 3.4 drivers and Oracle 8i. ColdFusion 7 is returning no data for CLOB datatype columns. The ColdFusion 5 version currently in production is successfully returning values for these columns. I have changed the datasource to allow CLOB support and increased the Long Text buffer to 256000. My largest set of data stored is 42000 bytes. I tried turning off CLOB support with the 256000 Long Text buffer and it still did not work. The only success I have had is with an Oracle package called dbms_lob. This package has a Substr( ) function which does return data but is limited to only 4000 characters at a time (varchar2). E.g. dbms_lob.substr(myCLOBColumn,4000,1). So to extract my data would require many output columns that I would then need to concatenate with ColdFusion. Concatenating with Oracle does not work since an Oracle varchar2 can return a maximum of 4000 characters. There is another DBMS_LOB function that I used to determine the true length of the data in the CLOB columns: DBMS_LOB.GETLENGTH(myCLOBColumn)

Are there any environmental, driver or other settings that may help with this problem?

Thanks,
GregK
This topic has been closed for replies.

2 replies

U
usachrisk
Participant
January 31, 2007
Having the same problem when using the JDBC drivers and trying to insert more than 1000 characters. On 1001, a blank line is inserted instead. When not using bind variables (CFQUERYPARAM), it'll accept up to 4000 characters (limitation of Oracle I'm pretty sure when not using bind variables). Using the JDBC drivers with another application allows much more than the 1000 characters that I can't seem to get through with CF7.
G
GregKAuthor
Participant
April 11, 2006
Additional information for this problem: ColdFusion is not returning an empty string for CLOB columns. Instead, it is blank- space filling the columns with the same number of characters as is contained in the column. Length of the data was determined using the function DBMS_LOB.GETLENGTH(myCLOBColumn). So a column that has 40000 non-blank characters is being returned by ColdFusion as 40000 blank spaces.
D
Drew Nathanson
Inspiring
April 13, 2006
Greg,

How did you write information to the CLOB field? I do it all the time. I use a stored procedure to write and update the information in the clob. If you need an example of the procedure, let me know (personal email) and I will send it to you. CLOB fields are tricky to use but they several a very useful purpose.
G
GregKAuthor
Participant
April 13, 2006
The data was written to the CLOB fields with SQL and <cfqueryparam> tags (no type= was specified) in ColdFusion 5. ColdFusion 7 is not retrieving the values correctly using an SQL Select statement. Unfortunately, writing stored procedures is beyond the scope of my CF5 to CF7 migration. I also discovered that ColdFusion 7 is having problems updating the CLOB columns using SQL. When I use a <cfqueryparam> tag with type=CF_SQL_CLOB or type=CF_SQL_LONGVARCHAR, the update fails with the message "No more data available to read" when attempting to Update with more than 1333 characters. The Update is successful with 1333 or less characters. When I skip using <cfqueryparam> altogether and just put the value between single quotes (e.g. '#inputValue#') I can input up to 4000 characters before it fails.

I have tried several versions of the DataDirect drivers up through version 3.5 and none seem to fix the problem. Throghout the history of ColdFusion I have always found tolerable workarounds for bugs but I am really stuck with this problem. Maybe migrating to MS SQL Server from Oracle would be the way to go.

GregK
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices