?
Solved

LIKE pattern match breaks in Coldfusion MX

Posted on 2003-03-10
5
Medium Priority
?
132 Views
Last Modified: 2013-12-24
We've migrated to MX recently and discovered that when selecting against Access DB using LIKE it no longer works.

The code concerned is

<CFSET searchCriteria = "WHERE #CHR(34)#Postcode#CHR(34)# like '%#Area# %'">

The following syntax error is reported.

[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression '[Postcode] like ''%EH1 %'' AND [Available] = -1'.

This could used to work before upgrade to MX.
I've tried a numbner of quote combinations but none are successful.
Does anyone have any idea if the LIKE syntax should be properly used in MX?

Thanks
0
Comment
Question by:beatroute
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8102372
I am using CFMX and Sybase
and like is working for me
AND upper(ctx_c_nme1_n) like '#UCase(customer_name)#%'


It could be problem in [ that are use  

Try the following
WHERE Postcode like 'EH1%'


If still have problem, check JDBC doc's for Access driver

0
 

Author Comment

by:beatroute
ID: 8102533
More info.
As you'll see, the query is being built using a number of tests to build the searchstring which is then being passed through the PreserveSingleQuotes function within the cfquery tag;

SELECT "Street Address" AS Address, Postcode, Rent, Family, GroupNumber,
     "Excluding Bills" as eBills, "Including Bills" as iBills, UniversityProperty
     FROM Address #PreserveSingleQuotes(searchCriteria)# ORDER BY UniversityProperty, Rent;

I think the combination of MX/function/ODBC driver is the problem.

Is this known?
Should I install JDBC Access driver and if so which one?
0
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8104320
Try the following query from new cf page

SELECT Address, Postcode, Rent, Family, GroupNumber,
    eBills,iBills, UniversityProperty
FROM Address
WHERE Postcode like 'EH1%'
ORDER BY UniversityProperty, Rent;

and let me know if you have problem with that

0
 

Author Comment

by:beatroute
ID: 8104468
Thanks for the help so far but I have been able to resolve the problem.

The original search clause was cummulatively built via different tests and then parsed through the preserve function in its entirity.

SELECT "Street Address" AS Address, Postcode, Rent, Family, GroupNumber,
     "Excluding Bills" as eBills, "Including Bills" as iBills, UniversityProperty
     FROM Address
     #PreserveSingleQuotes(searchCriteria)#
    ORDER BY UniversityProperty, Rent;

Using this method however caused the quotes NOT to be preserved.

I separated the LIKE part of the clause from the rest and only passed it through the function and it worked OK. This resulted in a two part search clause as below;

SELECT "Street Address" AS Address, Postcode, Rent, Family, GroupNumber,
     "Excluding Bills" as eBills, "Including Bills" as iBills, UniversityProperty
     FROM Address
     #searchCriteria#
     #PreserveSingleQuotes(postcodeCriteria)#
    ORDER BY UniversityProperty, Rent;

A strange one but all is now working.
0
 

Accepted Solution

by:
SpideyMod earned 0 total points
ID: 8104606
PAQ'd and all 75 points refunded.

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
The purpose of this video is to demonstrate how to insert an Iframe into WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Open Page or Post…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question