Help with Oracle CLOB

[Guest]

I have CF8 and Oracle 10g. I am trying to write a cfc for use by a Flex3 application over the AMF channel. What should the ReturnType be for the retieve function? If I use Query, and return the CLOB as a QueryObject, I can easily put the CLOB into a datagrid. But I really want to put the CLOB into a RichTextEdit control, allow the user to edit, and call an update function to put it back in the CLOB. I'm just testing right now. the real application will use a cfc to pull back a lot of data to populate a form that will have seven or eight CLOBs for different remark areas.
I have posted this question here because there isn't a separate forum for questions from the intersection of CF and Flex.
Possibly Acrobat forms would be a better solution than Flex for the UI, but I have no experience using Acrobat to do the data retrieve and update.
Thank you for any advice.
Scott

[Newsgroup_User · Accepted solution]

I'm not sure what you are asking for here. As far as ColdFusion and
Flex is concerned a CLOB is simply a large quantity of text. As long as
the server, client and the bandwidth in between can handle the text
size; there is nothing different with a CLOB string then the string
"Hello World" and they would be handled the same.

[Guest]

Ian,
So what you are saying is I should use String as the cfFunction ReturnType?
I was just unsure if there was any other way I should be returning it. Because I saw that, for the Update, I should use cf_sql_CLOB as the datatype.

[Newsgroup_User]

emerys wrote:
> Ian,
> So what you are saying is I should use String as the cfFunction ReturnType?
> I was just unsure if there was any other way I should be returning it. Because
> I saw that, for the Update, I should use cf_sql_CLOB as the datatype.
>

As far as I know, while to a database it is a CLOB. But to ColdFusion
and Flex it is just a really big string.

[Guest]

What confused me was that CF .cfcs are the middleman here, and just like you said you either use the CLOB or don't depending on which way you are facing. Here are two simple examples from my .cfc that work. A Retrieve function and an Update function. The description is the CLOB, but the .cfc only uses the CLOB type when it is going back into ORACLE.

<cffunction name="RetrieveDescription" access="remote" returntype="string" >
<cfargument name="PK" required="true" type="string">

<cfquery name="RetrieveDescription" datasource="dSource">
select DESCRIPTION
from DRDESCRIPTION
where CONTROLNUMBER=<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.PK#" />
</cfquery>

<cfreturn #RetrieveDescription.DESCRIPTION#/>
</cffunction>

<cffunction name="UpdateDescription" access="remote" returntype="string" >
<cfargument name="newDescription" required="true" type="string">
<cfargument name="descriptionUpPK" required="true" type="string">

<cfquery name="UpdateDescription" datasource="dSource">
update DRDESCRIPTION
set DESCRIPTION = <cfqueryparam cfsqltype="cf_sql_CLOB" value="#arguments.newDescription#" />
where CONTROLNUMBER=<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.descriptionUpPK#" />
</cfquery>
<cfset msg = 'The DR was updated.'>
<cfreturn msg/>
</cffunction>