Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 133
  • Last Modified:

LIKE pattern match breaks in Coldfusion MX

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
beatroute
Asked:
beatroute
  • 2
  • 2
1 Solution
 
HamdyHassanCommented:
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
 
beatrouteAuthor Commented:
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
 
HamdyHassanCommented:
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
 
beatrouteAuthor Commented:
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
 
SpideyModCommented:
PAQ'd and all 75 points refunded.

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now