Oracle CLOB Columns Failing With ColdFusion 7

[GregK]

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

[GregK]

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.

[drew222]

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.

[GregK]

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

[drew222]

You are correct in that it may be a driver issue. I would change your driver to
the Oracle drivers. If you are running Cold Fusion MX 7, you can define the
Datasource information as OTHER and load the Oracle information from there. I
have En terprise for my development machine and using the Merchant driver but
you can also use the JDBC driver from ORacle. Let me know if you need the
information as to where to find the driver.

As for moving to SQL Server, that would be a bad idea in my mind. Oracle is
far more powerful than MS SQL (in my humble opinion).