Now, here is a quick snippet of a MS SQl Server solution that is quite neat.
CF:
<cffunction name="genericQuery" access="public" output="false"
returntype="query">
<cfargument name="field" required="true" type="string">
<cfargument name="value" required="true" type="any">
<cfargument name="dataType" required="true" type="string">
<cfstoredproc datasource="test" procedure="usp_TestSP">
<cfprocparam variable="#arguments.field#" value="#arguments.value#"
cfsqltype="#arguments.dataType#">
<cfprocresult name="qTest">
</cfstoredproc>
<cfreturn qTest>
</cffunction>
<cfset query = genericQuery("entryCol1","Event","cf_sql_varchar")>
<cfdump var="#query#">
The above CF just passes in the name of the column, the data inside of the
column and the data type of the column. You can put the function into any
CFC that you like for generic non-dependent queries.
Stored Procedure:
alter procedure dbo.usp_TestSP
(
@entryCol1 varchar(50) = null
, @entryID int = null
)
as
select
*
from
tblEntry
where
entryCol1 = COALESCE(@entryCol1,entryCol1)
and
entryID = COALESCE(@entryID,entryID)
The hairy part of this is two parts. First, you want to have all the
columns of your table as a parameter of the stored procedure that havea
default of null. Second, you want to have every column in your table to
have an coalesce() clause in the where statement.
How it works is that if the value of the a variable is NULL, then the
expression does not even get executed because it would be comparing two NULL
named columns together and thus they don't exist.
So if you passed a column without a value (empty string) in the above
example, you would get everything because the the where caluse would not be
executed. If you pass the column and a value, you will see a filtered data
result set.
Without using BlueDragon, this is actually one technique to simulate a NULL
data type.
When CF translates the empty string to a NULL defaulted procedure param,
NULL is the end result regardless if the variable is really a NULL data type
or not.
Here is a link that explains it more in depth:
http://www.sqlteam.com/item.asp?ItemID=2077
It is the best explnation of it that I can find.
Cheers,
Teddy
On 2/2/07, John Mason <[EMAIL PROTECTED]> wrote:
Don't forget to use cfqueryparam...
John
[EMAIL PROTECTED]
------------------------------
*From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] *On Behalf Of *
[EMAIL PROTECTED]
*Sent:* Friday, February 02, 2007 12:05 PM
*To:* [email protected]
*Subject:* Re: [ACFUG Discuss] CFC and reuse query
Anthony:
If you have three columns that you want to query on you, can do the
following:
<cfcomponent>
<cffunction name="myFunction" access="public" returntype="string">
<cfargument name="myArgument1" type="string" required="yes">
<cfargument name="myArgument2" type="string" required="yes">
<cfargument name="myArgument3" type="string" required="yes">
<cfset myResult="foo">
<cfquery name="qFoo" datasource="datasource">
select *
from fooTable
where 1=1
and column1 = '%myArgument1%'
and column2 = '%myArgument2%'
and column3 = '%myArgument3%'
</cfquery>
<!--- or you could do: (this avoids wild cards) --->
<cfquery name="qFoo" datasource="datasource">
select *
from fooTable
where 1=1
<cfif len(trim(myArgument1))> and column1 = 'myArgument1'</cfif>
<cfif len(trim(myArgument2))> and column2 = 'myArgument2'</cfif>
<cfif len(trim(myArgument3))> and column3 = 'myArgument3'</cfif>
</cfquery>
<cfreturn myResult>
</cffunction>
</cfcomponent>
Sincerely,
Brooks Wilson
Try not to become a man of success but rather to become a man of value. -
Albert Einstein (1879-1955)
Federal Reserve Bank of Atlanta · 1000 Peachtree Street, N.E. · Atlanta
Georgia 30309-4470
404.498.8178 · fax 404.498.8239 · [EMAIL PROTECTED]
*Anthony Mathis <[EMAIL PROTECTED]>*
Sent by: [EMAIL PROTECTED]
02/02/2007 11:39 AM Please respond to
[email protected]
To
[email protected] cc
Subject
[ACFUG Discuss] CFC and reuse query
I'm looking for a solution to reusing a query in a cfc where the "where
clause needs to change.
example:
<cfquery name="qry" datasource="#APPLICATION.dsn#">
select * from table
where #field# = #value#
</cfquery>
How would I pass the info from my cfm to the cfc and continue to run the
same query from the same cfm using different values for #field# and
#value#? Can this be done?
thanks,
Anthony
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform<http://www.acfug.org/?fa=login.edituserform>
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by http://www.fusionlink.com
-------------------------------------------------------------
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform<http://www.acfug.org/?fa=login.edituserform>
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by FusionLink <http://www.fusionlink.com/>
-------------------------------------------------------------
--
Teddy R. Payne
G-Talk: [EMAIL PROTECTED]
Adobe Certified ColdFusion MX 7 Developer
Atlanta ColdFusion User Group (ACFUG)
Atlanta Flash & Flex User Group (AFFUG)
