Actually, I remember one time at work we considered using a similar approach. The purpose was that we were planning on having some sort of database failover mechanism embedded in the code. So all database access on the application would go through the same channel. This method would execute the queries and detect if there was an IO exception. If the exception was due to the DB being unreachable it would switch the DSN to the next one until it ran out of DSNs (we would initialize the query cfc with a bunch of DSNs pointing to different (replicated) databases). Now this was more of a thought experiment, and never went ahead an implemented it. @Alan, this does not ***necessarily*** imply that the SQL code will be scattered through out the application. This could just be the component that sits at the bottom-most layer of the application, and could have a fairly concise DAO/Gateways layer on top of it. And this DAOs or Gateways would rely on this particular component to send the final call to the DB. Though the cfqueryparam would still be an issue...
On Sun, Nov 16, 2008 at 5:31 AM, Alan Livie <[EMAIL PROTECTED]> wrote: > oops ... I said... > > 'onMissingMethod() could parse this and call getCustomersByFields() passing > the key and values from your arguments scope.' > > I meant pass the values from arguments scope and parse the key/column from > the method name. > > Alan > > ------------------------------ > *From:* Alan Livie <[EMAIL PROTECTED]> > *To:* [email protected] > *Sent:* Sunday, November 16, 2008 10:23:15 AM > > *Subject:* [CFCDEV] Re: using one component for all queries of enterprise > application - is good or bad > > Two of the reasons sql is moved in specific cfc's are encapsulating the > database-specific code and also making it easy for developers and dba's to > find the sql in an application. > > With your approach you achieve neither because the sql is still scattered > all over your application. > > Why not create specific CFC's for acceess to your various tables ie have a > CustomerGateway.cfc with all the <cfquery>s you need for customers ie > getAll() , getById() etc > then your calling code can use CustomerGateway.getById(url.customerId) > > Also, with CF8 you have onMissingMethod() so can even have > onMissingMethod() allow you to do Rails-esque things like > getCustomersByFirstnameAndLastname("Joe","Bloggs") (onMissingMethod() could > parse this and call getCustomersByFields() passing the key and values from > your arguments scope. > > You can cache the cfc in Application scope and give it the dsn in its > init() method when its created so you'll never pass the dsn in any method > calls to it from your calling code. > > These will make your calling code de-coupled from the db implementation, > give your dba an easy way to find all the sql and make your code more > readable and maintainable. > > There are lots of articles on these topics so hunt around for DAOs, > Gateways, TableDataGateway and also look at frameworks like Transfer or > Reactor which can also save you lots of time writing cfc's with sql queries. > > Alan > > ------------------------------ > *From:* prashant roy <[EMAIL PROTECTED]> > *To:* [email protected] > *Sent:* Sunday, November 16, 2008 8:27:14 AM > *Subject:* [CFCDEV] Re: using one component for all queries of enterprise > application - is good or bad > > Hi David/ Mike, > > thanks for discussing with me. > you guys are very right. I am going for some more overhead. > > below are the few facts I planned for this query factory. > 1. we are going towards object oriented concepts for our new application > so trying to give shape in objects. > 2. I can less my below code to one line code but still you are right that i > am doing overhead. > <cfset querier = createObject( "QueryFactory" ) /> > <cfset querier.setDSN( "BSParty" ) /> > <cfset querier.setQueryString( "select * from table" ) /> > <cfset results = querier.execute() /> > > 3. trying to less CFQuery tags from all code and will make things shorter > something like this > <cfset sqlstatement="select * from titles"> > <cfset results = querier.execute(QueryName,SQLStatement)> > > 4. One more Question: if I use Java SQL Service objects to execute the > query. is it beneficial for performance wise. > here is the link for using SQL java objects " > http://www.bennadel.com/index.cfm?dax=blog:186.viewcode&index=2"; > I am Confused, I know CFQuery tag is good. > > -prashant > > On Sat, Nov 15, 2008 at 10:52 PM, Mike Chabot <[EMAIL PROTECTED]> wrote: > >> >> I agree with David on this one. Seems like a lot of work and >> additional overhead with no obvious benefit. Cfquery is a great tag. >> Also, have you considered the benefits of using cfqueryparam or stored >> procedures? Have you considered how to prevent SQL injection attacks? >> Have you considered that some queries might benefit from caching? >> >> Database queries are nearly always the bottleneck in a Web >> application. With the setup above it will likely be more difficult to >> troubleshoot and fix common query performance problems. >> >> -Mike Chabot >> >> On 11/16/08, David McGuigan <[EMAIL PROTECTED]> wrote: >> > Sorry, I was doing 3 things at once. >> > >> > Revised code sample: >> > <cfset querier = createObject( "QueryFactory" ) /> >> > <cfset querier.setDSN( "BSParty" ) /> >> > <cfset querier.setQueryString( "select * from table" ) /> >> > <cfset results = querier.execute() /> >> > >> > >> > On Sat, Nov 15, 2008 at 11:11 PM, David McGuigan >> > <[EMAIL PROTECTED]>wrote: >> > >> >> What are you actually trying to accomplish here? >> >> >> >> Just glancing at your CFC, it seems like a simple query to the database >> >> >> >> <cfquery name="getBS" datasource="BSParty" > >> >> select * from table >> >> </cfquery> >> >> >> >> Has now become: >> >> <cfset querier = createObject( "QueryFactory" ) /> >> >> <cfset querier.setDSN = "BSParty" /> >> >> <cfset querier.setQueryString = "select * from table" /> >> >> <cfset results = querier.execute() /> >> >> >> >> I'm wondering what the point is? If it's to eliminate the need to >> specify >> >> a >> >> datasource in every query, you're doing just as much work here with >> these >> >> fruitless methods. One of the strengths of the cfquery tag is that it >> >> wraps >> >> around content for clear, readable formatting of SQL. With your CFC the >> >> only >> >> way to preserve that would be cfsavecontent, which will now add even >> more >> >> code to your plate for querying the database. >> >> >> >> I'm really curious. What are the benefits/goals of the approach? >> >> >> >> >> >> >> >> >> >> >> >> On Sat, Nov 15, 2008 at 10:40 PM, prashant roy >> >> <[EMAIL PROTECTED]>wrote: >> >> >> >>> right. i am just worrying. >> >>> if my one component will serving all my queries then how much it >> affects >> >>> the performance of website. load testing is good thing to go ahead >> >>> >> >>> On Sat, Nov 15, 2008 at 9:28 PM, Oscar Arevalo <[EMAIL PROTECTED] >> >wrote: >> >>> >> >>>> If you are picky about performance, you may want to get rid of the >> >>>> "isDefined" too... >> >>>> >> >>>> >> >>>> On Sat, Nov 15, 2008 at 11:27 PM, Barney Boisvert >> >>>> <[EMAIL PROTECTED]>wrote: >> >>>> >> >>>>> >> >>>>> if performance is the question, load testing is the answer. If even >> >>>>> simple load testing is too much work, then it's fast enough. :) >> >>>>> >> >>>>> cheers, >> >>>>> barneyb >> >>>>> >> >>>>> On 11/15/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> >> >>>>> wrote: >> >>>>> > >> >>>>> > Hi All, >> >>>>> > >> >>>>> > I am using a "Query Factory" component for all queries for my big >> >>>>> > Application. I want to know is it good or bad to use in place of >> >>>>> > CFquery as performance wise. >> >>>>> > >> >>>>> > I am using same kind of process for Object Factory. so its good or >> >>>>> > bad >> >>>>> > performance wise? >> >>>>> > Pls advice. >> >>>>> > Here is the code for QueryFactory >> >>>>> > -------------------------------------------------- >> >>>>> > >> >>>>> > <!---- >> >>>>> > >> >>>>> > <cfcomponent output="false" hint="query factory" >> >>>>> > extends="exception.load"> >> >>>>> > >> >>>>> > <cfscript> >> >>>>> > init(); >> >>>>> > </cfscript> >> >>>>> > >> >>>>> > <cffunction name="init" access="public" output="false" >> >>>>> > returntype="any" hint="Initialize the query factory object"> >> >>>>> > <cfargument name="DSN" required="false" type="any" >> >>>>> > default="DSNOfApp" hint="DSN for query" /> >> >>>>> > <cfset var dsn = Arguments.DSN> >> >>>>> > >> >>>>> > <cfscript> >> >>>>> > if (trim(dsn) eq "") >> >>>>> > { >> >>>>> > >> dsn="defaultdsnforyourapplication";// >> >>>>> default dsn for ur >> >>>>> > application >> >>>>> > } >> >>>>> > setDSN(DSN); >> >>>>> > setQueryString(""); >> >>>>> > </cfscript> >> >>>>> > </cffunction> >> >>>>> > >> >>>>> > <cffunction name="getType" access="public" returntype="any" >> >>>>> > output="false" hint="Returns the objects type"> >> >>>>> > <cfreturn "utility.queryFactory" /> >> >>>>> > </cffunction> >> >>>>> > >> >>>>> > <cffunction name="setDSN" access="private" output="false" >> >>>>> > returntype="void" hint="Sets DSN for query"> >> >>>>> > <cfargument name="newDSN" type="string" >> required="true" >> >>>>> /> >> >>>>> > <cfSet Variables.DSN = Arguments.newDSN /> >> >>>>> > </cffunction> >> >>>>> > >> >>>>> > >> >>>>> > >> >>>>> > <cffunction name="getDSN" access="private" output="false" >> >>>>> > returntype="any" hint="Gets DSN for query"> >> >>>>> > <cfscript> >> >>>>> > if (isDefined('Variables.DSN')){ >> >>>>> > return Variables.DSN; >> >>>>> > }else{ >> >>>>> > return ''; >> >>>>> > } >> >>>>> > </cfscript> >> >>>>> > </cffunction> >> >>>>> > >> >>>>> > <cffunction name="setQueryString" access="public" >> >>>>> > output="false" >> >>>>> > returntype="void" hint="Sets sql statements for query"> >> >>>>> > <cfargument name="newQueryString" type="string" >> >>>>> required="true" /> >> >>>>> > <cfSet Variables.QueryString = >> Arguments.newQueryString >> >>>>> /> >> >>>>> > </cffunction> >> >>>>> > >> >>>>> > <cffunction name="getQueryString" access="public" >> >>>>> > output="false" >> >>>>> > returntype="any" hint="Gets sql statements for query"> >> >>>>> > <cfscript> >> >>>>> > if (isDefined('Variables.QueryString')){ >> >>>>> > return Variables.QueryString; >> >>>>> > }else{ >> >>>>> > return ''; >> >>>>> > } >> >>>>> > </cfscript> >> >>>>> > </cffunction> >> >>>>> > >> >>>>> > <cffunction name="getResultSet" access="public" >> output="false" >> >>>>> > returntype="any" hint="Gets the resultset"> >> >>>>> > <cfscript> >> >>>>> > if (isDefined('variables.resultSet')){ >> >>>>> > return variables.resultSet; >> >>>>> > }else{ >> >>>>> > return ''; >> >>>>> > } >> >>>>> > </cfscript> >> >>>>> > </cffunction> >> >>>>> > >> >>>>> > <cffunction name="setResultSet" access="private" >> output="false" >> >>>>> > returntype="any" hint="Sets the resultset"> >> >>>>> > <cfargument name="newResultSet" type="any" >> >>>>> required="true" /> >> >>>>> > <cfset variables.resultSet = Arguments.newResultSet> >> >>>>> > </cffunction> >> >>>>> > >> >>>>> > <cffunction name="execute" access="public" output="false" >> >>>>> > hint="executes the query"> >> >>>>> > <cfset variables.qry=""> >> >>>>> > >> >>>>> > <cfquery name="qry" datasource="#getDSN()#"> >> >>>>> > #getQueryString()# >> >>>>> > </cfquery> >> >>>>> > >> >>>>> > <cfscript> >> >>>>> > setResultSet(variables.qry); >> >>>>> > </cfscript> >> >>>>> > >> >>>>> > </cffunction> >> >>>>> > >> >>>>> > </cfcomponent> >> >>>>> > >> >>>>> > >> >>>>> > > >> >>>>> > >> >>>>> >> >>>>> >> >>>>> -- >> >>>>> Barney Boisvert >> >>>>> [EMAIL PROTECTED] >> >>>>> http://www.barneyb.com/ >> >>>>> >> >>>>> >> >>>>> >> >>>> >> >>>> >> >>>> -- >> >>>> Oscar Arevalo >> >>>> http://www.oscararevalo.com >> >>>> >> >>>> >> >>>> >>> >> >>>> >> >> >> > >> > > >> > >> >> > > > > > > > > > > -- Oscar Arevalo http://www.oscararevalo.com --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "CFCDev" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfcdev?hl=en -~----------~----~----~----~------~----~------~--~---
