?
Solved

missing expression using CFQUERYPARAM

Posted on 2003-03-18
21
Medium Priority
?
891 Views
Last Modified: 2013-12-24
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.

0
Comment
Question by:SpineyNorman
[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
  • 10
  • 8
  • 3
21 Comments
 
LVL 8

Expert Comment

by:TallerMike
ID: 8160246
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.
0
 
LVL 8

Expert Comment

by:TallerMike
ID: 8160290
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.
0
 
LVL 8

Expert Comment

by:TallerMike
ID: 8160364
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...
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 1

Author Comment

by:SpineyNorman
ID: 8160398
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.
0
 
LVL 1

Author Comment

by:SpineyNorman
ID: 8160476
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.
0
 
LVL 1

Author Comment

by:SpineyNorman
ID: 8160572
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.
0
 
LVL 8

Expert Comment

by:TallerMike
ID: 8160753
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.
0
 
LVL 8

Expert Comment

by:TallerMike
ID: 8160766
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"">")#
0
 
LVL 1

Author Comment

by:SpineyNorman
ID: 8160904
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
0
 
LVL 8

Expert Comment

by:TallerMike
ID: 8161146
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.
0
 
LVL 1

Author Comment

by:SpineyNorman
ID: 8161314
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.
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8164528
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>

<!--- ==================== eg on using CFQueryparam ======================= --->
<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
0
 
LVL 1

Author Comment

by:SpineyNorman
ID: 8165842
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 ...
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8165905
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
0
 
LVL 1

Author Comment

by:SpineyNorman
ID: 8165970
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
0
 
LVL 1

Author Comment

by:SpineyNorman
ID: 8166077
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
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8166196
chk what u get in the output of the above query - so as toknow where exactly it fails
0
 
LVL 1

Author Comment

by:SpineyNorman
ID: 8166224
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)
-----------
0
 
LVL 8

Accepted Solution

by:
TallerMike earned 1000 total points
ID: 8166693
SpineyNorman, I really think this is a dead end road. It's just not possible to send query parameters any other way than to use CFQUERYPARAM within CFQUERY tags. Your latest attempt is still just sending the CFQUERYPARAM tag, not actually executing it. Outside of a CFQUERY, it has no meaning. And as a string it has no meaning either. The DB server has no idea what to do with CFQUERYPARAM.

Sorry if I'm being negative here... but I just don't think it's possible.
0
 
LVL 1

Author Comment

by:SpineyNorman
ID: 8167318
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.

0
 
LVL 8

Expert Comment

by:TallerMike
ID: 8167432
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! =)
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

770 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