Re: [plum] How to Implement a Custom Query

[Anne Girardeau]

How would you use the DatabaseBlocks component in conjunction with a custom query? If I can, I would prefer to stick with best practices, especially since I'm still fairly new to CFCs and OOP practices.

 

Thanks,

--Anne

[EMAIL PROTECTED] wrote: -----

To: plum@mail.productivityenhancement.com
From: Jeff Fleitz <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
Date: 11/02/2005 02:20PM
Subject: Re: [plum] How to Implement a Custom Query

I don't think so, other than personal preference.  The DatabaseBlocks component is there to allow us to abstract our data access, and since the framework makes use of calls to that cfc, I do so for consistency.  There is also the issue that most of the CF frameworks are now adopting OOP practices, and is is considered a best practice to access your data through a cfc.

That being said. A cfquery works also ;)

Jeff

On 11/2/05, Anne Girardeau < [EMAIL PROTECTED] "_blank" > wrote:

Just out of curiousity, are there any benefits to performing the query in a cfscript block versus using cfquery?  So far it seems people have done it both ways and I'm not sure why someone would prefer one over the other.

 

Thanks,

--anne

----- [EMAIL PROTECTED] "_blank" wrote: -----

To: plum@mail.productivityenhancement.com "_blank"
From: Jeff Fleitz < [EMAIL PROTECTED] "_blank" >
Sent by: [EMAIL PROTECTED] "_blank"
Date: 11/02/2005 07:46AM
Subject: Re: [plum] How to Implement a Custom Query

Hi Ann,

I do what you are suggesting.  I normally put my custom query at the top of the page in the manner you have done,, but assign it to a variable you can reference in the form tag with the query attribute. Here is an example.

<cfscript>
    qryVoyage = Application.DatabaseBlocks.SelectRecords(selectClause:"Voyage.AccountNumber,Vessel.VesselName", fromClause:"Voyage,Vessel", whereClause:"Voyage.VesselID = Vessel.VesselID AND VoyageID = #Evaluate('#Application.persistentScope#.VoyageID')#");
</cfscript>

Be sure to surround your query variable refererence with pound signs, e.g., query="#qryVoyage#"


--
v/r,

Jeff Fleitz

On 11/1/05, Anne Girardeau < [EMAIL PROTECTED] "_blank" > wrote:

Can someone send me an example of how to use a custom query?   So far I have a UDF that contains the query (properly aliased to use tablename_column instead of tablename.column) and I've included it in the the ViewForm I want to use it in.  However, I'm not certain this is the proper way to do this.  For example, should I instead place the following code in a CFC?

<cfscript>
selectClause = "Make.make AS Make_make, Model.model AS Model_model, ModelYe.year AS ModelYe_year, Style.style AS Style_style, Doors.doors AS Doors_doors, EngineCylinders.engineCylinders AS EngineCylinders_engineCylinders, DriveTrain.driveTrain AS DriveTrain_driveTrain, SaleType.SaleType AS SaleType_SaleType, Transmission.transmission AS Transmission_transmission, InteriorColor.Color AS InteriorColor_intColor, ExteriorColor.Color AS ExteriorColor_extColor, Condition.condition AS Condition_condition, Body.body AS Body_body, Images.ImageName AS Images_ImageName, Images.FileName AS Images_FileName, Inventory.* AS Inventory_*";
fromClause = "Transmission RIGHT JOIN (Style RIGHT JOIN (SaleType RIGHT JOIN (ModelYe RIGHT JOIN (Model RIGHT JOIN (Make RIGHT JOIN ((EngineCylinders RIGHT JOIN (DriveTrain RIGHT JOIN (Doors RIGHT JOIN (Condition RIGHT JOIN (Body RIGHT JOIN (Colors AS InteriorColor RIGHT JOIN (Colors AS ExteriorColor RIGHT JOIN Inventory ON ExteriorColor.colorID = Inventory.extColorID) ON InteriorColor.colorID = Inventory.intColorID) ON Body.bodyID = Inventory.bodyID) ON Condition.conditionID = Inventory.conditionID) ON Doors.doorID = Inventory.doorsID) ON DriveTrain.DriveID = Inventory.driveID) ON EngineCylinders.engineID = Inventory.engineID) LEFT JOIN Images ON Inventory.inventoryID = Images.InventoryID) ON Make.makeID = Inventory.makeID) ON Model.modelID = Inventory.modelID) ON ModelYe.yearID = Inventory.yearID) ON SaleType.SaleID = Inventory.SaleID) ON Style.styleID = Inventory.styleID) ON Transmission.transmissionID = Inventory.transID";
</cfscript>

If so, where should I save the CFC and how should I reference it in the application?  Any help with this would be greatly appreciated since I can't seem to find anything that documents a specific procedure.

Thanks so much,

--Anne

Anne D. Girardeau
Lead Web Developer
Corporate Information Systems (CIS)
Health and Hospital Corporation
(317) 221-2338
www.hhcorp.org "_blank" "_blank"

Confidentiality Notice: This E-Mail transmission may contain confidential or legally privileged information that is intended only for the individual or entity named in the E-Mail address. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or reliance upon the contents of this E-Mail is strictly prohibited. If you have received this E-Mail transmission in error, please reply to the sender, so arrangements can be made for proper delivery, and then delete the message from your system. Thank you.
********************************************************************** You can subscribe to and unsubscribe from lists, and you can change your subscriptions between normal and digest modes here: http://www.productivityenhancement.com/support/DiscussionListsForm.cfm "_blank" "_blank" **********************************************************************

Confidentiality Notice: This E-Mail transmission may contain confidential or legally privileged information that is intended only for the individual or entity named in the E-Mail address. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or reliance upon the contents of this E-Mail is strictly prohibited. If you have received this E-Mail transmission in error, please reply to the sender, so arrangements can be made for proper delivery, and then delete the message from your system. Thank you.
********************************************************************** You can subscribe to and unsubscribe from lists, and you can change your subscriptions between normal and digest modes here: http://www.productivityenhancement.com/support/DiscussionListsForm.cfm "_blank" **********************************************************************

--
v/r,

Jeff Fleitz

Confidentiality Notice: This E-Mail transmission may contain confidential or legally privileged information that is intended only for the individual or entity named in the E-Mail address. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or reliance upon the contents of this E-Mail is strictly prohibited. If you have received this E-Mail transmission in error, please reply to the sender, so arrangements can be made for proper delivery, and then delete the message from your system. Thank you.

********************************************************************** You can subscribe to and unsubscribe from lists, and you can change your subscriptions between normal and digest modes here: http://www.productivityenhancement.com/support/DiscussionListsForm.cfm **********************************************************************