Error Executing Database Query - Help!

[jackhuang]

I have a cfquery code that looks like this:

    <CFQUERY DATASOURCE="mydatabase" USERNAME="myuser" PASSWORD="mypassword" NAME="codelist" CACHEDWITHIN="#CreateTimeSpan(0,1,0,0)#">
    SELECT *
    FROM systemcode
    WHERE (LEN(TRIM(systemcode)) = 2 OR LEN(TRIM(systemcode)) = 4)
    <CFSWITCH EXPRESSION="#CAT#">
    <CFCASE VALUE="1">AND (MID(systemcode,1,2) BETWEEN '01' AND '05')</CFCASE>
    <CFCASE VALUE="2">AND (MID(systemcode,1,2) BETWEEN '06' AND '15')</CFCASE>
    <CFCASE VALUE="3">AND (MID(systemcode,1,2) BETWEEN '16' AND '24')</CFCASE>
    <CFCASE VALUE="4">AND (MID(systemcode,1,2) BETWEEN '25' AND '27')</CFCASE>
    <CFCASE VALUE="5">AND (MID(systemcode,1,2) BETWEEN '28' AND '38')</CFCASE>
    <CFCASE VALUE="6">AND (MID(systemcode,1,2) BETWEEN '39' AND '40')</CFCASE>
    <CFCASE VALUE="7">AND (MID(systemcode,1,2) BETWEEN '41' AND '43')</CFCASE>
    <CFCASE VALUE="8">AND (MID(systemcode,1,2) BETWEEN '44' AND '49')</CFCASE>
    <CFCASE VALUE="9">AND (MID(systemcode,1,2) BETWEEN '50' AND '63')</CFCASE>
    <CFCASE VALUE="10">AND (MID(systemcode,1,2) BETWEEN '64' AND '67')</CFCASE>
    <CFCASE VALUE="11">AND (MID(systemcode,1,2) BETWEEN '68' AND '71')</CFCASE>
    <CFCASE VALUE="12">AND (MID(systemcode,1,2) BETWEEN '72' AND '83')</CFCASE>
    <CFCASE VALUE="13">AND (MID(systemcode,1,2) BETWEEN '84' AND '85')</CFCASE>
    <CFCASE VALUE="14">AND (MID(systemcode,1,2) BETWEEN '86' AND '89')</CFCASE>
    <CFCASE VALUE="15">AND (MID(systemcode,1,2) BETWEEN '90' AND '97')</CFCASE>
    <CFCASE VALUE="16">AND (MID(systemcode,1,2) BETWEEN '98' AND '99')</CFCASE>
    <CFDEFAULTCASE><CFLOCATION URL="somewhere.htm"></CFDEFAULTCASE>
    </CFSWITCH>
    ORDER BY systemcode
    </CFQUERY>

This works perfectly in CF 5.  However, when running in CF 8 I am getting the error message:

Error Executing Database Query.FUNCTION mydatabase.LEN does not exist The specific sequence of files included or processed is: /home/httpd/vhosts/somedomain.com/httpdocs/reference/systemcode.cfm, line: 24

(that refers to the above cfquery)

"mydatabase" is the name of the database and LEN is a function.    It's checking for the length of systemcode to be 2 or 4, if they are then check if the value of #CAT# is 1 and that the first 2 characters is between 01 and 05 then select the row.  I don't know why it works with CF5 but not CF8?  It's driving me nuts.  Any help is appreciated.

[Dave Watts]

You can't use CFML functions in SQL. Len and Trim are CFML functions.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on

GSA Schedule, and provides the highest caliber vendor-authorized

instruction at our training centers, online, or onsite.

Read this before you post:

http://forums.adobe.com/thread/607238

[jackhuang]

This was working perfectly in CF5.  Is this a new limitation?  Can you help out with alternate code?

[Dan_Bracuk]

Mixing cfml and sql in a cfquery is an inexact science.  If your error message is about the len() function, look for an sql equivalent and use that.  By the way, the sql equivalent depends on your db software.

[jackhuang]

I am also using the CFSWITCH within the cfquery and it worked before also....

[-__cfSearching__-]

"mydatabase" is the name of the database and LEN is a

function. 

What database type are you using? Is LEN() a valid sql function in your database?

>>  <CFLOCATION URL="somewhere.htm">

Is there really a cflocation in the middle of your query or is that a typo?

[jackhuang]

I am using MySQL.  Before when I was using CF5 I was using MS Access.  But it worked perfectly.  I just don't see why all of a sudden it doesn't work with CF8 and MySQL,  Are there MySQL equivalent for these two functions (TRIM and LEN)?

I don't konw there was a TRIM and LEN in MS Access database.  I guess I was mixing them.  Yes, I did put a CFLOCATION inside the cfquery.  Never encounter that scenario so I didn't know if it works.

[-__cfSearching__- · Accepted solution]

I just don't see why

all of a sudden it doesn't work with CF8 and MySQL,

If you changed databases, that is the source of the problem, not upgrading CF.  MySQL will not understand MS Access specific functions and vice versa. Do not expect queries written for one database type to automatically work with another.  While there are sql standards, every database is different. It is quite common for database vendors to have totally different function names, data types and even syntax. You will need to throrougly review your database queries and convert any Access specific syntax, data types, etcetera to the MySQL equivalents.

Are there MySQL equivalent for these two functions (TRIM and

LEN)?

Yes. See the string functions section in the online mySQL docs.

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

I don't konw there was a TRIM and LEN in MS Access

database.  I guess I was mixing them.

Yes. It is a little confusing because both CF and MS Access have those functions.  In this specific case you were using the Access functions, not CF.

        http://www.techonthenet.com/access/functions/index_alpha.php

>> Yes, I did put a

CFLOCATION inside the cfquery.  Never encounter that

scenario so I didn't know if it works.

Well if you are using the code, it should be something you have tested. So you know how it behaves. But CFLOCATION really does not belong inside a database query.

`