How do I put the form variables in quoted value list to make it usable in SQL query

[Vineet Garg-4]

Hi,

I have some variables in form which may or may not be defined. Now I want to use these variables in IN clause of a SQL query. i.e Select * from <tablename> where <condition> and column IN ('#form.var1$#','#form.var2#'..).
Is there any function in coldfusion to convert the form variables into quoted value list. I know there is a function QuotedValueList() to convert query column values. Is there anything similar to this for form variables?

Thanks in advance.


Regards,
Vineet Garg.

This message and the information contained herein is proprietary and confidential and subject to the Amdocs policy statement,
you may review at http://www.amdocs.com/email_disclaimer.asp


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4814
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.15

[Paul Kukiel-2]

This may help.

 

You can use this to loop over the variables in a form:

 

<!--- dummy form data --->

<cfset form = {} />

<cfset myList = "" />

<cfset form.var1 = "Name" />

<cfset form.var2 = "LastName" />

 

<!--- Bild a csv list --->

<cfloop collection="#form#" item="key">

      <cfset myList &= form[key] & "," />

</cfloop>

 

<!--- drop the last " , " --->

<cfset myList = left(myList,len(myList)-1) />

 

Also remember to use cfqueryparam.

.. and column IN <cfqueryparam list="true" cfsqltype="cf_sql_varchar"
value="#myList#" />

 

Paul.

http://blog.kukiel.net

 

 


From: Vineet Garg [mailto:VINEETG@...]
Sent: Wednesday, September 23, 2009 7:30 AM
To: cf-newbie
Subject: How do I put the form variables in quoted value list to make it
usable in SQL query

 

 

Hi,

 

I have some variables in form which may or may not be defined. Now I want to
use these variables in IN clause of a SQL query. i.e Select * from
<tablename> where <condition> and column IN
('#form.var1$#','#form.var2#'..).

Is there any function in coldfusion to convert the form variables into
quoted value list. I know there is a function QuotedValueList() to convert
query column values. Is there anything similar to this for form variables?

 

Thanks in advance.

 

 

Regards,

Vineet Garg.

 

This message and the information contained herein is proprietary and
confidential and subject to the Amdocs policy statement,

you may review at http://www.amdocs.com/email_disclaimer.asp

 

 



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4815
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.15

[Vineet Garg-4]

Thanks Paul,

But isn't there any built in function to do this task?

-----Original Message-----
From: Paul Kukiel [mailto:kukielp@...]
Sent: Wednesday, September 23, 2009 7:37 PM
To: cf-newbie
Subject: RE: How do I put the form variables in quoted value list to make it usable in SQL query


This may help.

 

You can use this to loop over the variables in a form:

 

<!--- dummy form data --->

<cfset form = {} />

<cfset myList = "" />

<cfset form.var1 = "Name" />

<cfset form.var2 = "LastName" />

 

<!--- Bild a csv list --->

<cfloop collection="#form#" item="key">

      <cfset myList &= form[key] & "," />

</cfloop>

 

<!--- drop the last " , " --->

<cfset myList = left(myList,len(myList)-1) />

 

Also remember to use cfqueryparam.

.. and column IN <cfqueryparam list="true" cfsqltype="cf_sql_varchar"
value="#myList#" />

 

Paul.

http://blog.kukiel.net

 

 


From: Vineet Garg [mailto:VINEETG@...]
Sent: Wednesday, September 23, 2009 7:30 AM
To: cf-newbie
Subject: How do I put the form variables in quoted value list to make it
usable in SQL query

 

 

Hi,

 

I have some variables in form which may or may not be defined. Now I want to
use these variables in IN clause of a SQL query. i.e Select * from
<tablename> where <condition> and column IN
('#form.var1$#','#form.var2#'..).

Is there any function in coldfusion to convert the form variables into
quoted value list. I know there is a function QuotedValueList() to convert
query column values. Is there anything similar to this for form variables?

 

Thanks in advance.

 

 

Regards,

Vineet Garg.

 

This message and the information contained herein is proprietary and
confidential and subject to the Amdocs policy statement,

you may review at http://www.amdocs.com/email_disclaimer.asp

 

 





~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4820
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.15

[Vineet Garg-4]

Thanks Paul,

But isn't there any built in function to perform this task?

Vineet Garg.

-----Original Message-----
From: Paul Kukiel [mailto:kukielp@...]
Sent: Wednesday, September 23, 2009 7:37 PM
To: cf-newbie
Subject: RE: How do I put the form variables in quoted value list to make it usable in SQL query


This may help.

 

You can use this to loop over the variables in a form:

 

<!--- dummy form data --->

<cfset form = {} />

<cfset myList = "" />

<cfset form.var1 = "Name" />

<cfset form.var2 = "LastName" />

 

<!--- Bild a csv list --->

<cfloop collection="#form#" item="key">

      <cfset myList &= form[key] & "," />

</cfloop>

 

<!--- drop the last " , " --->

<cfset myList = left(myList,len(myList)-1) />

 

Also remember to use cfqueryparam.

.. and column IN <cfqueryparam list="true" cfsqltype="cf_sql_varchar"
value="#myList#" />

 

Paul.

http://blog.kukiel.net

This message and the information contained herein is proprietary and confidential and subject to the Amdocs policy statement,
you may review at http://www.amdocs.com/email_disclaimer.asp


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4821
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.15

[Dave Phillips-10]

Vineet,

I think the short answer to your question is no.  However, you may or may
not be aware that you can build your own functions (UDF's) in ColdFusion.
There is a site called CFLIB (www.cflib.org) where people have generously
shared many of the UDF's built over the last 10-15 years.  I found a
function there called structToList(), modified it a tad to make it do what
you wanted it to do, and then added a built in function to finish the task.
Here is the finished version (with comments and credit to Greg Nettles,
original author):

<cfscript>
/**
* Converts struct into delimited key/value list.
*
* @param s      Structure. (Required)
* @param delim      List delimeter. Defaults to a comma. (Optional)
* @return Returns a string.
* @author Greg Nettles (gregnettles@...)
* @version 2, July 25, 2006
*/
function structToList(s) {
    var delim = ",";
    var i = 0;
    var newArray = structKeyArray(arguments.s);
 
    if (arrayLen(arguments) gt 1) delim = arguments[2];
 
    for(i=1;i lte structCount(arguments.s);i=i+1) newArray[i] =
arguments.s[newArray[i]];
 
    return arraytoList(newArray,delim);
}
</cfscript>
<cfoutput>#listQualify(structToList(form),"'")#</cfoutput>

The listQualify() function will wrap individual items within the list with
whatever value you give it, in this case, the single quote.  Ultimately, you
would just use this in your query like this:

WHERE <column> IN (<cfqueryparam
value="#listQualify(structToList(form),"'")#" cfsqltype="CF_SQL_VARCHAR"
list="yes">)

Use the cfsqltype of the column you are comparing the list against, so if
it's not varchar, then numeric, etc.

Hope this helps.

Dave

-----Original Message-----
From: Vineet Garg [mailto:VINEETG@...]
Sent: Thursday, September 24, 2009 2:50 AM
To: cf-newbie
Subject: RE: How do I put the form variables in quoted value list to make it
usable in SQL query


Thanks Paul,

But isn't there any built in function to perform this task?

Vineet Garg.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4822
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.15