Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    3

    Unhappy Unanswered: Using % in WHERE clause gives "unknown table error" from myODBC

    Using ColdFusion 5 to create a search page. A form lets the user set the search parameters. This code snippet processes the returned form data to create a search string:

    <cfif isDefined("form.search")>
    <cfoutput>
    <cfset #form.pattern# = "%#form.pattern#%">
    <cfset search_string = "SELECT * from #form.table# WHERE #form.select# LIKE '#form.pattern#' ORDER BY #form.sort#;">
    <cfquery name="foo"
    datasource=#MM_mydb_DSN#
    username=#MM_mydb_USERNAME# password=#MM_mydb_PASSWORD#>
    #search_string#
    </cfquery>
    </cfoutput>
    </cfif>

    A typical value for search_string: SELECT * from table_foo WHERE foo_id LIKE '%foo%' ORDER BY foo_key;

    From the MySQL command line, this works like a charm, as expected. But in the browser, I get:

    ODBC Error Code = S1000 (General error)
    [TCX][MyODBC]Unknown column 'foo' in 'where clause'

    MyODBC is interpreting '%foo%' as a column name. If I remove either % sign, I get a syntax error:

    ODBC Error Code = S1000 (General error)
    [TCX][MyODBC]You have an error in your SQL syntax near 'foo%'' ORDER BY foo_key;' at line 1

    Even if I pull both % signs out and move the single-quotes to the definition of form.pattern (<cfset #form.pattern# = "'#form.pattern#'"> ) I still get a syntax error:

    [TCX][MyODBC]You have an error in your SQL syntax near 'foo'' ORDER BY foo_id;' at line 1

    I think a key is the double-quote (or two single-qiotes?) in the error message, but I have no idea what's going on. HAALP!


    [TCX][MyODBC]You have an error in your SQL syntax near 'amds'' ORDER BY course_id;' at line 1
    Last edited by corrie; 07-09-02 at 16:09.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    look up PreserveSingleQuotes() function in ColdFusion documentation --

    #PreserveSingleQuotes(search_string)#

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Jul 2002
    Posts
    3
    Thanks. Still not working, though.

    <cfset #form.pattern# = "'%" & #form.pattern# & "%'">
    <cfset search_string = "SELECT * from #form.table# WHERE #form.select# LIKE " & #PreserveSingleQuotes(form.pattern)# & " ORDER BY #form.sort#;" >

    The value of search_string is then:

    SELECT * from foo_table WHERE foo_id LIKE '%foo%' ;

    which works from the mySQL command line, but in the browser I get:

    [TCX][MyODBC]Unknown column 'foo' in 'where clause'


    I think it's the percent signs, but maybe I'm fixating. Any ideas?

  4. #4
    Join Date
    Jul 2002
    Posts
    3

    Talking got it!

    who hoo! This works:

    <cfset #form.pattern# = "'%" & #form.pattern# & "%'">
    <cfset search_string = "SELECT * from #form.table# WHERE #form.select# LIKE " & #PreserveSingleQuotes(form.pattern)# & " ORDER BY #form.sort#;" >
    <cfquery name="foo" datasource=#MM_mydb_DSN# username=#MM_mydb_USERNAME# password=#MM_mydb_PASSWORD#>
    #PreserveSingleQuotes(search_string)#
    </cfquery>

    Had to use PreserveSingleQuotes both inside the cfset that defined search_string, and inside the cfquery that submitted it to myODBC.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •