I am trying to use cfstoreproc to invoke a stored procedure
from a CFC.
I pass in 2 variables into the cfprocparam but am getting
this error:
I dumped the 2 invokearguments to make sure they are working
and they dump
fine.
Any ideas why this isn't working? (my first time trying to
use stored
procedures)
Here is the invoke:
<cfobject name="IQ" component="cfcs.IQ.IQweb">
<cfinvoke component="#IQ#" method="NewPassword">
<cfinvokeargument name="username"
value="#GetInfo.ACCOUNT_ID#">
<cfinvokeargument name="password" value="#stpassword#">
</cfinvoke>
Here is the CFC:
<cfcomponent>
<cffunction name="NewPassword" access="public"
returntype="void">
<cfargument name="username" type="string"
required="yes">
<cfargument name="password" type="string"
required="yes">
<cfstoredproc procedure="sp_web_changepassword"
datasource="sql_abt">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR"
variable="@Account_ID"
value="#arguments.username#" null="No">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR"
variable="@UnencryptedNewPassword"
value="#arguments.password#" null="No">
<cfprocparam type="Out" cfsqltype="CF_SQL_VARCHAR"
variable="new_result"
dbvarname>
</cffunction>
</cfcomponent>
Here is the Stored Procedure:
create procedure sp_web_changepassword
@account_ID varchar(8), @UnencryptedNewPassword Varchar(255),
@return_code
int OUTPUT
as
declare @EncryptedPassword varchar(255)
-- initialize variables Return code to Account_ID error code
select
@Return_code = 6, @EncryptedPassword = ' '
if exists (select * from ABT_ACCOUNTS where Account_ID =
@Account_ID)
begin
-- first encrypt entered password
exec sp_encrypt_text @UnencryptedNewPassword,
@EncryptedPassword OUTPUT
-- update ABT_ACCOUNTS with new password
UPDATE ABT_ACCOUNTS
Set Password = @EncryptedPassword
where Account_ID =
@account_ID
-- return SUCCESS code
select
@Return_code = 0
end
GO