Stored Procedure Dataset bug?

Classic Servoy
1

I have a stored procedure that was working, but was edited to add to some fields and now returns no dataset to Servoy 2.1.2. However, it returns a dataset in Query Analyzer in all versions. Help? The second version (below) apparently includes “field variables”, and the third (below) avoids field variables to see if that’s the problem but instead uses “table variables”. Again, all three produce a dataset in Query Analyzer, but only the first produces one in Servoy (no error from debugger for any of them):

CREATE PROCEDURE dbo.ud_BudgetContractSelect
      @proj_id INTEGER,
      @tier2_id INTEGER, 
      @tier3_id INTEGER,
      @sortorder VARCHAR(30)
   AS

-- Create Procedure ud_BudgetContractSelect
-- retrieve the distinct values of contract_id for base contracts on budget rows
-- ( base contract rows have the parent id = to the contract id )
-- UNION'ed with rows attached to amendments and the contract is not on any other budget row
-- for this proj_id, tier2_id, tier3_id
-- 
-- PARAMETERS IN:
-- proj_id		PK of TblProj01Project
-- tier2_id		PK of TblProj07Tier2
-- tier3_id		PK of TblProj09Tier3
-- sortorder   contract_nbr and start_date are the only 2 values
-- by contract_nbr is the default
-- PARAMETERS OUT:
-- none
-- FUNCTION CALLS
-- udfBTLContractSelect
-- Last Change Date and Description
-- Jan    2004 written by Vicki Cowles-CS3W Associates Inc.
-- Jan 15,2004 add ETC column 
-- Apr 15,2004 add milestone col, remove ETC, change EAC to ETC/EAC
-- Apr 22,2004 remove budget line data (minstart, max end, total dollars) 
-- and replace with contract info (contract total dollars, ntpdate, actuals)
-- Jun 17,2004 split the etc/eac columns into 2. eac is once agan the calculated column
-- oct 13,2004 add 4th parm to call of udfBTLContractSelect. target_id = 0
-- jan 05,2005 change selects to be just the fields for the servoy app

-- --	test lines
-- 	DECLARE @proj_id int, @tier2_id int, @tier3_id INTEGER, 
-- 		@sortorder VARCHAR(30)
-- 	SET @proj_id = 134  -- 282
-- 	SET @tier2_id = 22  -- 19
-- 	SET @tier3_id = 0  -- 0
-- 	SET @sortorder = 'start_date'
-- --	end test lines 

   DECLARE @target_id INT
   SET @target_id = 0  -- this tells the function to get current budget rows, not saved target rows


   IF @sortorder = 'start_date'
      SELECT
         contract_id,
         contract_nbr,
         contract_title,
         ntp_date,
         curr_completion_date,
         actuals_LTD,
         orig_amt,
         '1/1/2006' AS min_start,
         '12/31/2010' AS max_end,
         25000 AS ETC
      FROM dbo.udfBTLContractSelect ( @proj_id, @tier2_id, @tier3_id, @target_id )
      ORDER BY ntp_date, contract_nbr
   ELSE
      SELECT
         contract_id,
         contract_nbr,
         contract_title,
         ntp_date,
         curr_completion_date,
         actuals_LTD,
         orig_amt,
         '1/1/2006' AS min_start,
         '12/31/2010' AS max_end,
         25000 AS ETC
      FROM dbo.udfBTLContractSelect ( @proj_id, @tier2_id, @tier3_id, @target_id )
      ORDER BY status DESC, contract_nbr,  ntp_date


GO

The second version, which works in Query Analyzer but gives no error and no dataset to Servoy:

CREATE PROCEDURE dbo.ud_BudgetContractSelect
      @proj_id INTEGER,
      @tier2_id INTEGER, 
      @tier3_id INTEGER,
      @sortorder VARCHAR(30)
   AS

-- Create Procedure ud_BudgetContractSelect
-- retrieve the distinct values of contract_id for base contracts on budget rows
-- ( base contract rows have the parent id = to the contract id )
-- UNION'ed with rows attached to amendments and the contract is not on any other budget row
-- for this proj_id, tier2_id, tier3_id
-- 
-- PARAMETERS IN:
-- proj_id		PK of TblProj01Project
-- tier2_id		PK of TblProj07Tier2
-- tier3_id		PK of TblProj09Tier3
-- sortorder   contract_nbr and start_date are the only 2 values
-- by contract_nbr is the default
-- PARAMETERS OUT:
-- none
-- FUNCTION CALLS
-- udfBTLContractSelect
-- Last Change Date and Description
-- Jan    2004 written by Vicki Cowles-CS3W Associates Inc.
-- Jan 15,2004 add ETC column 
-- Apr 15,2004 add milestone col, remove ETC, change EAC to ETC/EAC
-- Apr 22,2004 remove budget line data (minstart, max end, total dollars) 
-- and replace with contract info (contract total dollars, ntpdate, actuals)
-- Jun 17,2004 split the etc/eac columns into 2. eac is once agan the calculated column
-- oct 13,2004 add 4th parm to call of udfBTLContractSelect. target_id = 0
-- jan 05,2005 change selects to be just the fields for the servoy app
-- jan 25,2005 add the minstart, maxend, and sum(etc) for the budget rows in this contract
--             also return all the 'other' rows in this proc, grouped by the field calcothergroup

-- --	test lines
-- 	DECLARE @proj_id int, @tier2_id int, @tier3_id INTEGER, 
-- 		@sortorder VARCHAR(30)
-- 	SET @proj_id = 134  -- 282
-- 	SET @tier2_id = 1  -- 19
-- 	SET @tier3_id = 0  -- 0
-- 	SET @sortorder = ''
-- --	end test lines 

   DECLARE @target_id INT, @ETC MONEY, @min_start AS DATETIME, @max_end AS DATETIME, 
   @i INT, @cnt INT, @contract_id INT

   DECLARE @tmpContract TABLE (
   tcid INT IDENTITY (1,1),
   contract_id INT,
   contract_nbr VARCHAR(20),
   contract_title VARCHAR(150),
   ntp_date DATETIME,
   curr_completion_date DATETIME,
   actuals_LTD MONEY default 0,
   orig_amt MONEY default 0,
   status INT,
   min_start DATETIME,
   max_end DATETIME,
   ETC MONEY default 0,
   calcothergroup INT default 0    )

   SET @target_id = 0  -- this tells the function to get current budget rows, not saved target rows

   INSERT INTO @tmpContract (
         contract_id,
         contract_nbr,
         contract_title,
         ntp_date,
         curr_completion_date,
         actuals_LTD,
         orig_amt,
         status     )
      SELECT
         contract_id,
         contract_nbr,
         contract_title,
         ntp_date,
         curr_completion_date,
         actuals_LTD,
         orig_amt,
         status  
      FROM dbo.udfBTLContractSelect ( @proj_id, @tier2_id, @tier3_id, @target_id )

   SELECT @cnt = count(*) FROM @tmpContract
   SET @i = 1
   WHILE @i <= @cnt
   BEGIN
      SELECT @contract_id = contract_id FROM @tmpContract WHERE tcid = @i

      SELECT   @min_start = MIN(start_date),
               @max_end   = MAX(finish_date),
               @ETC       = SUM(current_forecast)  
      FROM ViewBudgetTierLine  
      WHERE  proj_id        = @proj_id  
            AND tier2_id    = @tier2_id 
            AND tier3_id    = @tier3_id 
            AND contract_id = @contract_id

      UPDATE @tmpContract
      SET   min_start = @min_start,
            max_end = @max_end,
            ETC = @ETC
      WHERE tcid = @i

      SET @i = @i + 1
   END

-- now get the 'other' rows to show as 'contracts'
   INSERT INTO @tmpContract (
         contract_id,
         contract_nbr,
         contract_title,
         min_start,
         max_end,
         ETC,
         calcothergroup              )
      SELECT
         contract_id,
         'TEMP:' + contract_nbr,
         contract_desc,
         MIN(Start_date) AS min_start,
         MAX(end_date) AS max_end,
         SUM(budget) AS ETC,
         calcothergroup
      FROM TblContractPlan01Budget
      WHERE proj_id        = @proj_id  
            AND tier2_id    = @tier2_id 
            AND tier3_id    = @tier3_id 
            AND contract_id = 0
      GROUP by proj_id,tier2_id,tier3_id, contract_id, contract_nbr, contract_desc, calcothergroup

   SELECT   contract_id,
            contract_nbr,
            contract_title,
            ntp_date,
            curr_completion_date,
            actuals_LTD,
            orig_amt,
            min_start,
            max_end,
            ETC,
            calcothergroup
   FROM @tmpContract
   ORDER BY status DESC, contract_nbr,  ntp_date

GO

Third Version: The DBA wrote a workaround using table variables instead of field variables and that also fails to produce an error or a dataset in Servoy but does in Query Analyzer:

   CREATE PROCEDURE dbo.ud_BudgetContractSelect
      @proj_id INTEGER,
      @tier2_id INTEGER, 
      @tier3_id INTEGER,
      @sortorder VARCHAR(30)
   AS

-- Create Procedure ud_BudgetContractSelect
-- retrieve the distinct values of contract_id for base contracts on budget rows
-- ( base contract rows have the parent id = to the contract id )
-- UNION'ed with rows attached to amendments and the contract is not on any other budget row
-- for this proj_id, tier2_id, tier3_id
-- 
-- PARAMETERS IN:
-- proj_id		PK of TblProj01Project
-- tier2_id		PK of TblProj07Tier2
-- tier3_id		PK of TblProj09Tier3
-- sortorder   contract_nbr and start_date are the only 2 values
-- by contract_nbr is the default
-- PARAMETERS OUT:
-- none
-- FUNCTION CALLS
-- udfBTLContractSelect
-- Last Change Date and Description
-- Jan    2004 written by Vicki Cowles-CS3W Associates Inc.
-- Jan 15,2004 add ETC column 
-- Apr 15,2004 add milestone col, remove ETC, change EAC to ETC/EAC
-- Apr 22,2004 remove budget line data (minstart, max end, total dollars) 
-- and replace with contract info (contract total dollars, ntpdate, actuals)
-- Jun 17,2004 split the etc/eac columns into 2. eac is once agan the calculated column
-- oct 13,2004 add 4th parm to call of udfBTLContractSelect. target_id = 0
-- jan 05,2005 change selects to be just the fields for the servoy app
-- jan 25,2005 add the minstart, maxend, and sum(etc) for the budget rows in this contract
--             also return all the 'other' rows in this proc, grouped by the field calcothergroup

-- --	test lines
-- 	DECLARE @proj_id int, @tier2_id int, @tier3_id INTEGER, 
-- 		@sortorder VARCHAR(30)
-- 	SET @proj_id = 134  -- 282
-- 	SET @tier2_id = 1  -- 19
-- 	SET @tier3_id = 0  -- 0
-- 	SET @sortorder = ''
-- --	end test lines 

   DECLARE @target_id INT, @ETC MONEY, @min_start AS DATETIME, @max_end AS DATETIME, 
   @i INT, @cnt INT, @contract_id INT

   DECLARE @tmpContract TABLE (
   tcid INT IDENTITY (1,1),
   contract_id INT,
   contract_nbr VARCHAR(20),
   contract_title VARCHAR(150),
   ntp_date DATETIME,
   curr_completion_date DATETIME,
   actuals_LTD MONEY default 0,
   orig_amt MONEY default 0,
   status INT,
   min_start DATETIME,
   max_end DATETIME,
   ETC MONEY default 0,
   calcothergroup INT default 0    )

   SET @target_id = 0  -- this tells the function to get current budget rows, not saved target rows

   INSERT INTO @tmpContract (
         contract_id,
         contract_nbr,
         contract_title,
         ntp_date,
         curr_completion_date,
         actuals_LTD,
         orig_amt,
         status     )
      SELECT
         contract_id,
         contract_nbr,
         contract_title,
         ntp_date,
         curr_completion_date,
         actuals_LTD,
         orig_amt,
         status  
      FROM dbo.udfBTLContractSelect ( @proj_id, @tier2_id, @tier3_id, @target_id )

   SELECT @cnt = count(*) FROM @tmpContract
   SET @i = 1
   WHILE @i <= @cnt
   BEGIN
      SELECT @contract_id = contract_id FROM @tmpContract WHERE tcid = @i

      SELECT   @min_start = MIN(start_date),
               @max_end   = MAX(finish_date),
               @ETC       = SUM(current_forecast)  
      FROM ViewBudgetTierLine  
      WHERE  proj_id        = @proj_id  
            AND tier2_id    = @tier2_id 
            AND tier3_id    = @tier3_id 
            AND contract_id = @contract_id

      UPDATE @tmpContract
      SET   min_start = @min_start,
            max_end = @max_end,
            ETC = @ETC
      WHERE tcid = @i

      SET @i = @i + 1
   END

-- now get the 'other' rows to show as 'contracts'
   INSERT INTO @tmpContract (
         contract_id,
         contract_nbr,
         contract_title,
         min_start,
         max_end,
         ETC,
         calcothergroup              )
      SELECT
         contract_id,
         'TEMP:' + contract_nbr,
         contract_desc,
         MIN(Start_date) AS min_start,
         MAX(end_date) AS max_end,
         SUM(budget) AS ETC,
         calcothergroup
      FROM TblContractPlan01Budget
      WHERE proj_id        = @proj_id  
            AND tier2_id    = @tier2_id 
            AND tier3_id    = @tier3_id 
            AND contract_id = 0
      GROUP by proj_id,tier2_id,tier3_id, contract_id, contract_nbr, contract_desc, calcothergroup

   SELECT   contract_id,
            contract_nbr,
            contract_title,
            ntp_date,
            curr_completion_date,
            actuals_LTD,
            orig_amt,
            min_start,
            max_end,
            ETC,
            calcothergroup
   FROM @tmpContract
   ORDER BY status DESC, contract_nbr,  ntp_date

HELP? Servoy bug?

ellen

2

i need to see much more.
Are you sure it doesn’t return anything?
for example:
dataset.getExceptionMsg()
doesn’t return anything?

Also how do you call them? What scripts are you using?

I really can’t tell anything with the info i have now. The best would be to have the sample database with a small solution.

3

Haven’t tried getexception, will do asap. In the meantime, I am running the below script and no dataset appears even when stepping through the method in debugger (when the stored procedure is written using table variables or field variables in MSSQL):

var maxReturnedRows = 1000;
var procedure_declaration = '{call ud_BudgetContractSelect(?,?,?,?)}';
var args = new Array(4); 
args[0] = globals.gProject;
args[1] = globals.gTier2ID;
args[2] = globals.gTier3ID;
args[3] = '1'
var directionArray = new Array(4);
directionArray[0]=0;
directionArray[1]=0;
directionArray[2]=0;
directionArray[3]=0;
var dataset = databaseManager.executeStoredProcedure(controller.getServerName(),procedure_declaration, args, directionArray, maxReturnedRows);
for ( var i = 1; i <= dataset.getMaxRowIndex(); i++)
{
   dataset.rowIndex = i;
   controller.newRecord()
   fk_projid = globals.gProject
   userid = globals.gStaff
   tier1_id = globals.gTier1ID
   tier2_id = globals.gTier2ID
   tier3_id = globals.gTier3ID
   contract_id = dataset[1]
   contract_nbr = dataset[2]
   contract_title = dataset[3]
   ntp = dataset[4]
   currcomp = dataset[5]
   actual = dataset[6]
   currauth = dataset[7]
   //mindate = dataset[8]
   //maxdate = dataset[9]
   //etc = dataset[10]
   eac = etc + actual
 }
Sort();
4

We’ve been experimenting with Servoy’s ability to get datasets from MS SQL Stored Procedures, and have developed the pattern of success/failure. My DBA wrote this for you guys:

what works:

  1. a simple select from a table(s)
  2. a simple select from a user defined function that returns a table

what fails:

  1. a stored procedure that has a table variable where the data is collected from several sources, inserted into the table variable and then selected
  2. a select statement that has variables selected along with columns