Retrieving long text (CLOB) using CFQuery

Question

I am using CFQuery to retrieve the CLOB field from Oracle DB. If the CLOB filed contains the Data less than ~ 8000, then I can see <CFQuery > retrieved the value (<cfdump> the o/p), however If the value in CLOB field size is more than 8000 chars, then its not retrieving the value. in <cfdump> i can see the query retrieved as 'empty String' though the value exists in Oracle DB.

I am using the Oracle Driver in CFadim console, enabled 'Enable long text retrieval (CLOB).' and 'Enable binary large object retrieval (BLOB). '

Set 'Long Text Buffer (chr)' and 'Blob Buffer(bytes) ' values to 6400000

Any suggestions to retrieve the full text?

Ribonuclease

Are you sure it is set to 6,400,000 and not the default 64,000? If it was 64,000 I would have figured that the 8000 characters = 64,000 bytes. Either way, does increasing the value to a larger size and restarting the services make a difference? – Pedropedrotti

Answer 1

Did you enable CLOB fields in the datasource configuration? If not make sure you do.

1. Go to the coldfusion administrator
2. select datasources
3. edit your datasource
4. click on advanced settings
5. check the "enable clob"
6. save datasource

You shouldn't need to restart CF for this.

Psalm

Just for searching purposes, I was storing PDFs in the database but was unable to retrieve or display them. I kept getting a "pdf-" or was it "-pdf" error. Anyway, enabling the setting per this answer resolved my issue. – Methuselah

Worked for me as well. Before making this change, I only received 64000 bytes and the page showed a truncated jpeg. Now I receive the whole picture. – Facula

Answer 2

Don't know if you find the solution for your problem but for me it's a driver issue: then you need to update the jdbc driver. Look at this technote Updated DataDirect JDBC drivers (version 3.4)

Anticlinorium