4

I have a MySQL database with a column datatyppe LONGTEXT. It has in the record 85.504 characters. When I do a select using coldfusion query, it only return the first 64,001 characters. Is ColdFusion limited to 64,001 characters?

selectTest = new query();
selectTest.setDatasource("myDB");
selectTest.setName("selectTest");
selectTest.addParam(name="MyID",            value="#arguments.data.MyID#",          cfsqltype="cf_sql_integer");
result = selectTest.execute(sql="SELECT MyLongText FROM MyTable WHERE ID = :MyID");
rs = result.getResult();
return rs.MyLongText[1];

This is my function where only return the first 64,001 characters.

Thanks

Code Guy
  • 105
  • 1
  • 7
  • Are you sure it's `LONGTEXT` and not `TEXT` by accident? That one is much more limited. 64,001 is an odd value, I'd expect 65,535 instead. Does ColdFusion have POST limits? – tadman Nov 12 '18 at 21:36
  • 1
    Possible duplicate of [retreving long text (CLOB) using CFQuery](https://stackoverflow.com/questions/5075215/retreving-long-text-clob-using-cfquery) – SOS Nov 12 '18 at 21:45
  • Yes, it is LONGTEXT. When I select data from MySQL Workbench, it returns everything, just in the ColdFusion SQL that returns only 64,001 characters – Code Guy Nov 12 '18 at 22:07
  • Yes, whether or not you aware of it, you are talking about CLOB. What happened when you tried it? – SOS Nov 12 '18 at 22:31

1 Answers1

9

ColdFusion uses JDBC drivers which buffer data retrieved from the SQL server. This buffer is limited to 64k bytes by default. If you need to retrieve larger data chunks, you have to increase the buffer.

Increasing the buffer can be done either by allowing full CLOB/BLOB retrieval (hint: LONGTEXT is considered as such) or by increasing the maximum amount of bytes in the buffer by manually setting a higher value.

Both things can be done in ColdFusion's datasource management (administrator panel).

Edit: The link posted by Ageax tells you the steps to enable full CLOB retrieval: Retrieving long text (CLOB) using CFQuery

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alex
  • 7,743
  • 1
  • 18
  • 38
  • I have pushed it to 1MB using this approach. BUT I had to create a separate datasource just for that. I started getting weird errors if I had every query do that. – James A Mohler Nov 13 '18 at 03:03
  • @JamesAMohler what errors were you getting? Wondering if I have a related issue... – beloitdavisja Nov 13 '18 at 13:31
  • It would be a general slowness. Timeouts would occur. The system would not hold up under stress. Keep in mind this was a while back on CF 9. 90% of the code could use the regular datasource. That was about 99.9% of the traffic. I think CF works this way. When you run CFQuery, memory is allocated before the results come. If the results fit, all is good. If the results don't fit weird errors come up. But if you keep increasing the memory for queries, they lots of memory gets allocated. CF seemed to allocate faster than it could release. IF things are set wrong. – James A Mohler Nov 14 '18 at 01:25
  • Unless you are pushing large amounts of data, 64k is just fine. – James A Mohler Nov 14 '18 at 01:27