解決済み
SQL LIKE statement to text field fomr CF MX 6.1
Hi.
Here's my scenario. I am running SQL Server 2000 SP4 on a Windows 2003 Server running IIS 6. Using ColdFusion MX 6.1 Updater version.
I am storing HTML page code (some complete, some just clips) in a text type field.
I need to check to see if someone has already saved that exact code already before. So I do the following query:
<cfquery name="checkforexisting" datasource="mydatasource">
SELECT smallfieldtogetareturn
FROM MyTable
WHERE MyTextField LIKE '%#mystringoflongdata#%'
</cfquery>
Then to see if it posted:
<cfif checkforexisting.RecordCount GT 0>
do the don't clip optional processing
</cfif>
What is killing me is that it finds the match SOMETIMES. Just not ALL the time. I have tried no percent signs when passing the ColdFusion variable, only one on the right (which has solved these types of problems before).
If I pull the record back OUT of the database in a page by itself (retrieving it by key field), it finds every other copy of the code EVERY time.
I have put this into a stored procedure, which seemed to help (?).
I even have resorted to creating a temp table first with the clip, then running a query to retrieve the record that was just put in, and comparing THAT to my primary table (both have text type fields). The reason for this, is that if I write a single page that has just the retrieval of the temp record, and that compare is run, it finds EVERY copy of it in the primary table. I also thought that maybe there was a strong problem (I had tried HTMLEditFormat, HTMLCodeFormat, JavaCast since it was coming from a java program on the browser end).
I am not terribly familiar with using large text fields, or dealing with these large pieces of HTML code as something to compare!
- Mike
Here's my scenario. I am running SQL Server 2000 SP4 on a Windows 2003 Server running IIS 6. Using ColdFusion MX 6.1 Updater version.
I am storing HTML page code (some complete, some just clips) in a text type field.
I need to check to see if someone has already saved that exact code already before. So I do the following query:
<cfquery name="checkforexisting" datasource="mydatasource">
SELECT smallfieldtogetareturn
FROM MyTable
WHERE MyTextField LIKE '%#mystringoflongdata#%'
</cfquery>
Then to see if it posted:
<cfif checkforexisting.RecordCount GT 0>
do the don't clip optional processing
</cfif>
What is killing me is that it finds the match SOMETIMES. Just not ALL the time. I have tried no percent signs when passing the ColdFusion variable, only one on the right (which has solved these types of problems before).
If I pull the record back OUT of the database in a page by itself (retrieving it by key field), it finds every other copy of the code EVERY time.
I have put this into a stored procedure, which seemed to help (?).
I even have resorted to creating a temp table first with the clip, then running a query to retrieve the record that was just put in, and comparing THAT to my primary table (both have text type fields). The reason for this, is that if I write a single page that has just the retrieval of the temp record, and that compare is run, it finds EVERY copy of it in the primary table. I also thought that maybe there was a strong problem (I had tried HTMLEditFormat, HTMLCodeFormat, JavaCast since it was coming from a java program on the browser end).
I am not terribly familiar with using large text fields, or dealing with these large pieces of HTML code as something to compare!
- Mike
AdChoices