> > Also, if you're using a database engine that allows TSQL, > > then put the loop inside the CFQUERY and put a ; at the end > > of each INSERT > > This looks like a good tip, can this be done with other Databases as > well? i.e. Oracle? Have always coded the loop outside the cfquery > tag, anybody got any performance info on this?
AFAIK, any of the "big" SQL engines, SQL Server, Oracle and such all deal with multiple requests in one query, this saves traffic to the database server... > > > <cfloop list="#form.Accomidations#" index="x"> > > > <cfquery datasource="blah" name="insertAccomidation"> > > > insert into tblAccomidations (personKey, > > > accomitationId) values(pid,x) > > > </cfquery> > > > </cfloop> > > > > Not forgetting to put #'s around your variables; > > > > insert into tblAccomidations (personKey, accomitationId) > > values(#pid#,#x#) > > Jeez, where have all the cfqueryparam's gone.... can somebody > tell me if > there is a reason *not* to use them in a hard coded query? Should be > using them all the time where possible (unless your an uber coder and > you just *know* the reason on why not to use them in that particular > instance) Yes, you SHOULD be using them whenever there is a CF value, I was just using it without as the original code sample wasn't referring to the CF variables at all The big problem with them is that if you expect an Integer and it's being passed a Varchar, then you have to trap the error yourself with CFTRY... But you can get around this by dealing with any non-numerics before the query... I'm a BIG advocate of CFQUERY as it speeds up your queries as well as the validation (depending on your database engine - Your Mileage May Vary) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
