Just a syntax question: building a query statement based on user's selections

why you have ' at #user's_selection_from_form#  ?

change this variable to #users_selection_from_form#

just make it single quotes, don't worry about escaping and all that. if there are possibly going to be single quotes in the form variable, then use #preservesinglequotes(form.variable)#

Beyond all that, creating a SQL string is rather awkward. You can do this instead:

<cfquery>
  SELECT attribute_a, attribute_b
  FROM table_a
  WHERE
<CFIF user wants student_id info>student_id = '#student_ID#'</CFIF>
<CFIF user wants something else -- (here's where my problem is)>
    student_major = '#preservesinglequotes(form.variable)#'
</cfquery>

Tallermike is correct, but just a suggestion.

say if the user selects more than one options then you will have to form your query more apropriately.

that is say user wants both student_id info student_major then you will have to combine cfif's

<CFSET EXTRACONDITION = "">
<CFSET S_SQL== "">

<CFIF user wants student_id info>
   <CFSET S_SQL = "student_id = '" & #student_ID# & "'">
   <CFSET EXTRACONDITION = "AND">
</CFIF>

<CFIF user wants something else -- (here's where my problem is)>
     <CFSET S_SQL = #S_SQL# & #EXTRACONDITION#  & "student_major = '" & #PRESERVESINGLEQUOTES(form.variable)# & "'">
</CFIF>

<cfquery>
 SELECT attribute_a, attribute_b
 FROM table_a
 WHERE #PRESERVESINGLEQUOTES(S_SQL)#
</cfquery>

Regards
Hart(Harish)

What if the user doesn't select anything?  It should be like this:

<cfquery>
 SELECT attribute_a, attribute_b
 FROM table_a
 WHERE 0=0
<CFIF user wants student_id info>AND student_id = '#student_ID#'</CFIF>
<CFIF user wants something else -- (here's where my problem is)>AND student_major = '#preservesinglequotes(form.variable)#'
</cfquery>

Why use single quotes in the student_major form field at all?  If it's not a multiple select, there's really no reason to do this.  So if that's the case, get rid of the preservesinglequotes above.

Forgot something...  Isn't student_id an integer field?  If not, it should be and the single quotes should be removed.  Also, is there any form validation that prevents the user from NOT selecting anything?  If so, the query I submitted above will work but wouldn't be necessary.

Simple :

1/ the way you build your query is OK. (don't forget a space at the end of each concatenation, to avoir sticking words together)

2/ to send the query, just do :

  <cfquery name="..." datasource="...">
    #PreserveSingleQuotes(strSQL)#
  </cfquery>

This is due to the fact that CF will double the ' when expressing a variable :

IF variable "a" contains "it's cool"

THEN "#a#" will produce "it''s cool" (only inside the cfquery tag, of course).

This is very usefull : this is why you don't have to worry about double-quoting usualy...

E.

Any luck on this question natG?

TallerMike et al,

This question has become an issue again. I was doing it by embedding CFIFs inside my query tag like you suggested on 3/11, and it was working fine. However, now I realize I need to save this query string as text in a db field so that users can choose default querys (filtering options on a list of students) for their individual needs. And when they sort the list by clicking on the column heads the page reloads, so having the query string as a var that I can pass around seems like the way to go.

Anyway, I am still having some syntax problems-- There is a CFLOOP insided the select statement that dynamically adds columns to the query, and the output(column names) has the index of the loop appended to them to give them unique names. So, I have coldfusion tags and variables inside the string that shouldn't be evaluated until the query is actually run; I think I need to use the DE() and / or evaluate() functions, but I'm still getting various errors.

I don't have the code with me at the moment. I could post it tonight. In the meantime, if anyone has any examples they could post I'd appreciate it.

OK, here is the latest version of the code. I've tried multiple variations and I get a different error every time. I have part of it commented out just to try to tackle one problem at a time. The commented-out part, inside the cfloop, was throwing a different error- CF was trying to evaluate the i at the time I set the var, and not later. DE() did not seem to work, so I commented it out and found myself with another problem. Here it is:


<cfset sql = DE('Select     Student.Student_ID,
                    <cfloop index = i from = 1 to = #getprereqs.RecordCount#>
                    (Select Prereq_Status from PetitionPrereqState
                              where PetitionPrereqState.Student_ID = Student.Student_ID
                              and PetitionPrereqState.Prereq_ID = 1) as Prereq_Status1,
          <!---               and PetitionPrereqState.Prereq_ID = '&chr(35)&'i'&chr(35)&') as Prereq_Status'&#chr(35)#&'i'&#chr(35)#&',     --->
          <!---               and PetitionPrereqState.Prereq_ID = '&chr(35)&'i'&chr(35)&') as Prereq_Status'&#chr(35)#&'i'&#chr(35)#&',     --->
                    </cfloop>

                    Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
                    Student_Major, Student_GPA, Pref_Class_Section,
                    (Select TOP 1 Status_name from StatusHistory
                                                            where Petition.Petition_ID = StatusHistory.Petition_ID
                                                            order by     Status_date desc) as Pet_Status

                    from Student, Petition
                    where Student.Student_ID = Petition.Student_ID
                    and Petition.Course_ID = #SESSION.DEFAULTCOURSE#')>
     <!---
<cfif state is "2">
     <cfif #form.firstchoice# is "1">
               and DispCode = '#form.secondChoice#'   change next line to else if!
          <cfif #form.firstchoice# is "2">
               <cfset sql = sql & ' and Student.Student_GPA = '#form.secondChoice#''>
          <cfelseif #form.firstchoice# is "3">
               <cfset sql = sql & " and Student.Student_Major = '#form.secondChoice#'">
          </cfif>
</cfif> --->
                   
<cfset sql = sql & '     order by #sort#  #sortorder#'>

<cfquery      name="getstudents"
                    DATASOURCE="#ODBC_SOURCE#"  
                    username="#ODBC_USERID#"
                    password= "#ODBC_PASSWD#">

                    #PreserveSingleQuotes(sql)#

                                   
</CFQUERY>




The error (this time) is:
"[Macromedia][SQLServer JDBC Driver][SQLServer]The identifier that starts with 'Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where' is too long. Maximum length is 128."


Anyone who could throw me a bone here would be much appreciated.
Thanks in advance.

NatG


Thanks in advance.

you need to increase the size of your column in your db structure, or change the datatype to something bigger.

I don't understand-- the size of what column? This is a query, I am not adding or updating to the db. Can you elaborate?

I don't understand-- the size of what column? This is a query, I am not adding or updating to the db. Can you elaborate?

OK, the code has evolved a little as I am trying different things. Here it is as it looks now:

<cfset sql ='Select     Student.Student_ID,
                    #chr(60)#cfloop index = i from = 1 to = #getprereqs.RecordCount##chr(62)#
                    (Select Prereq_Status from PetitionPrereqState
                              where PetitionPrereqState.Student_ID = Student.Student_ID
                              and PetitionPrereqState.Prereq_ID = 1) as Prereq_Status1,
                    #chr(60)##chr(47)#cfloop#chr(62)#

                    Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
                    Student_Major, Student_GPA, Pref_Class_Section,
                    (Select TOP 1 Status_name from StatusHistory
                                                            where Petition.Petition_ID = StatusHistory.Petition_ID
                                                            order by     Status_date desc) as Pet_Status

                    from Student, Petition
                    where Student.Student_ID = Petition.Student_ID
                    and Petition.Course_ID = #SESSION.DEFAULTCOURSE#'>
     <!---
<cfif state is "2">
     <cfif #form.firstchoice# is "1">
               and DispCode = '#form.secondChoice#'   change next line to else if!
          <cfif #form.firstchoice# is "2">
               <cfset sql = sql & ' and Student.Student_GPA = '#form.secondChoice#''>
          <cfelseif #form.firstchoice# is "3">
               <cfset sql = sql & " and Student.Student_Major = '#form.secondChoice#'">
          </cfif>
</cfif> --->
                   
<cfset sql = sql & '     order by #sort#  #sortorder#'>


<CFOUTPUT> #PreserveSingleQuotes(sql)# </cfoutput>

<cfquery      name="getstudents"
                    DATASOURCE="#ODBC_SOURCE#"  
                    username="#ODBC_USERID#"
                    password= "#ODBC_PASSWD#">

                    #sql#



                                   
</CFQUERY>



Here is the cfoutput result:

Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where PetitionPrereqState.Student_ID = Student.Student_ID and PetitionPrereqState.Prereq_ID = 1) as Prereq_Status1, Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID, Student_Major, Student_GPA, Pref_Class_Section, (Select TOP 1 Status_name from StatusHistory where Petition.Petition_ID = StatusHistory.Petition_ID order by Status_date desc) as Pet_Status from Student, Petition where Student.Student_ID = Petition.Student_ID and Petition.Course_ID = 1 order by Student_Lname


And, on the same page right below the output is the error that says:

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 2: Incorrect syntax near '<'.  
 
Then it shows the SQL string as it was sent to the DB, which reads:

Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where PetitionPrereqState.Student_ID = Student.Student_ID and PetitionPrereqState.Prereq_ID = 1) as Prereq_Status1, Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID, Student_Major, Student_GPA, Pref_Class_Section, (Select TOP 1 Status_name from StatusHistory where Petition.Petition_ID = StatusHistory.Petition_ID order by Status_date desc) as Pet_Status from Student, Petition where Student.Student_ID = Petition.Student_ID and Petition.Course_ID = 1 order by Student_Lname  


Now, how weird is it that there is no open anglebracket (<) anywhere in the output of the SQL (indeed I escaped them for the cfloop) but the error refers to one....

Here's another bit of info:  In the code I pasted above, I replaced the index i in the cfloop with just a 1. If I actually include the i like this:

<cfset sql ='Select     Student.Student_ID,
                    #chr(60)#cfloop index = i from = 1 to = #getprereqs.RecordCount##chr(62)#
                    (Select Prereq_Status from PetitionPrereqState
                              where PetitionPrereqState.Student_ID = Student.Student_ID
                              and PetitionPrereqState.Prereq_ID = #chr(35)#i#chr(35)#) as Prereq_Status#chr(35)#i#chr(35)#,
                    #chr(60)##chr(47)#cfloop#chr(62)#

                    Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
                    Student_Major, Student_GPA, Pref_Class_Section,
                    (Select TOP 1 Status_name from StatusHistory
                                                            where Petition.Petition_ID = StatusHistory.Petition_ID
                                                            order by     Status_date desc) as Pet_Status

                    from Student, Petition
                    where Student.Student_ID = Petition.Student_ID
                    and Petition.Course_ID = #SESSION.DEFAULTCOURSE#'>


...then the output and error message on the screen includes the pound signs, even though the <cfloop> is NOT included (nor are the <'s).


....

... and here is the output:

Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where PetitionPrereqState.Student_ID = Student.Student_ID and PetitionPrereqState.Prereq_ID = #i#) as Prereq_Status#i#, Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID, Student_Major, Student_GPA, Pref_Class_Section, (Select TOP 1 Status_name from StatusHistory where Petition.Petition_ID = StatusHistory.Petition_ID order by Status_date desc) as Pet_Status from Student, Petition where Student.Student_ID = Petition.Student_ID and Petition.Course_ID = 1 order by Student_Lname

I don't think you can set a variable with a string that has line breaks in it. I don't think the following is valid:

<cfset thisVariable = "This is the first line.
   And this is the second">

You'll either need to do this:

<cfscript>
  thisVariable = "This is the first line.";
  thisVariable = thisVariable & "And this is the second";
</cfscript>

Or you'll need to create a custom tag to do something like this:

<cf_SuperVariable variableName="thisVariable">
  <cfoutput>
    This is the first line.
    And this is the second
  </cfoutput>
</cf_SuperVariable>

Where cf_SuperVariable is going to take thiTag.generatedContent and put it into the variableName attribute which you can then access later like so:

<cfoutput>#thisVariable#</cfoutput>

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

The reason you're getting errors about a '>' is that you're missing a single quote somewhere, so it is interpreting some of your code as a string. You'll need to find this extra single quote.

OK, thanks-- I'll look for it and get back to you....

... and here is the output:

Select Student.Student_ID, (Select Prereq_Status from PetitionPrereqState where PetitionPrereqState.Student_ID = Student.Student_ID and PetitionPrereqState.Prereq_ID = #i#) as Prereq_Status#i#, Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID, Student_Major, Student_GPA, Pref_Class_Section, (Select TOP 1 Status_name from StatusHistory where Petition.Petition_ID = StatusHistory.Petition_ID order by Status_date desc) as Pet_Status from Student, Petition where Student.Student_ID = Petition.Student_ID and Petition.Course_ID = 1 order by Student_Lname

I think I have solved it. The problem was in the cfloop being inside a string and then evaluated along with the variables inside it. I changed the code to loop OUTSIDE the cfset, and put the cfset inside the loop, and it works like a charm:

<!--- Begin building Query string --->
<cfset sql ='Select     Student.Student_ID, '>
<cfloop index = i from = 1 to = #getprereqs.RecordCount#>
     <cfset sql = sql & 
          '(Select Prereq_Status from PetitionPrereqState
                    where PetitionPrereqState.Student_ID = Student.Student_ID
                    and PetitionPrereqState.Prereq_ID = #i#) as Prereq_Status#i#, '>
</cfloop>

<cfset sql = sql & ' Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
                    Student_Major, Student_GPA, Pref_Class_Section,
                    (Select TOP 1 Status_name from StatusHistory
                                                            where Petition.Petition_ID = StatusHistory.Petition_ID
                                                            order by     Status_date desc) as Pet_Status

                    from Student, Petition
                    where Student.Student_ID = Petition.Student_ID
                    and Petition.Course_ID = #SESSION.DEFAULTCOURSE#'>

<cfif state is "2">
               <cfif #form.firstchoice# is "1">
                    <cfset sql = sql & " and DispCode = '#form.secondChoice#' ">
               <cfelseif #form.firstchoice# is "2">
                    <cfset sql = sql & " and Student.Student_GPA = '#form.secondChoice#' ">
               <cfelseif #form.firstchoice# is "3">
                    <cfset sql = sql & " and Student.Student_Major = '#form.secondChoice#' ">
               </cfif>
</cfif>

                   
<cfset sql = sql & '     order by #sort#  #sortorder#'>
<!--- end building query string--->

<cfquery      name="getstudents"
                    DATASOURCE="#ODBC_SOURCE#"  
                    username="#ODBC_USERID#"
                    password= "#ODBC_PASSWD#">

                    #PreserveSingleQuotes(sql)#
</CFQUERY>




I hope somebody else comes along and benefits from my hours of frustration, because this seems like a common challenge. I imagine I will run into this kind of thing often.

Mike, thanks for your help and quick responses.

NatG

SOLUTION:

The problem was in the cfloop being inside a cfset command, and then evaluated along with the variables inside it. I changed the code to loop OUTSIDE the cfset, and put the cfset inside the loop, and it works like a charm:

<!--- Begin building Query string --->
<cfset sql ='Select     Student.Student_ID, '>
<cfloop index = i from = 1 to = #getprereqs.RecordCount#>
    <cfset sql = sql & 
         '(Select Prereq_Status from PetitionPrereqState
                   where PetitionPrereqState.Student_ID = Student.Student_ID
                   and PetitionPrereqState.Prereq_ID = #i#) as Prereq_Status#i#, '>
</cfloop>

<cfset sql = sql & ' Student_Lname, Student_Fname, Student_MidInit, Petition.Petition_ID,
                   Student_Major, Student_GPA, Pref_Class_Section,
                   (Select TOP 1 Status_name from StatusHistory
                                                           where Petition.Petition_ID = StatusHistory.Petition_ID
                                                           order by     Status_date desc) as Pet_Status

                   from Student, Petition
                   where Student.Student_ID = Petition.Student_ID
                   and Petition.Course_ID = #SESSION.DEFAULTCOURSE#'>

<cfif state is "2">
              <cfif #form.firstchoice# is "1">
                   <cfset sql = sql & " and DispCode = '#form.secondChoice#' ">
              <cfelseif #form.firstchoice# is "2">
                   <cfset sql = sql & " and Student.Student_GPA = '#form.secondChoice#' ">
              <cfelseif #form.firstchoice# is "3">
                   <cfset sql = sql & " and Student.Student_Major = '#form.secondChoice#' ">
              </cfif>
</cfif>

                   
<cfset sql = sql & '     order by #sort#  #sortorder#'>
<!--- end building query string--->

<cfquery      name="getstudents"
                   DATASOURCE="#ODBC_SOURCE#"  
                   username="#ODBC_USERID#"
                   password= "#ODBC_PASSWD#">

                   #PreserveSingleQuotes(sql)#
</CFQUERY>

Well, glad you solved it at least. =) Wish we could have helped more!