and return parameters. The
CFSTOREDPROC tag allows for all that. Also, you can't use CFQUERY on all
platforms to execute stored procedures, as I recall. But if you don't have
any of these issues there's nothing wrong with using CFQUERY instead, and
there are some advantages, such as the ability to use
Just to add a bit to the thread:
cfstoredproc is, in general, a little more secure. It allows you to
specify the data type of parameters, which can help with security.
Stored procedures are generally harder to break out of during a SQL
injection attack. Also, a stored procedure can have more
I've found that they work really well together. I generally develop an app with
cfquery, and change them all to cfstoredproc before I put it into production.
I just want to point out that the topic of this thread is that one can CALL
simple stored procedures with cfquery... tags, so what
ok...
question.
i have always used a stored proc to initally grab my data set.
but then use cfquery to re-sort the data as well as page though it..
i am not that quick at dba stuff, but is the way you guys do it?
or do you pass your sort orders paging back to the proc..
thx
website at http://www.reedexpo.com
-Original Message-
From: Ian Skinner
To: CF-Talk
Sent: Thu Feb 22 18:12:12 2007
Subject: RE: cfstoredproc vs cfquery
I've found that they work really well together. I generally develop an app
with cfquery, and change them all to cfstoredproc before I put
-Original Message-
From: Paul Ihrig
To: CF-Talk
Sent: Thu Feb 22 18:34:49 2007
Subject: Re: cfstoredproc vs cfquery
ok...
question.
i have always used a stored proc to initally grab my data set.
but then use cfquery to re-sort the data as well as page though it..
i am not that quick at dba stuff
of Graduate Studies
University of California, Irvine
http://www.rgs.uci.edu/
949.824.6363
-Original Message-
From: Paul Ihrig [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 22, 2007 10:35 AM
To: CF-Talk
Subject: Re: cfstoredproc vs cfquery
ok...
question.
i have always used a stored proc
Before I spend a load of time that I really do not have available at present
experimenting with this I thought that I would ask the question here. I was
wondering if anybody has ever dynamically built a cfstoredproc tag and then
executed it. The situation I have is that I have ben asked
the question
here. I was wondering if anybody has ever dynamically built a cfstoredproc
tag and then executed it. The situation I have is that I have ben asked if
it is possible to dynamically build a cfstoredproc tag on the fly when you
only know the number and type of parameters and SQL
On 12/12/06, Andy Mcshane [EMAIL PROTECTED] wrote:
Before I spend a load of time that I really do not have available at
present experimenting with this I thought that I would ask the question
here. I was wondering if anybody has ever dynamically built a cfstoredproc
tag
The situation I have is that I have ben asked if it is possible to dynamically
build a cfstoredproc tag on the fly when you only know the number and type
of parameters and SQL stored procedure name at run time. Has anybody
ever done anything like this before?
No, I haven't, but you could
Andy,
I think you can still execute procs in CFQUERY. If you want, try using CF
to build up the SQL that calls the proc in variables, and then send it to
the server by putting the #sqlprocstmt# inside a CFQUERY block. e.g.:
CFQUERY ... #sqlprocstmt#/CFQUERY
Executing procs via CFQUERY limits
I would really appreciate it if someone could
Show me a simple example of how to use cfstoreproc
With a MSSQL stored procedure and then the code to
Display the results of that.
I have worked with Access for years and am relatively new
To MSSQL and have never used that tag. We have a new
software
:[EMAIL PROTECTED]
Sent: Friday, November 03, 2006 1:04 PM
To: CF-Talk
Subject: cfstoredproc
I would really appreciate it if someone could
Show me a simple example of how to use cfstoreproc
With a MSSQL stored procedure and then the code to
Display the results of that.
I have worked
Hi Terry,
Hope this helps I have popped some cf code together and a dummy stored
procedure:
-
!--- *** COLDFUSION CODE: CALL THE STORED PROCEDURE AND PASS IN SOME
DATA***
cfstoredproc procedure=sp_SomeStoredProcedureName
datasource=YourDatabase
cfprocparam value=21
cfstoredproc procedure=NAME OF STORED PROC
datasource=ColdFusion Datasource RETURNCODE=Yes
cfprocparam type=In cfsqltype=VariableType
dbvarname=Input Param Name from SP value=#ColdFusion Var Name# null=No
cfprocparam type
Hey all,
I'm working in CF 5.0 (damn federal agencies are so slow to update).
The QA standards that were set require that CFSTOREDPROC be used for all
stored procedures.
As opposed to wrapping an execute statement in a CFQUERY.
Anyways is their a way to see exactly what CFSTOREDPROC
Anyways is their a way to see exactly what CFSTOREDPROC is sending?
You can usually trace or profile SQL connections at the database.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers
Hi, i am getting a problem when i try to use stored procedures that are stored
in the mysql database.
the error that is coming up is java.lang.NullPointerException
it is coming up with this error in the following cfstoredproc code:
cfstoredproc procedure=getStudentDetails datasource
Shouldnt the storedproc be...
WHERE studentEmail = @email
-Original Message-
From: Richard White [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 19, 2006 9:36 AM
To: CF-Talk
Subject: cfstoredproc
Hi, i am getting a problem when i try to use stored procedures that are
stored
getting a problem when i try to use stored
procedures that are stored in the mysql database.
the error that is coming up is java.lang.NullPointerException
it is coming up with this error in the following cfstoredproc code:
cfstoredproc procedure=getStudentDetails datasource=mydataSource
thanks for the replies, i have tested it in mysql and it executes fine and
displays the results.
if the stored procedure expects a varchar do you have any suggesstions on what
i may try
i am just wondering also why it is saying null pointer, it sounds like its not
detecting the stored
i have also checked the privileges in mysql, i have no idea why this is
happening. Can anyone else using mysql and the stored procedure tag see a
problem with the code or provide advice on what i need to check.
Id really appreciate some help, ive been on this all day with no luck :(
thanks
i have tried a very simple example with getStudentDetails just being select
studentFirstName from students, this stored procedure took no parameters. and
it is still coming out with the error null pointer
it looks like for some reason the cfstoredproc is not finding the stored
procedure
The quick difference between the Cfquery and cfStoreproc is you can deal
with multiple recordset returned from the stored procedure which was not
possible using cfquery .With CfstoredProc you can deal with the return
values from the stored procedures.
The Only drawback I see using
PROTECTED]
Sent: Friday, October 13, 2006 7:45 AM
To: CF-Talk
Subject: Re: CFStoredProc vs CFQUERY
The quick difference between the Cfquery and cfStoreproc is you can deal
with multiple recordset returned from the stored procedure which was not
possible using cfquery .With CfstoredProc you can deal
):
**
cffile action=Read
file=C:\testing\Test.xml
variable=variables.myXMLInput
cfstoredproc procedure=TESTCLOB datasource=MYDNS
cfprocparam cfsqltype=CF_SQL_CLOB type=In
value=#variables.myXMLInput#
cfprocparam cfsqltype=CF_SQL_CLOB type=Out
variable=variables.myXMLOutput
):
**
cffile action=Read
file=C:\testing\Test.xml
variable=variables.myXMLInput
cfstoredproc procedure=TESTCLOB datasource=MYDNS
cfprocparam cfsqltype=CF_SQL_CLOB type=In
value=#variables.myXMLInput#
cfprocparam cfsqltype=CF_SQL_CLOB type=Out
variable
, January 19, 2006 10:45 AM
To: CF-Talk
Subject: RE: cfstoredproc and cache
You can put the results into the session/application scope and manage it
yourself or call the stored procedure using cfquery tags and make use of
the cache attributes of the cfquery tag.
Mike
From: Peterson, Andrew S. [mailto
Is there a way to cache the results of a stored procedure in or around
the cfstoredproc tag?
Sincerely,
Andrew
~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:229980
Archives: http://www.houseoffusion.com
I'm sure there many ways to do this. If there is a query result that I want to
use over and over again, I will put it into a memory variable. Usually
application or session.
Bob
~|
Message:
Not on MX 6.1 natively I do not think - but you could take each resultant
query and put it into the session scope.
-Original Message-
From: Peterson, Andrew S. [mailto:[EMAIL PROTECTED]
Sent: 19 January 2006 16:26
To: CF-Talk
Subject: cfstoredproc and cache
Is there a way to cache
procedure in
or around the cfstoredproc tag?
~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:229988
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm
is a refcursor.
I am using CF MX 7
I am getting this error -
PLS-00306: wrong number or types of arguments in
call
to 'GET_METADATA' ORA-06550: line 1, column 7
This is my CF Code for calling the Stored Procedure
:-
===
CFSTOREDPROC procedure = METADATA.get_Metadata
dataSource
On 9/23/05, Quinn Ng [EMAIL PROTECTED] wrote:
Please read my problem below - which I had posted a
couple of days ago.
I am basically trying to get a REF CURSOR as an output
from the stored procedure in my CF code.
I continue to face this problem i.e get error
PLS-00306: wrong number or
Dave,
Curious if you trie granting SYS.ALL_ARGUMENTS to the schema owner?
-Adam
On 9/23/05, Dave Carabetta [EMAIL PROTECTED] wrote:
On 9/23/05, Quinn Ng [EMAIL PROTECTED] wrote:
Please read my problem below - which I had posted a
couple of days ago.
I am basically trying to get a REF
On 9/23/05, Adrocknaphobia [EMAIL PROTECTED] wrote:
Dave,
Curious if you trie granting SYS.ALL_ARGUMENTS to the schema owner?
What does that grant do exactly? I'll have my DBA temporarily grant it
to see if it makes a difference, but I don't know what the above
allows?
Regards,
Dave.
Thanks for replying Dave.
It seems to be a very big bug - of not being able to use oracle ref cursors
with coldfusion ! Big for me beacuse I cannot imagine how data driven
applications can then be developed on coldfusion
Could you tell me how do I replace the version 3.3/3.4 DataDirect driver -
On 9/23/05, kugh ng [EMAIL PROTECTED] wrote:
Thanks for replying Dave.
It seems to be a very big bug - of not being able to use oracle ref cursors
with coldfusion ! Big for me beacuse I cannot imagine how data driven
applications can then be developed on coldfusion
Well it's hard to hold
What does that grant do exactly?
On a traditonal locked-down database, a number of the more common
roles are revoked from the schema owner. One of these roles permits
select on SYS.ALL_ARGUMENTS. If the schema owner does not have this
grant, it cannot read the spec of a procedure properly. Which
Could you tell me how do I replace the version 3.3/3.4
DataDirect driver - with the 3.5 version that works?
I mean is there some path/procedure to follow to be able to
replace this properly and ask ColdFusion to use the new
version instead?
Technically, it's easy to use any external JDBC
On 9/23/05, Adrocknaphobia [EMAIL PROTECTED] wrote:
What does that grant do exactly?
On a traditonal locked-down database, a number of the more common
roles are revoked from the schema owner. One of these roles permits
select on SYS.ALL_ARGUMENTS. If the schema owner does not have this
On 9/23/05, kugh ng [EMAIL PROTECTED] wrote:
Thanks for replying Dave.
It seems to be a very big bug - of not being able to use oracle ref cursors
with coldfusion ! Big for me beacuse I cannot imagine how data driven
applications can then be developed on coldfusion
Could you tell me how do
I am using the default driver (i.e data direct driver) for Oracle 9i - which is
not a new version
I get problems with REF cursor with it too.
Hence dont know how would the upgrade to recent Oracle driver help anyways.
?
On 9/23/05, kugh ng [EMAIL PROTECTED] wrote:
Thanks for replying Dave.
Kugh,
You have a different issue then. CF works fine with everything prior
to Oracle 10g r2.
-Adam
On 9/23/05, kugh ng [EMAIL PROTECTED] wrote:
I am using the default driver (i.e data direct driver) for Oracle 9i - which
is not a new version
I get problems with REF cursor with it too.
Could you tell me how do I replace the version 3.3/3.4
DataDirect driver - with the 3.5 version that works?
I mean is there some path/procedure to follow to be able to
replace this properly and ask ColdFusion to use the new
version instead?
Technically, it's easy to use any external JDBC
:-
= CFSTOREDPROC procedure = METADATA.get_Metadata
dataSource = mydatasource
returnCode = No
CFPROCPARAM type = IN
CFSQLType = CF_SQL_NUMERIC
value = pageid
CFPROCPARAM type = IN
CFSQLType = CF_SQL_VARCHAR
value = myFormName
CFPROCPARAM type = IN
CFSQLType = CF_SQL_VARCHAR
value = myfieldName
To recap again I use the following CF code :-
= CFSTOREDPROC procedure = METADATA.get_Metadata
dataSource = mydatasource
returnCode = No
CFPROCPARAM type = IN
CFSQLType = CF_SQL_NUMERIC
value = pageid
CFPROCPARAM type = IN
CFSQLType = CF_SQL_VARCHAR
value = myFormName
CFPROCPARAM
)
CFSET x=2
CFSTOREDPROC procedure = METADATA.get_Metadata
dataSource = #Request.Site.DataSource#
returnCode = No
debug=Yes
CFPROCPARAM type = IN
CFSQLType = CF_SQL_INTEGER
value=#x
. After looking at your
code I added these - but didnt help
This is how my CF code is (with the actual input values)
CFSET x=2
CFSTOREDPROC procedure = METADATA.get_Metadata
dataSource = #Request.Site.DataSource#
returnCode = No
debug=Yes
YEP I did that but it didnt help
I am going crazy !!
Did you?
GRANT SELECT ON SYS.ALL_ARGUMENTS TO METADATA
-Adam
On 9/23/05, kugh ng [EMAIL PROTECTED] wrote:
~|
Logware (www.logware.us): a new and convenient web-based time
Also it works perfectly from SQL prompt - which goes to say that this
SYS.ALL_ARGUMENTS would not be a problem.
But anyways I have done this - which has not helped
Did you?
GRANT SELECT ON SYS.ALL_ARGUMENTS TO METADATA
-Adam
On 9/23/05, kugh ng [EMAIL PROTECTED] wrote:
On 9/23/05, kugh ng [EMAIL PROTECTED] wrote:
Also it works perfectly from SQL prompt - which goes to say that this
SYS.ALL_ARGUMENTS would not be a problem.
But anyways I have done this - which has not helped
Have you done a dump of the actual values being passed in? Perhaps
putting a val()
Does the value=#x# need to be quoted?
value=#x#
Just a random thought I don't know the answer to.
On 9/23/05, Dave Carabetta [EMAIL PROTECTED] wrote:
On 9/23/05, kugh ng [EMAIL PROTECTED] wrote:
Also it works perfectly from SQL prompt - which goes to say that this
SYS.ALL_ARGUMENTS would
On 9/23/05, Jerry Johnson [EMAIL PROTECTED] wrote:
Does the value=#x# need to be quoted?
value=#x#
Just a random thought I don't know the answer to.
Nah, CF doesn't care about that. Just like you could do
cfloop from=#start# to=#end#
/cfloop
I personally don't like that approach and
PLSQL error
I didnt know that CFPROCPARAMS need a closing / tag. After looking at your
code I added these - but didnt help
This is how my CF code is (with the actual input values)
CFSET x=2
CFSTOREDPROC procedure = METADATA.get_Metadata
dataSource = #Request.Site.DataSource#
returnCode
Makes sense. I overlooked that it would be CF parsing that tag, so of
course it doesn't care. Thanks for clearing that up.
~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
the Stored Procedure :-
===
CFSTOREDPROC procedure = METADATA.get_Metadata
dataSource = mydatasource
returnCode = No
CFPROCPARAM type = IN
CFSQLType = CF_SQL_NUMERIC
value = pageid
CFPROCPARAM type
-
PLS-00306: wrong number or types of arguments in call
to 'GET_METADATA' ORA-06550: line 1, column 7
This is my CF Code for calling the Stored Procedure :-
===
CFSTOREDPROC procedure = METADATA.get_Metadata
dataSource = mydatasource
returnCode = No
CFPROCPARAM type = IN
CFSQLType
On Tuesday 20 September 2005 14:24, Quinn Ng wrote:
CFPROCPARAM type = OUT
CFSQLType = CF_SQL_REFCURSOR
variable = METADATA_CURTYPE
CFPROCRESULT name = metadataResult
/CFSTOREDPROC
One or t'other, not both :-)
--
Tom Chiverton
Advanced
CFSQLType = CF_SQL_REFCURSOR
variable = METADATA_CURTYPE
CFPROCRESULT name = metadataResult
/CFSTOREDPROC
One or t'other, not both :-)
--
Tom Chiverton
Advanced ColdFusion Programmer
~|
Discover CFTicket
Hm...IIRC, there's an issue with some versions of CF with some Oracle
Drivers. I think it's been talked about before...try doing a search on
houseoffusion.com http://houseoffusion.com and see what you come up with.
On 9/20/05, kugh ng [EMAIL PROTECTED] wrote:
Thanks Deanna and Tom for
defined cursor type being retuned as an output variable, how
do I specify that in the call to the cfstoredproc ?
For e.g :- I have this type defined in the Package :-
TYPE metadata_recordtype IS RECORD
(
fieldName FORMINPUTCONTROL.FIELDNAME%TYPE,
fieldValue DATA_FIELDVALUE.fieldvalue%TYPE
I assume that you are talking about just a couple of problem
characters.
Once identified you can do a replace and replace the offending
character with the ascii equivalent. Once you've run the replace they
should go in the database ok. Hope that helps.
j
Yep thanks that worked. It's a workaround though because I replaced the
characters with the #acii equivalent this made the total amount of characters
greater and I had to resize the varchar fields in the database. Also I get
problems now with sorting and had to update also the input search
When I use cfstoredproc to insert a nvarchar string and use
cf_sql_longvarchar or cf_sql_varchar as cfprocparam i receive a '?' for
every nonlatin character into the SQL stored procedure. Can someone
help me how i can recieve special characters like polonian characters
into a stored procedure
I assume that you are talking about just a couple of problem characters.
Once identified you can do a replace and replace the offending character with
the ascii equivalent. Once you've run the replace they should go in the
database ok. Hope that helps.
j
Ron Jonk wrote:
When I use cfstoredproc to insert a nvarchar string and use
cf_sql_longvarchar or cf_sql_varchar as cfprocparam i receive a '?' for
every nonlatin character into the SQL stored procedure. Can someone
help me how i can recieve special characters like polonian characters
I'm cleaning up some old code, can anyone say why this returns records:
cfquery datasource=#SESSION.Datasource# name=Cases
execute SearchCases '#Search#', '#ReferralType#', '#CaseType#', '#NumberType#'
/cfquery
And this doesn't:
cfstoredproc procedure=SearchCases datasource=#session.datasource
And this doesn't:
cfstoredproc procedure=SearchCases datasource=#session.datasource#
cfprocresult name=Cases resultset=1
cfprocparam type=In cfsqltype=CF_SQL_CHAR value=#Search#
cfprocparam type=In cfsqltype=CF_SQL_CHAR value=#ReferralType#
cfprocparam type=In cfsqltype=CF_SQL_INTEGER value=#CaseType
this returns records:
cfquery datasource=#SESSION.Datasource# name=Cases
execute SearchCases '#Search#', '#ReferralType#', '#CaseType#',
'#NumberType#'
/cfquery
And this doesn't:
cfstoredproc procedure=SearchCases datasource=#session.datasource#
cfprocresult name=Cases resultset=1
From: Greg Luce [mailto:[EMAIL PROTECTED]
CREATE PROCEDURE [SearchCases]
@Search varchar( 20),
@ReferralType varchar(5),
@CaseType int,
@NumberType varchar(4)
You cfprocparam tags use CHAR for the SQL Type. Switch it to VARCHAR.. maybe
your values are getting truncated.
Thanks Mike! That did it.
On Thu, 3 Feb 2005 16:23:18 -0500, Michael T. Tangorre
[EMAIL PROTECTED] wrote:
From: Greg Luce [mailto:[EMAIL PROTECTED]
CREATE PROCEDURE [SearchCases]
@Search varchar( 20),
@ReferralType varchar(5),
@CaseType int,
@NumberType varchar(4)
You
From: Greg Luce [mailto:[EMAIL PROTECTED]
Thanks Mike! That did it.
np.
~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or
I am working on migrating our CF5 apps to CFMX and ran into a problem with one
of our stored procedure calls. It is a CFSTOREDPROC that has 3 input
parameters, 1 output parameter and returns a result set. The only error I get
is java.lang.NullPointerException - in : line -1. The exception
On Tue, 01 Feb 2005 12:36:26 -0400, Alisa Thomson [EMAIL PROTECTED] wrote:
I am working on migrating our CF5 apps to CFMX and ran into a problem with
one of our stored procedure calls. It is a CFSTOREDPROC that has 3 input
parameters, 1 output parameter and returns a result set. The only
Right. I just left them in there so we could tell what variable it is mapping
to since the dbvarname is just ignored at this point. I believe it has
something to do with the fact that there is both an output parameter AND a
resultset. All of our other proc calls work fine.
Thanks.
Ci-Ci
On
Alisa,
Is this the only proc not working? I'd be curious to see if other
stored procedures that dont use CF_SQL_TIMESTAMP would execute.
-Adam
On Tue, 01 Feb 2005 13:53:49 -0400, Alisa Thomson [EMAIL PROTECTED] wrote:
Right. I just left them in there so we could tell what variable it is
was using a
user-defined datatype that was not defined in the default database. It seems
that CFMX is using the metadata from the default database in the driver instead
of the database that the cfstoredproc tag references.
Alisa,
Is this the only proc not working? I'd be curious to see if other
We're using CFMX 6.1 w/ updater and there is a cfstoredproc that has
been working fine previous to migrating to MX. This is an Oracle
stored
procedure and we were using the Oracle driver provided with CFMX. Now
when we try to execute this procedure, it is taking at least 14
seconds
to reply. We
Hey Nathan,
This was with cfstoredproc and cfprocparam, with this I have seen no lapse
or delay in SP's. My point was merely that it was wrong of a DBA to assume
and state that the use of SP's was wrong.
That's not the point at all. We all know stored procedures are faster
than plain vanilla
Here is a useful link on Stored Procedures:
http://www.sql-server-performance.com/stored_procedures.asp
~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message:
Hmmm, I know I still pass in @varname in dbvarname as it doesn't fail so at
least it is backward supported!!
-Original Message-
From: C. Hatton Humphrey [mailto:[EMAIL PROTECTED]
Sent: 21 December 2004 19:41
To: CF-Talk
Subject: Re: CFStoredProc bug?
The dbvarname attribute
constantly.
HTH
Neil
-Original Message-
From: Nathan Strutz [mailto:[EMAIL PROTECTED]
Sent: 21 December 2004 20:32
To: CF-Talk
Subject: Re: CFStoredProc bug?
Michael Dinowitz wrote:
Thank you for pointing that out. It looks like I missed that. So
basically,
the order
I've seen some strange behavior with cfstoredproc, and our DBA has
requested that we don't use it. If you trace your database (tested on
sql2k), you will see cfstoredproc creates and compiles a procedure,
Hmm, I find this hard to swallow and I would question your 'DBA' without
seeing the facts
Robertson-Ravo, Neil (RX) wrote:
Based on the comments - I ran a quick test with an SP which basically ran
the following (where iLanguageID and iEventID were @ variables passed in via
dbvarname):
How did you call the procedure, with cfquery or with cfstoredproc? Did
you use cfqueryparam's
calling this one table):
cfstoredproc datasource=#dsn# procedure=up_get_status
cfprocparam cfsqltype=CF_SQL_VARCHAR variable=cSearch value=#f_search#
/cfstoredproc
When I run this I get a SQL error Error converting data type varchar to int.
This is the same result I get when I try to run
You should change your cSearch to @cSearch
-Original Message-
From: C. Hatton Humphrey [mailto:[EMAIL PROTECTED]
Sent: 21 December 2004 17:02
To: CF-Talk
Subject: CFStoredProc bug?
Okay, I just ran across this and I'm not sure if it's something that
has been looked into before
It's normal and not buggy.
CFStoredproc passes values in the order that they are declared. I
believe the variable attribute to the cfprocparam tag tells CF what
the variable name will be (for OUT and IN/OUT variables)
Stored procedures can be called by name
sp_proc @two='xyz', @one='abc
]
Sent: 21 December 2004 17:02
To: CF-Talk
Subject: CFStoredProc bug?
Okay, I just ran across this and I'm not sure if it's something that
has been looked into before but it does give me reason for concern.
I have a stored procedure, up_get_status, that takes two parameters,
@nStatus_id
Oh yeahsorry my bad = you only need it with @ if you are using
@dbvarname.
Thanks MD, was a little out on that one ;-)
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 21 December 2004 17:23
To: CF-Talk
Subject: RE: CFStoredProc bug?
If you mean
As an aside for info purposes, the variable attribute in cfprocparam is for
info sent from the SP to CF. To bind a passed value to a SP variable, you
use dbvarname.
It's normal and not buggy.
CFStoredproc passes values in the order that they are declared. I
believe the variable attribute
bad = you only need it with @ if you are using
@dbvarname.
Thanks MD, was a little out on that one ;-)
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 21 December 2004 17:23
To: CF-Talk
Subject: RE: CFStoredProc bug?
If you mean in the CFPROCPARAM
Eh? what so you remove it altogether in for dbvarname?
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 21 December 2004 17:36
To: CF-Talk
Subject: RE: CFStoredProc bug?
It's one that throws everyone due to a bad example somewhere in the past.
Actually
in for dbvarname?
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 21 December 2004 17:36
To: CF-Talk
Subject: RE: CFStoredProc bug?
It's one that throws everyone due to a bad example somewhere in the past.
Actually, the @ should never be used in either variable
From: Michael Dinowitz [mailto:[EMAIL PROTECTED]
Yep.
cfprocparam type=In cfsqltype=CF_SQL_INTEGER dbvarname=Order_ID
value=#Arguments.Order_ID# null=no
This binds to the Order_ID inside the SP which is defined
(inside) as @Order_ID. The reason for the @ inside is to show
it's a SQL
Thank you for pointing that out. It looks like I missed that. So basically,
the order of cfprocparams being passed is all that matters and no parameter
can be missed when writing cfprocparam tags.
Is there any performance (i.e. binding) savings to using the CFSTOREDPROC
tag vs. a CFQUERY
) savings to using the
CFSTOREDPROC tag vs. a CFQUERY with CFQUERYPARAMs? Is the
CFSTOREDPROC call as efficient?
Better? What's the advantage? Anyone from MM want to comment?
Thanks
One major advantage of using the cfquery tag to call the stored
procedure is the fact that you can use
But not at the same time as using CFQUERYPARAM. :(
One major advantage of using the cfquery tag to call the stored
procedure is the fact that you can use the cachediwthin and cachedafter
attributes whereas cfstoredproc does not have them.
Michael T. Tangorre
From: Michael Dinowitz [mailto:[EMAIL PROTECTED]
But not at the same time as using CFQUERYPARAM. :(
I thought that if you used cfqueryparam for constants the results were
cache-able.
~|
Special thanks to the CF Community
101 - 200 of 424 matches
Mail list logo
