cfstoredproc cfprocresult problem

[DL]

I have a cfstoredproc with two cfprocresult:
<cfstoredproc
procedure = "sp_package_has_inventory"
dataSource = "#dsn#">
<cfprocresult name = "inventorySnapShot">
<cfprocresult name = "inSufficientInventorySnapShot"
resultset="2">
<cfprocparam
type = "IN"
CFSQLType = "CF_SQL_INTEGER"
Value = "#pakId#"
dbVarName = "@packageid">
<cfprocparam
type = "OUT"
CFSQLType = "CF_SQL_INTEGER"
variable = "hasInventory"
dbVarName = "@has_inventory">
</cfstoredproc>


Railo correctly get's hasInventory, the output param, but it doesn't
get inventorySnapShot, my first cfprocresult

Is this a known bug? Is there a workaround?

Thanks,

Derek

[Leigh]

What database are you using? I ran a quick test with MS SQL and Railo 3.1.2 and it worked fine for me.

alter procedure sp_package_has_inventory
@packageID INT
, @hasInventory INT OUT
as
begin
--- change output var
set @hasInventory = 285
--- generate two resultsets
select @packageID AS Value1
select 99 AS Value2
end
go

[DL]

I am using MS SQL 2008.

The storeproc is like this:

ALTER PROC [dbo].[sp_package_has_inventory]
@package_id INT
, @has_inventory BIT OUT
, @showInsufficientProducts BIT = 1
AS
SET @has_inventory = 1
....
CREATE TABLE #inv_tbl (
package_id INT
...
SELECT * FROM #inv_tbl

IF @showInsufficientProducts = 1
SELECT * FROM #insuffient_inv_tbl


I am using Railo 3.2.0.000

The error message is: variable [inventorysnapshot] doesn't exist

Thanks,

Derek

[Leigh]

Though not the same configuration, I could not reproduce the problem with:

- Railo 3.1.2 / MS SQL 2005 / MS SQL OR JTDS driver
* Assuming @showInsufficientProducts = 1

So as not to overlook the obvious, it return the correct when you run the procedure directly in the database (with the same parameters)? Also, are you using SET NOCOUNT ON inside the procedure?

-Leigh

--- Test Procedure
ALTER PROCEDURE sp_package_has_inventory
@packageID INT
, @hasInventory BIT OUT

, @showInsufficientProducts BIT = 1
AS

BEGIN
SET NOCOUNT ON

SET @hasInventory = 1

--- create and populate table1
CREATE TABLE #insuffient_inv_tbl ( Value1 INT )
INSERT INTO #insuffient_inv_tbl ( Value1 ) VALUES (999)
--- create and populate table2
CREATE TABLE #inv_tbl ( package_id INT, hasInventory BIT )
INSERT INTO #inv_tbl (package_id, hasInventory) VALUES (@packageID, @hasInventory)

--- generate resultset1
SELECT * FROM #inv_tbl
--- generate resultset2

IF @showInsufficientProducts = 1
SELECT * FROM #insuffient_inv_tbl

SET NOCOUNT OFF
END
GO

[Leigh]

> <cfprocresult name="inSufficientInventorySnapShot"
> resultset="2">

I do not know if it makes a difference, but it could not hurt to use an explicit resultset number for both cfprocresult's.

-Leigh

[DL]

OK. This is what I have found out:

I have a storedproc like this:
********************************************************************************************************
ALTER PROC [dbo].[sp_package_inventory_on_hand_2]
@packageID INT
AS

CREATE TABLE #warehouse_location (
warehouse_location_id_int INT
, sequence_int INT
)

CREATE TABLE #inv_tbl (
value1 INT
)
/* this makes railo crash. comment this out to see the crash go away
*/
INSERT INTO #warehouse_location (warehouse_location_id_int,
sequence_int) VALUES (1,1)

SELECT * FROM #inv_tbl

***********************************************************************************************************************************
Another storedproc like this:

ALTER PROC [dbo].[sp_package_has_inventory_2]
@packageID INT
, @has_inventory BIT OUT

, @showInsufficientProducts BIT = 1
AS

SET @has_inventory = 1

CREATE TABLE #inv_tbl (
value1 INT
)

CREATE TABLE #insuffient_inv_tbl (
value2 INT
)

INSERT INTO #inv_tbl
EXECUTE sp_package_inventory_on_hand_2 @packageID


SELECT * FROM #inv_tbl

SELECT * FROM #insuffient_inv_tbl

****************************************************************************************
My cfml is like this:
<cfstoredproc
procedure = "sp_package_has_inventory_2"
dataSource = "#dsn#">
<cfprocresult name = "inventorySnapShot" resultset="1">

<cfprocresult name = "inSufficientInventorySnapShot"
resultset="2">
<cfprocparam
type = "IN"
CFSQLType = "CF_SQL_INTEGER"

Value = "1"
dbVarName = "@packageID">

<cfprocparam
type = "OUT"
CFSQLType = "CF_SQL_INTEGER"
variable = "hasInventory"
dbVarName = "@has_inventory">
</cfstoredproc>

<cfdump var="#inventorySnapShot#"><cfabort>

****************************************************************************************************************
It crashes like this:
Message variable [INVENTORYSNAPSHOT] doesn't exist

**************************************************************************************************************
It works fine in CF9


Comment out "INSERT INTO #warehouse_location
(warehouse_location_id_int, sequence_int) VALUES (1,1) ", it will work
in railo.

[Leigh]

Did you try adding SET NOCOUNT ON as suggested? Extraneous record counts (ie xx rows affected) can often throw off the processing of resultsets in some programs. It is a common problem with stored procedures. SET NOCOUNT suppresses the extra "chatter" which usually is not needed by the calling program anyway. My group uses it on all stored procedures, no matter what environment we are using.

CREATE PROCEDURE ....
... parameters

AS
BEGIN
SET NOCOUNT ON

... rest of sql ....

SET NOCOUNT OFF
END
GO

-Leigh

[DL]

Hi Leigh,

Thank you very much for your help! SET NOCOUNT ON/OFF is right on the
money. Sorry I have been very busy and had no time to verify.

I use SET NOCOUNT ON/OFF when I get the next identity, never used it
on normal stored procedures. I'll do some research to see if there
will be any potential problems using them.

Also, Adobe CF never minds without SET NOCOUNT ON/OFF...

Thanks again!

-- Derek

[Leigh]

Hi Derek,

> I'll do some research to see if there
> will be any potential problems using them.

Glad to hear it resolved. There should not be any issues with it. We use it all the time. It just suppresses extra communication that is normally discarded anyway. So there is a minute performance benefit as well.

> Also, Adobe CF never minds without SET NOCOUNT ON/OFF...

It has been a while, so I do not recall how ACF handles procedures without it. I do remember ACF 8 had similar issues when certain compound statements were used within cfquery (ie INSERT INTO/SCOPE_IDENTITY()) which varied by driver. So consistently using SET NOCOUNT is a good policy. Then you do not have to worry about this kind of issue cropping up due to changes in version, engine or database driver ;-)

-Leigh

[Koofka]

Hi Derek and Lee.

I am running up against an issue that seems similar and related to the
insertion of values into a temp table. Hoping you might lend some
insight.

Basics are a Railo 3.1.2 hosted cfc that has the following query in
one of its functions

<cfquery name="adminGetVisitsByHour" datasource="choa">
SET NOCOUNT ON
Create Table ##tempHour
(
dayHour int
)

Declare @curSeq int
Set @curSeq = 1

While @curSeq <= 24

BEGIN
Insert into ##tempHour
(dayHour)
VALUES
(@curSeq)
Set @curSeq = @curSeq + 1
END

Select DatePart(hour, datemodified) as dayHour,
count(counterid) as visitCount
into ##todaysHours
from tblSession
where datemodified >= convert(varchar(12), getdate(), 101)
group by DatePart(hour, datemodified)
order by DatePart(hour, datemodified)

Select a.dayHour, isNull(b.VisitCount, 0) as visitCount
from ##tempHour A LEFT OUTER JOIN ##todaysHours B on a.dayhour =
b.dayhour

SET NOCOUNT OFF
</cfquery>

Query runs fine in an external DB client (DB visualizer) but returns
an error similar to the one Derek mentioned: variable
[admingetvisitsbyhour] doesn't exist.

If I remove the section where the loop inserts into the #tempHour
table the query returns as expected - though without data due to the
lack of the tempHour table.

Any thoughts you may have would be appreciated.

Best,
Koof

[Leigh]

> I am running up against an issue that seems similar and
> related to the
> insertion of values into a temp table. 

IMO that much sql logic belongs in a stored procedure, not a cfquery. But unless there is more to it (?) .. you could greatly simplify the whole thing by removing the #temp tables. Create a permanent hours table instead of recreating a temporary one every time. Then join it to a derived query that returns today's hours

ie
SELECT  a.dayHour, isNull(b.VisitCount, 0) as visitCount
FROM    PermHoursTable a LEFT OUTER JOIN
       (
       SELECT    DatePart(hour, datemodified) as dayHour, COUNT(counterid) as visitCount
       FROM    tblSession
       WHERE    datemodified >= ... etc...
       GROUP BY DatePart(hour, datemodified)
    ) b on a.dayHour = b.dayHour
ORDER BY a.dayHour

-Leigh

[Koofka]

Thanks Leigh.

As it happens that is the direction I ended up moving forward with - a
permanent table with the hourly values for that query.
Not sure I agree completely on store proc vs query logic boundaries,
but needed a workaround for the issue that seems to be occurring with
Railo.

From what I can tell, a Select ... into #tempTable operates as
expected, but using Create Table #tempTable and later VALUE Inserts
throws the error that Derek was noting in his earlier post.

One somewhat interesting test I did was based on your SET NOCOUNT ON
commentary (normally use at the beginning and end of most CFQUERY
SQL). When I surrounded the VALUE Insert into the #tempTable with
the ON & OFF instead of the whole query the error returned was
"already a #tempHour in the database". Moving the ON & OFF back to
the outer boundaries of the query put it back to the original error.

Not quite sure what may be going on with it, but moved on using the
permanent table workaround - it does seem like the CREATE TABLE
#Temptable and Insert VALUES behaviors aren't operating as expected.

Best & Thanks for the response,
-Koof

[Leigh]

> When I surrounded the VALUE Insert
> into the #tempTable with
> the ON & OFF instead of the whole query the error
> returned was
> "already a #tempHour in the database". 

That is odd. When I ran your original sql earlier, both ACF and Railo complained about the pre-existing temp table the second time the code was executed. So I added the missing drop table statements, just before the set nocount off.

However I did notice
a) The original sql (plus the drop table statements) worked fine as a stored procedure, called with cfstoreproc
b) The query result *did* exist when I used the jtds driver instead of the ms vendor driver.

-Leigh

[Koofka]

Yeah - I had neglected to leave the Drop Table statements in that one
I posted, but they were in the original as the final lines before the
ending SET NOCOUNT OFF. Removed them when I started debugging the odd
error.

Was wondering if it was possibly driver related - I am on the MS
vendor driver currently. Will attempt to switch that over when I get
a chance to circle back a bit. Supposing its providence as the error
sent me down the permanent table route as you suggested.

Thanks for the tests & appreciate the assist.

Best,
-Koof

[DL]

I guess I am late in this discussion. If you open SQL Studio and run
the following:

use master
go
CREATE TABLE #t(x INT PRIMARY KEY);
INSERT INTO #t VALUES (2);
SELECT x FROM #t;

If you run the second time, you'll get the same error message. The
temp table gets dropped when you close the SQL editor.

If this code is in a stored procedure, the table is dropped when the
stored proc is done. According to MS Doc: A local temporary table
created in a stored procedure is dropped automatically when the stored
procedure is finished., All other local temporary tables are dropped
automatically at the end of the current session.

I haven't had the need to use Temp tables with cfquery's. I would use
array of structures instead. I use temp tables in stored procs only
because there are no other tools like array of structures.

The other question is: does the code work in Adobe CF? I am
experiencing problems here and there with Railo, with working code in
Adobe CF.

[Leigh]

> All other local temporary tables are dropped
> automatically at the end of the current session.

I think Koofka mentioned he just forgot to include the "drop table..." statements in his original post. But you are right. Temp tables should be dropped at the end of the query. So they do not cause problems if an open db connection is reused.

> does the code work in Adobe CF? 

It works with CF9/Adobe Driver and Railo/jTDS driver. It did *not* work with either engine when using the MS SQL Vendor Driver (v1.0?). That suggests a possible driver problem.

-Leigh