I query tables all the time that have CLOB fields and I use the CFQUERYPARAM tag. I just tested it to make sure because could not find a working example, test ran without issue with CF8 Enterprise and Oracle 9i. I also tried it with CF6 Enterprise and against the same Oracle database and had no issues. Here are the test queries that were used in both environments: <cfquery name="test" datasource="#App.DataSource#"> SELECT DEFAULT_VALUE AS THE_VALUE FROM SYS_SITE_CONFIG_TYPES WHERE DEFAULT_VALUE = <cfqueryparam value="false" cfsqltype="cf_sql_clob" /> </cfquery>
<cfquery name="test2" datasource="#App.DataSource#"> SELECT DEFAULT_VALUE AS THE_VALUE FROM SYS_SITE_CONFIG_TYPES WHERE DEFAULT_VALUE LIKE <cfqueryparam value="%false%" cfsqltype="cf_sql_clob" /> </cfquery> My CF8 environment is out of the box, nothing special was done just a datasource setup and only the basic fields filled in when doing that. the CF6 environment I do not have access to the CF Admin but knowing how the data center admins are I would be VERY surprised if it was not out of the box as well. What I wonder about is if the use of CLOBS is truly needed when I see them used in a WHERE clause for an exact match, just seems strange to me that someone would be passing in a CF variable with so much information that exceeded what a VARCHAR2 can handle. In the database I did my test against the original designer over used them, I think he was CLOB happy. On Thu, Dec 11, 2008 at 6:43 PM, Mike Gillespie <[email protected]> wrote: > > If you are doing this in a Oracle database, you have to search clobs > using the dbms_lob functions > > I have not had to do this in a long time, so I have not done it with > cfqueryparam, this is how you do it without cfqueryparam > > SELECT * > FROM lob_tab > Where dbms_lob.instr(clob_content,'#clob_content#') > > > The only time I have used cfqueryparam when playing with CLOBs lately > is on inserts or updates, which works like your sample code, but if I > had to guess, I would say you would use cfqueryparam like this > > SELECT * > FROM lob_tab > Where dbms_lob.instr(clob_content,'<cfqueryparam type="cf_sql_clob" > value="#clob_content#">') > > But this might work better (depending on your data); will definately > be slower > > SELECT * > FROM lob_tab > Where dbms_lob.instr(lower(clob_content),'<cfqueryparam > type="cf_sql_clob" value="#lcase(clob_content)#">') > > > HTH, > > M > > On Dec 11, 3:30 pm, Manno <[email protected]> wrote: > > I am having trouble output text string with the CLOB contraint. > > I enable the option in the admin, set the query param to recognize it > > but it gives me this error. > > If anyone has any idea on this subject i could use the help. > > Error > > inconsistent datatypes: expected - got CLOB > > > > SELECT * > > 12 : FROM lob_tab > > 13 : Where clob_content = <cfqueryPARAM value = "#clob_content#" > > CFSQLType = "CF_SQL_CLOB"> > > 14 : </cfquery> > > > > Thanks > > Manuel > > > -- Aaron Rouse http://www.happyhacker.com/ --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the "Houston ColdFusion Users' Group" discussion list. To unsubscribe, send email to [email protected] For more options, visit http://groups.google.com/group/houcfug?hl=en -~----------~----~----~----~------~----~------~--~---
