Status/Resolution/Reason: Closed/Withdrawn/Duplicate
Reporter/Name(from Bugbase): burak toker / burak toker (burak toker)
Created: 04/21/2015
Components: Database
Versions: 11.0
Failure Type: Data Loss
Found In Build/Fixed In Build: CF11_Final /
Priority/Frequency: Critical / All users will encounter
Locale/System: ALL / Windows 7 SP1 64-bit
Vote Count: 0
Duplicate ID: CF-3973457
Problem Description: While using cfstoredproc with out parameter, the variable literally disappears.
Steps to Reproduce: On SQL Server 2008 :
1. create table:
CREATE TABLE [dbo].[UserTest](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[EmailAddress] [varchar](250) NULL,
CONSTRAINT [PK_dbo.UserTest] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
2. Create stored procedure :
CREATE PROCEDURE [dbo].[PROC_ADD_USERTEST]
@EmailAddr VARCHAR(250)= NULL,
@NewUserID INT OUTPUT
AS
BEGIN
INSERT INTO dbo.UserTest ( emailAddress )
VALUES ( @EmailAddr );
set @NewUserID = ident_current('dbo.UserTest');
END
GO
3. Run Stored proc and see the results:
DECLARE @return_value int,
@NewUserID int
EXEC @return_value = [dbo].[PROC_ADD_USERTEST]
@EmailAddr = N'z@z.com',
@NewUserID = @NewUserID OUTPUT
SELECT @NewUserID as NewUserID
SELECT 'Return Value' = @return_value
GO
select * from dbo.UserTest
4. create a cfm and put
<cftry>
<cfscript>
setting showdebugoutput=true;
local = structNew();
callResult = structNew();
callResult.qryResult = {};
local.emailAddr = 'b@b.com';
</cfscript>
<cfstoredproc procedure="proc_add_usertest" datasource="#application.stldsn#" >
<cfprocparam type="in" cfsqltype="cf_sql_varchar" dbvarname="@EmailAddr" value="#trim(local.emailAddr)#" null="#not len(local.emailAddr)#">
<cfprocparam type="out" cfsqltype="cf_sql_integer" dbvarname="@NewUserID" variable="newUID">
<cfprocresult name="callResult.qryResult">
</cfstoredproc>
<cfdump var="#callResult#" label="callResult">
<cfoutput>newUID = #newUID#</cfoutput>
<cfcatch>
<cfdump var="#cfcatch#">
<cfdump var="#variables#" label="variables">
</cfcatch>
</cftry>
Observe the error message: Message Variable NEWUID is undefined.
Actual Result:
Variable NEWUID is undefined.
Expected Result:
NewUserID = 1 or some other integer.
Any Workarounds: undo CF11 Update 5.
----------------------------- Additional Watson Details -----------------------------
Watson Bug ID: 3973462
External Customer Info:
External Company:
External Customer Name: burak
External Customer Email:
External Test Config: My Hardware and Environment details:
windows 7 pro, CF11. Rest not relevant.Attachments:
Comments: