Yes, that sounds like a good idea.
I don't think CFQUERYPARAM would have helped you with
performance anyway. It just validates the parameters
you pass to the stored procedure.
I'm guessing here but are you actually looking for the
syntax for passing parameters to an Oracle stored procedure?
In which case you want something like
(CF template)
<cfstoredproc procedure="procname"....>
<cfprocparam type="in" dbvarname="variablename" value="#value#"
cfsqltype="cf_sql_varchar ...or whatever...">
</cfstoredproc>
(SQL for creating the stored procedure)
create or replace procedure procname
(variablename in varchar2....) as
begin
select columnname from tablename
where columnname = variablename;
.......
end procname;
I think you only need to use *bind variables* if you are going
to build the SQL statement dynamically within the stored
procedure and execute it with EXECUTE IMMEDIATE. But that's only
if you don't know which columns ar tables are going to be
referenced until runtime.
Nick
-----Original Message-----
From: Terri Stocke [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 05, 2001 4:19 PM
To: CF-Talk
Subject: RE: Syntax for binding a query?
Oops. Yes, I should have been more specific :) and stipulated "bind
variables". I'm talking about improving our db performance by "caching" the
best execution paths for the queries.
I know 4.5 uses the CFQUERYPARAM tag, but alas, we are still on 4.0.
I was hoping that there would be an equivalent way in 4.0 that would be
relatively painless, but from what I've found so far, it looks like our only
choice would be to used stored procedures to pass the variables to Oracle
and allow Oracle to do the rest...
----Original Message Follows----
From: "DeVoil, Nick" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: CF-Talk <[EMAIL PROTECTED]>
Subject: RE: Syntax for binding a query?
Date: Mon, 05 Feb 2001 15:26:04 -0000
> Can anyone tell me what the SQL syntax is for binding a query (Oracle
db)?
Terri,
Not sure what you mean.
To me "binding a query" refers to the action of associating values
with variable names when when querying a database. In SQL this is
simply done by saying "WHERE columnname = value".
Or are you asking about "bind variables" in Oracle PL/SQL?
These are local variables in stored procedures or triggers which you
declare "VARIABLE variablename datatype" and refer to by saying
something like ":variablename := value"
Nick
**********************************************************************
Information in this email is confidential and may be privileged.
It is intended for the addressee only. If you have received it in error,
please notify the sender immediately and delete it from your system.
You should not otherwise copy it, retransmit it or use or disclose its
contents to anyone.
Thank you for your co-operation.
**********************************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
