3 Replies Latest reply on May 22, 2014 4:25 PM by Carl Von Stetten

    limitation cf_sql_varchar length

    iccsi Level 1

      I have following cffunction in my cfc and calling from jQuery.

      I use MS SQL Server 2008 R2 and the field is nvarchar(max) in the database.

       

      The function fails to update when the string more than 800 characters.

      I would like to know are there length limitation for cf_sql_varchar?

       

      I tried to changed to CF_SQL_LONGVARCHAR, but it still fails when the text longer than 800 characters.

      My users need to enter more than 800 characters.

       

      Another issue is when the text has '%' then it wipe out all text and pass null to the MS SQL Server.

       

      Any suggestion is appreciated,

       

       

      Iccsi,

       

      <cffunction name="Myfunction" access="remote">

         <cfargument name ="MyField" required="no" default="" hint="My Field">

       

          <cfstoredproc procedure = "MySP">

              <cfprocparam value = "#MyField#" CFSQLTYPE = "cf_sql_varchar">

      </cfstoredproc>

      </cffunction>

        • 1. Re: limitation cf_sql_varchar length
          Carl Von Stetten Adobe Community Professional & MVP

          isccsi,

           

          Check the datasource settings in CF Administrator.  Make sure CLOB or "Enable long text retrieval (CLOB)" is checked.

           

          -Carl V.

          • 2. Re: limitation cf_sql_varchar length
            Jamo Level 2

            How are you "calling from jQuery"?  Is it a form "post"?  (Do not use "get".)


            Which version of ColdFusion are you using?  (I ask because some newer security features have been added in newer versions of ColdFusion.)


            When posting to a varchar(max) field, be sure to use CF_SQL_LONGVARCHAR.

             

            Do you have any ColdFusion features running that would sanitize the submitted content?  Depending upon the type of content being submitted, you may need to disable scriptprotect or some of it may be removed or altered.

            • 3. Re: limitation cf_sql_varchar length
              Carl Von Stetten Adobe Community Professional & MVP

              @Jamo - Good points! 

               

              @isccsi, have you checked the AJAX data that jQuery is sending to ColdFusion using your browser's developer tools or a sniffer like Fiddler or Charles?  Make sure that your jQuery code isn't truncating the data before it gets sent to ColdFusion.  Once you rule that out, maybe log the length of the value arriving in the MyField argument to see if it is arriving intact to the function.

               

              -Carl V.