4 Replies Latest reply on Feb 6, 2009 6:09 AM by emerys

    Help with Oracle CLOB

      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
        • 1. Re: Help with Oracle CLOB
          Level 7

          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.

          • 2. Re: Help with Oracle CLOB
            Level 1
            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.
            • 3. Re: Help with Oracle CLOB
              Level 7
              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.

              • 4. Re: Help with Oracle CLOB
                Level 1
                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>