asked on

missing expression using CFQUERYPARAM

Being the dilligent little developer that I am, I'm trying to outfit our queries with CFQueryParam. We're using CF version 5, native drivers, Oracle 8.1.7.
Here's the query in the .cfm file
select
RTRIM(POLE_TYPE,' ') AS POLE_TYPE
, GEOLOC, POLE_NO
from #table#
WHERE #Criteria#
ORDER BY GEOLOC
Actually, it selects a lot more fields, but I've deleted them b/c they don't matter.
#table# is just the table name, nothing fancy.
#Criteria# is built in a custom tag wich parses arguments such as field type, name, and user-inputted value to come out with
<CFSET Criteria = Criteria & ' = <cfqueryPARAM value = #Value# CFSQLTYPE="CF_SQL_VARCHAR">'>
or <CFSET Criteria = 'GEOLOC = <cfqueryPARAM value = #Value# CFSQLTYPE="CF_SQL_VARCHAR">'>
When I run this I get an oracle error.
ORA-00936: missing expression
SQL = "select RTRIM(POLE_TYPE,' ') AS POLE_TYPE , GEOLOC, POLE_NO from V_PLR_RPT WHERE GEOLOC = ORDER BY GEOLOC"
Viewing the source for that page shows me that the SQL = "select
RTRIM(POLE_TYPE,' ') AS POLE_TYPE
, GEOLOC, POLE_NO
from V_PLR_RPT
WHERE GEOLOC = <cfqueryPARAM value = M16108 CFSQLTYPE="CF_SQL_VARCHAR">
ORDER BY GEOLOC"
So, it would appear that things are getting interpreted/evaluated correctly. In fact, if I copy/paste
WHERE GEOLOC = <cfqueryPARAM value = M16108 CFSQLTYPE="CF_SQL_VARCHAR">
from the souce into the code, the query works.

Anybody have any ideas?
BTW I used to be using WHERE #PRESERVESINGLEQUOTES(Criteria)# in my cfml and
<CFSET Criteria = Criteria & " = <cfqueryPARAM value = '#Value#' CFSQLTYPE='CF_SQL_VARCHAR'>"> in the custom tag
but I took that out on a whim and am having the same result.

TallerMike

Call me crazy...but I don't think this is valid:

<CFSET Criteria = Criteria & " = <cfqueryPARAM value = '#Value#' CFSQLTYPE='CF_SQL_VARCHAR'>">

You can't call a tag from within another tag? the CFQUERYPARAM tag basically puts a question mark in the SQL, and then sends parameters to the DB seperate from the SQL. So even if this did work, it would only put a question mark in the SQL code and it would never actually send the parameters.

THe following works because it is the correct useage of cfqueryparam:

WHERE GEOLOC = <cfqueryPARAM value = M16108 CFSQLTYPE="CF_SQL_VARCHAR">

In short, you will not be able to create a variable that stores all of the criteria and use cfqueryparam. One or the other.

TallerMike

I think you're thinking that cfqueryPARAM is returning a value that you're putting in the string, but that's not the case. It returns nothing. It just tells the DB to expect some values.

To see how this works, go into your DB and run the following query:

SELECT RTRIM(POLE_TYPE,' ') AS POLE_TYPE , GEOLOC, POLE_NO
FROM V_PLR_RPT
WHERE GEOLOC = ?
ORDER BY GEOLOC

It should pop up a window that asks for the values of the parameters. Basically what happens here is that CFQUERYPARAM puts that "?" in the SQL, and then sends the parameters seperately.

Let me know if I'm not being clear enough.

TallerMike

It MAY be possible for you to put all of the criteria into a string, and then call EVALUATE on it or something, but you would have to test it out to see if it works. Basically you'd have to put DE() tags around the stuff you don't want evaluated, and stuff like that. I'm not exactly sure what you'd have to do to get it to work, but that would be a start. Maybe try testing with something simple to see if you can get it to work, like this:

<CFSET Criteria = "DE(""MyVal = "") & <cfqueryPARAM value = '#Value#' CFSQLTYPE='CF_SQL_VARCHAR'>">

I dunno thought, looks pretty funky to me though...

**********************************************************************
**********************************************************************

Maybe the better option is to create a module that would simply output the cfqueryparam tags like this:

select
RTRIM(POLE_TYPE,' ') AS POLE_TYPE
, GEOLOC, POLE_NO
from #table#
WHERE <cfmodule template="genCriteria.cfm">
ORDER BY GEOLOC

And have genCriteria generate the CFQUERYPARAM tags. That might not even work, but it's worth a try too...

SpineyNorman

ASKER

I understand what your saying, and I think it would explain things; except, when I look at the souce on the error page, it has clearly substituted the variable #Criteria# with
GEOLOC = <cfqueryPARAM value = M16108 CFSQLTYPE="CF_SQL_VARCHAR">
Otherwise, why would I even see the value of the variable?
In the interest of making sure things are clear:
The cfquery tag is in the main page
The criteria variable is being set in a custom tag called. from the main page before the call to cfquery is made.

SpineyNorman

ASKER

I took the criteria generating tag out of the equation. Now, this code is all in the main page
<CFSET Criteria = "GEOLOC">
<cfset Value = "M16108">
<CFSET Criteria = Criteria & ' = <cfqueryPARAM value = #Value# CFSQLTYPE="CF_SQL_VARCHAR">'>
<cfquery ...>
select
RTRIM(POLE_TYPE,' ') AS POLE_TYPE
, GEOLOC, POLE_NO
from #table#
WHERE #Criteria#
ORDER BY GEOLOC
</CFQUERY>

I've played around with DE a bit and couldn't get it to work, but I'll try a bit more. Evaluate doesn't seem right; indeed it doesn't work, because I'm not really evaluating anything, just interpreting a variable and trying to pass that through cfquery.

SpineyNorman

ASKER

TallerMike

I know, and what you're doing, is that phsically sending the string "<cfqueryPARAM value = M16108 CFSQLTYPE="CF_SQL_VARCHAR">" to the DB. You are at no point actually executing the CFQUERYPARAM tag. You're seeing the value of the variable because you put pound signs around it.

TallerMike

Again, I don't think you can call CFQUERYPARAM either outside of a CFQUERY block, or within another tag. So I really don't think any of this is going to work. But I'm still willing to try and come up with silly ways around it...

Try this maybe though:

WHERE GEOLOC = #Evaluate("<cfqueryPARAM value=""#Value#"" CFSQLTYPE=""CF_SQL_VARCHAR"">")#

SpineyNorman

ASKER

That gives me one of these. (see below)
I also tried assigning
MyCrit = DE('<CFQUERYPARAM value="#Value#" CFSQLTYPE="CF_SQL_VARCHAR">')>
and then evaluating that. But, that leads to the original error. I think I see what your saying. Sometimes I'm passing the literal "<cfqueryparam..." to the db (without it being executed-->wrong) or, cfqueryparam is being executed outside of the cfquery tag where it's resulting in ? or nothing-->"missing expression".

I'm starting to think there is no happy medium; but, I appreciate your help your help.

An error occurred while evaluating the expression:

#Evaluate("")#

Error near line 134, column 18.
--------------------------------------------------------------------------------

An error has occurred while processing the expression:

<cfqueryPARAM value="M11101" CFSQLTYPE="CF_SQL_VARCHAR">

Invalid token found on line 1 at position 1. ColdFusion was looking at the following text:

<c
Invalid expression element. The usual cause of this error is a misspelling in the expression

TallerMike

Sorry it doesn't seem to work out... although you could still try using a module/custom tag to do it like so:

SELECT RTRIM(POLE_TYPE,' ') AS POLE_TYPE, GEOLOC, POLE_NO
FROM #table#
WHERE <cfmodule template="genCriteria.cfm">
ORDER BY GEOLOC

And then from within genCriteria.cfm:

Criteria = <cfqueryPARAM value = #Value# CFSQLTYPE="CF_SQL_VARCHAR">
AND Criteria2 = <cfqueryPARAM value = #Value2# CFSQLTYPE="CF_SQL_VARCHAR">

I'm not sure that this will work either, but this way the CFQUERYPARAM tags are generated in another file that may be included (or modualized) from multiple places.

SpineyNorman

ASKER

Good thought, but it didn't work either. I can get to the point where I get the same error as before "missing expression" or I can get some grief about
The CFQUERYPARAM tag must be nested between the <CFQUERY> and the </CFQUERY> tags
Which is a *little* helpful (given what you've already said, of course)

I also tried using cfinclude.

anandkp

Hi, change ur Query to :
<CFQUERY NAME="x">
select RTRIM(POLE_TYPE,' ') AS POLE_TYPE
, GEOLOC, POLE_NO
from #table#
WHERE Criteria = <CFQUERYPARAM value="#Value#" CFSQLTYPE="CF_SQL_VARCHAR">ORDER BY GEOLOC
</CFQUERY>


<CFSET Course_ID=12>
<CFQUERY NAME="getFirst" DataSource="CompanyInfo">
SELECT * FROM departments
WHERE Dept_ID=
<CFQUERYPARAM VALUE="#Dept_ID#" CFSQLTYPE="CF_SQL_INTEGER">
</CFQUERY>
<CFOUTPUT QUERY="getFirst">
<P>Department Number: #number#<BR>Description: #descript#</P>
</CFOUTPUT>

let me know

K'Rgds
Anand

SpineyNorman

ASKER

anandkp,
The query works if I just code the cfqueryparam into the SQL within the cfquery tag. What I'm trying to do is get the cfqueryparam into a variable, and use that variable in the SQL such that the cfqueryparam tag will be interpreted in the cfquery.

I'm doing this because I've built one custom tag that builds all of my WHERE clauses. It takes into acount that a user could type in !x, >x, <x, x%, x|y, x:z, or =. (not x greater than, less than, wildcard, or, between, and IS NULL respectively) I'd like to use cfqueryparam there, instead of writing a CFIF for each field that can be searched on (assuming that would even work, it's still about 20-30 fields for the larger ones). I don't want to sound like I'm griping(though i am), just trying to give a better view of what I want.
----
TallerMike,
I'm pretty close to accepting that it can't be done. I'll probably accept one of your comments in a few hours (on the off chance that I, or someone else, has an epiphany).

Perhaps you could combine a couple of your earlier comments about it not working outside of a cfquery tag b/c ...

anandkp

in that case - y not call a tag strait away in ur query & have all ur code for the CFQUERYPARM within ur tag, there u cld take care of all the permutation & combination & just call a tag in ur query - this can be done right !

does this sound too confusing ???

K'Rgds
Anand

SpineyNorman

ASKER

anandkp,

Actually, it sounds simple. And sounds like it would work, but it doesn't, at least not that I could figure out. If you're able to get an example working, I'm ready to try it.

Thanks

SpineyNorman

ASKER

All,

My latest attempt:
<cfset val = "abc">
<cfset var = 'colA = cfqp VALUE="#val#" CFSQLTYPE="CF_SQL_VARCHAR">'>
<CFQUERY NAME="RptQuery"
DATASOURCE="support_user"
DBTYPE="Oracle80"
USERNAME="#user#"
PASSWORD="#pass#">
SELECT *
FROM support.bind_test
WHERE #Replace(var,"cfqp","<cfqueryparam")#
</CFQUERY>
This produces the Oracle missing expression error.
It should be noted that just
SELECT * FROM table WHERE
produces a different error: unexpected end of SQL command

anandkp

chk what u get in the output of the above query - so as toknow where exactly it fails

SpineyNorman

ASKER

The query I just posted yields
----------
Error Diagnostic Information
Oracle Error Code = 936

ORA-00936: missing expression

SQL = "SELECT * FROM support.bind_test WHERE colA = "

Data Source = "SUPPORT_USER"

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (19:1) to (23:19).

Date/Time: 03/19/03 08:00:28
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 4.0; SBC)
-----------

ASKER CERTIFIED SOLUTION

TallerMike

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SpineyNorman

ASKER

Yup. I think it's hopeless. Fortunately for me, nobody's pressuring me to implement this but myself.
I don't like the answer, but it's the right one.

Thanks for your effort in trying to find a work-around.

TallerMike

No problem. Let me know if you've got any other questions.

Thanks for still giving me a good grade even if it wasn't the answer you wanted. Some people tend to give a bad grade when I tell them it's not possible. I can't help it if you can't do it! =)