2

Is there a character limit on cf_sql_longvarchar that I can increase, or perhaps an alternative? It seems to be cut off at 43,679 characters or somewhere around 40-45 kb.

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
Patrick Schomburg
  • 2,494
  • 1
  • 18
  • 46
  • 1
    There is a limit, but it is pretty large, way more than KB. Most likely the problem is your datasource settings. If you search the archives, there are several threads mentioning the DSN settings, ie [CLOB - Enable long text retrieval](http://stackoverflow.com/questions/9021508/grabbing-long-text-from-sql-database-with-coldfusion-output-truncated). If that does not help, please update the question with more details: a) dbms b) column data type and c) relevant query code. – Leigh May 04 '16 at 16:18
  • 1
    Are you confirming this by reviewing the size within SQL using datalength()? Or are you returning the value and it's truncated? If returning a truncated value, you may just need to "Enable long text retrieval" on the datasource within the ColdFusion CFAdmin. – James Moberg May 04 '16 at 16:21
  • It was the setting, but it is also getting truncated in Microsoft SQL server management studio. Is there a setting for that as well? – Patrick Schomburg May 04 '16 at 16:23
  • @PatrickSchomburg - Yes, but I do not remember it off the top of my head. Do a search on something like SSMS text truncated and it should turn up the exact settings you need to change. – Leigh May 04 '16 at 16:46

1 Answers1

8

Usually a combination of CLOB and an Long Text Buffer solve the problem.

I use the example below only for queries that I know have to move lots of data. I don't use it universally because then JVM memory issues can come up.

enter image description here

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
  • (Edit) I have not thought about it in a while, but I always thought CLOB/BLOB and Buffer were mutually exclusive, and that if CLOB/BLOB was enabled, the Buffer sizes were ignored. – Leigh May 04 '16 at 19:07
  • I just reviewed my DSN settings, most of them do not use CLOB/BLOB. You may have a point. – James A Mohler May 04 '16 at 19:17
  • Well, I will have to test it out later and see if my memory is faulty or not ;-) – Leigh May 04 '16 at 20:55
  • 1
    Just verified it. The CLOB/BLOB setting does override the Buffer size. If CLOB/BLOB are disabled, the maximum returned is the Buffer size value. If enabled, the Buffer size is ignored. – Leigh May 06 '16 at 04:23