> Hi,
> I need to build a SQL update statement dynamically,
> based on what form fields are being passed in at a
> particular time. The database is MS SQL Server.
<snip>
> But this doesn't seem possible, since I have to
> output the SQL string in the cfquery statement.
> I can build the cfqueryparam statements into the
> string, but they aren't evaluated.
> Is there any way to use cfqueryparam in this
> situation? And if not, what is the proper way to
> escape those single quotes so that I can
> leave them within the individual text string?
Hi Tom,
What you need to do to accomplish this in a reuseable way without
knowing in advance what columns/data will be used is to create a
structure which will be able to take and store "search criteria meta
data" which can later be used within the cfquery tag. Rather than
building the entire sql statement as a string, you would build only
the select and order-by clauses and then use your created structure to
build the where clause within your reuseable tag or function.
As an example, you might have a structure which looks like this:
aWhere (array)
aWhere[1] (struct)
aWhere[1].column = "column1"
aWhere[1].type = "varchar"
aWhere[1].content = "blah blah"
aWhere[1].compare = "like"
aWhere[2] (struct)
aWhere[2].column = "column2"
aWhere[2].type = "integer"
aWhere[2].content = 3
aWhere[2].compare = "="
in your cfquery tag you would then loop over your structure to create
your where clause like this:
<cfquery ...>
#preservesinglequotes(selectstatement)#
<cfloop index="x" for="1" to="#arraylen(aWhere)#">
#aWhere[x].column# #aWhere[x].compare#
<cfqueryparam value="#aWhere[x].content#"
cfsqltype="cf_sql_#aWhere[x].type#">
</cfloop>
#preservesinglequotes(orderby)#
</cfquery>
The onTap framework has a lot of query tools which use this sort of
structure for generating queries using cfqueryparam tags... although
the framework's tools are fairly extensive and likely much more than
you need to accomplish what you're working on currently, so it's
probably easier to build this new than to remove all the extra code
from what I've written for this.
s. isaac dealey 954.522.6080
new epoch : isn't it time for a change?
add features without fixtures with
the onTap open source framework
http://macromedia.breezecentral.com/p49777853/
http://www.sys-con.com/author/?id=4806
http://www.fusiontap.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:204783
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
