?
Solved

Best practice for CFQuery: using DBName as attribute or in SQL?

Posted on 2008-10-28
7
Medium Priority
?
692 Views
Last Modified: 2012-06-27
When using CFQuery, is it best to have the database name as an attribute (example code 1) or as part of the SQL (example code 2)?  

Does this make a difference for ColdFusion query caching (cachedWithin)?

(currently using ColdFusion 8)
example 1:
<cfquery name="qCountry" datasource="LOCALDSN" dbname="#curr_db_name#">
SELECT * FROM tblCountry WHERE country_id = #cid#
</cfquery
 
example 2
<cfquery name="qCountry" datasource="LOCALDSN">
SELECT * FROM #curr_db_name#.dbo.tblCountry WHERE country_id = #cid#
</cfquery

Open in new window

0
Comment
Question by:paid_tech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 19

Expert Comment

by:erikTsomik
ID: 22822790
it does not really matter the datasource get setup in CF administrator which will point to the DB file. So what you doing is absolutely identicall and has no effect on the perforance
0
 
LVL 36

Accepted Solution

by:
SidFishes earned 500 total points
ID: 22822896
actually dbname is deprecated and should not be used

"Deprecated the connectString, dbName, dbServer, provider, providerDSN, and sql attributes, and all values of the dbtype attribute except query. They do not work, and might cause an error, in releases later than ColdFusion 5. "

from livedocs
0
 

Author Comment

by:paid_tech
ID: 22823575
thank you for pointing out the deprecation of dbname
(http://www.cfquickdocs.com/cf8/?getDoc=cfquery#cfquery)
just to clarify I have about 40 databases, one for each site, and they are all setup through the same ColdFusion Datasource/DSN.

1) if its possible to switch to a DSN for each database, should we change to that?
2) So should I use the format of example 2, with the dname in front of each table name in the SQL? (example below)

<cfquery name="qCountry" datasource="LOCALDSN">
SELECT * 
FROM #curr_db_name#.dbo.tblCountry  AS c
LEFT OUTER JOIN #curr_db_name#.dbo.tblCountryStatus AS cs ON c.country_id = cs.country_id
WHERE country_id = #cid#
</cfquery

Open in new window

0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
LVL 36

Assisted Solution

by:SidFishes
SidFishes earned 500 total points
ID: 22823745
yes example 2 would be the best approach...i believe it's actually a bit better than multiple dsn's as it use the db's power rather than cf's and that's always a good thing

you could try putting the variable in application scope if each site has it's own codebase

FROM #application.curr_db_name#.dbo.tblCountry  AS c

0
 

Author Comment

by:paid_tech
ID: 22824209
thank you SidFish

also does this approach (with dbname in SQL) affect the ColdFusion query caching?

would ex 3 & 4 be considered different queries by ColdFusion, and hence be cached as different queries?

ex 3:
<cfquery name="qCountry" datasource="LOCALDSN">
SELECT * 
FROM funSite.dbo.tblCountry  AS c
LEFT OUTER JOIN funSite.dbo.tblCountryStatus AS cs ON c.country_id = cs.country_id
WHERE country_id = 5
</cfquery>
 
ex 4:
<cfquery name="qCountry" datasource="LOCALDSN">
SELECT * 
FROM testSite.dbo.tblCountry  AS c
LEFT OUTER JOIN testSite.dbo.tblCountryStatus AS cs ON c.country_id = cs.country_id
WHERE country_id = 5
</cfquery>

Open in new window

0
 
LVL 36

Assisted Solution

by:SidFishes
SidFishes earned 500 total points
ID: 22824626
"To pull from the cache, more than just the name of the query must match. Here's the list:

    * Same query "Name"
    * Exact same SQL statement - "where username='bubbaLouie'" and "where username = 'samIam'" are 2 different statements, ergo 2 different queries in the cache - even if they are both "named" NightOnTown.
    * Same Datasource - for those of you who fail to assume and stumbled onto that thought.
    * Same Username and password - This is interesting to note. If you have a site with a shared datasource but multiple db usernames you may not get the benefit from caching that you think you should.
    * Same DBTYPE"

http://mkruger.cfwebtools.com/index.cfm?mode=entry&entry=EAA0D1CA-01F6-F3EC-5520AAD6EEC68061


that being said, if you're not using (which your examples don't)

cachedwithin="#crateTimespan(0,010,0)#" in your cfquery tag you're not using caching anyways... (and caching has to be enabled in cfadmin)

for the benefit of future readers of this q, as noted in the article, versions prior to CF8 could not use cached queries -and- cfqueryparam. This is a major problem as imho, there is no circumstance where you should eliminate the use of cfqueryparam as protection agaisnt sql injection even if it means giving up server performance.




0
 

Author Closing Comment

by:paid_tech
ID: 31510776
Thank you very much, your answers were detailed and easy to understand
0

Featured Post

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question