Oracle won't accept a PL-SQL batch in a CFQuery, properly parsed with
semicolons? Wow! If true, that really stinks. You should be able to send
any SQL you like -- I know SQL Server will do it. Of course, Oracle doesn't
support ANSI-style table JOINS, either, which is just silly.
At one of the sessions at the Allaire Dev Conference last year in Boston,
one of their techs explained that you want to keep the execution time
between the start and end tags of a CFQUERY to an absolute minimum. The
driver connection is held open for the entire amount of time it takes to
digest the contents. The tech recommended that we use variables like this
to build really complex SQL, placing CFLOOPS and lengthy logic outside of
the CFQUERY block.
A few inline functions (especially VAL() for numeric parameters) or <CFIF>s
are fine, but I would build an SQL string for anything more involved than
that.
See you guys in D.C. in November.
Christian Gustafson
Senior Software Engineer
Onvia.com
Work. Wisely.
http://www.onvia.com
(p) 206.373.9307
(c) 206.852.6095
-----Original Message-----
From: Dan Haley [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 17, 2000 9:23 AM
To: CF-Server
Subject: RE: question about quotations
Depending on the database this does not work. Oracle doesn't allow multiple
SQL statements in a single <cfquery>. Or if it does, I haven't seen how to
do it.
As for your comment "and never put anything more complex than a simple
function call into a CFQuery block" - why? Personally I prefer building my
complex dynamic queries inside the <cfquery> where I can follow the logic
better, rather than trying to create a variable with the SQL in it - reminds
me of creating SQL statements in VB!
Dan
-----Original Message-----
From: Christian Gustafson [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 16, 2000 5:22 PM
To: CF-Server
Subject: RE: question about quotations
If you're going to take this route, you should store the SQL in a variable
and submit it to the DB in a single batch, and even add transactional code
if you like:
<cfset Variables.SQL="">
<cfloop list="#form.phone_number#" index="i" delimiters=",">
<cfset Variables.SQL=Variables.SQL & "delete phone_detail where
phone = '#i#';">
</cfloop>
<cfquery name="deletePhone" datasource="#application.dsn#">
#PreserveSingleQuotes(Variables.SQL)#
</cfquery>
As a good rule of thumb, never loop over queries, and never put anything
more complex than a simple function call into a CFQuery block.
Cheers,
Christian Gustafson
Senior Software Engineer
Onvia.com
Work. Wisely.
http://www.onvia.com
(p) 206.373.9307
(c) 206.852.6095
The information contained in this message is privileged and confidential.
It is intended solely for the use of the named recipient. If you are not
the intended recipient, you are hereby notified that any disclosure,
copying, distribution, or use of the contents of this transmission is
strictly prohibited. If you receive this message in error, please notify
the sender immediately. Thank you.
----------------------------------------------------------------------------
--
To unsubscribe, send a message to [EMAIL PROTECTED] with
'unsubscribe' in the body or visit the list page at www.houseoffusion.com
------------------------------------------------------------------------------
To unsubscribe, send a message to [EMAIL PROTECTED] with
'unsubscribe' in the body or visit the list page at www.houseoffusion.com
