tracker issue : CF-4113978

select a category, or use search below
(searches all categories and all time range)

storedproc().execute().getPrefix() - only returning ExecutionTime

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/AsDesigned

Reporter/Name(from Bugbase): Matthew Parks / Matthew Parks (Matthew Parks)

Created: 02/04/2016

Components: Database

Versions: 11.0

Failure Type: Unspecified

Found In Build/Fixed In Build: CF11_Final /

Priority/Frequency: Trivial / All users will encounter

Locale/System: English / Win 2012 Server x64

Vote Count: 0

Problem Description: calling Stored Procedure was expecting StatusCode to be returned to check id proc executed correctly but it is not.

Steps to Reproduce:-- Table creation

create table Test
( id int, 
name varchar2(20)

-- Data insertion
insert into Test values(1, 'SomeText', 'SomeText', 23432.45, 'SomeText', 'SomeText', 'SomeText', 'SomeText', 34.34, 'SomeText', 'SomeText', 'SomeText', 'SomeText');
insert into Test values(2, 'SomeText', 'SomeText', 324.23, 'SomeText', 'SomeText', 'SomeText', 'SomeText', 56.5, 'SomeText', 'SomeText', 'SomeText', 'SomeText');
insert into Test values(3, 'SomeText', 'SomeText', 23232.2, 'SomeText', 'SomeText', 'SomeText', 'SomeText', 78.6, 'SomeText', 'SomeText', 'SomeText', 'SomeText');
insert into Test values(4, 'SomeText', 'SomeText', 7665.5, 'SomeText', 'SomeText', 'SomeText', 'SomeText', 789.5, 'SomeText', 'SomeText', 'SomeText', 'SomeText');

--create stored procedure
create or replace procedure spTest (idd in number, name out varchar2) as 
SELECT name FROM Test where id = idd;

--Calling Stored ptocedure
        spService = new storedproc();
        //set attributes using implicit setters
        //add procparams using addParam
        spService.addParam(cfsqltype="CF_SQL_DECIMAL", type="IN", value="2");
        spService.addParam(cfsqltype="CF_SQL_VARCHAR", type="OUT", value="name");

        //execute the stored procedure
        result = spService.execute();
        //getprocOutVariables() returns any OUT or INOUT varibles added using addParams()
        name = result.getProcOutVariables().name;

// only ExecutionTime in dump expectin StatusCode

Actual Result: only ExecutionTime returnedin in result.getPrefix() structure

Expected Result: FROM ADOBE SITE: " Note: If you specify a result attribute for a stored procedure, then calling getPrefix() returns, executionTime,statusCode,cached . If you do not specify a result attribute, getPrefix() returns only executionTime and statusCode."

Any Workarounds:

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	4113978

External Customer Info:
External Company:  
External Customer Name: Matthew Parks
External Customer Email:  
External Test Config: My Hardware and Environment details:Window 2012 R2 running CF11 update 7



I would expect executionTime and statusCode to be returned.
Comment by External U.
4824 | February 04, 2016 11:51:19 AM GMT
I have to add. This happens with or without debug turned on the server.
Comment by External U.
4825 | February 04, 2016 11:51:54 AM GMT
Matthew, Are you getting a statuscode/returncode when you execute this stored procedure directly in Oracle SQLPlus applcation?
Comment by Nimit S.
4826 | March 14, 2016 01:19:08 AM GMT
Matthew, Can you please answer the query mentioned in the previous comment?
Comment by Nimit S.
4827 | April 11, 2016 06:11:40 AM GMT
No. Not status code when executing in Oracle SQL Plus. The executionTime and statusCode are documented in the storedproc().execute().getPrefix() to return both elements and only the executionTime is returning. I would expect to check the statusCode first, to see if statement executed as expected. ExecutionTime can be 0 a lot of the time.
Comment by External U.
4828 | April 11, 2016 10:19:26 AM GMT
Matthew, It populates cfstoredproc.statusCode with status code returned by the stored procedure. If the stored procedure is not returning any status code, then ColdFusion cannot return anything.
Comment by Nimit S.
4829 | July 14, 2016 04:01:29 AM GMT