Skip to main content



      Home
Home » Archived » BIRT » Stored Proc dataset not returned
Stored Proc dataset not returned [message #168612] Thu, 08 June 2006 12:05 Go to next message
Eclipse UserFriend
Originally posted by: keith.chappelow.siemens.com

I have two stored procedures in MS SqlServer that return data using SQL
Query Analyzer but BIRT does not get any data in the dataset.

I first noticed this when creating the dataset and trying to do a preview.
I then modified the stored procedure to just do a select statement and
created a report with the fields. With just the "fake" select statement
every works but as soon as I restore the stored procedure to what it
should really be returning I get no data returned.

I have returned data with the stored procedure using the SqlServer jdbc
driver so I don't think that is the problem.

I can see no errors logged.

Here is the report definition for the dataset:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Written by Eclipse BIRT 2.0 -->
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3" id="1">
<property name="createdBy">Eclipse BIRT Designer Version 2.0.1 Build
&lt;20060222-1204></property>
<property name="units">in</property>
<data-sources>
<oda-data-source
extensionID="org.eclipse.birt.report.data.oda.jdbc" name="Data Source"
id="4">
<property
name="odaDriverClass">com.microsoft.jdbc.sqlserver.SQLServerDriver </property>
<property
name="odaURL">jdbc:microsoft:sqlserver://localhost:1433;SelectMethod=cursor;DatabaseName=LMS</property>
<property name="odaUser">LMS</property>
<encrypted-property
name="odaPassword">TE1T</encrypted-property>
</oda-data-source>
</data-sources>
<data-sets>
<oda-data-set
extensionID="org.eclipse.birt.report.data.oda.jdbc.SPSelectDataSet "
name="Data Set" id="5">
<property name="dataSource">Data Source</property>
<list-property name="parameters">
<structure>
<property name="name">StartDate</property>
<property name="dataType">string</property>
<property name="position">1</property>
<expression name="defaultValue">'05/04/2006
7:00'</expression>
<property name="allowNull">true</property>
<property name="isInput">true</property>
<property name="isOutput">false</property>
</structure>
<structure>
<property name="name">StopDate</property>
<property name="dataType">string</property>
<property name="position">2</property>
<expression name="defaultValue">'05/04/2006
12:00'</expression>
<property name="allowNull">true</property>
<property name="isInput">true</property>
<property name="isOutput">false</property>
</structure>
<structure>
<property name="name">SortControllerName</property>
<property name="dataType">string</property>
<property name="position">3</property>
<expression name="defaultValue">'ALL'</expression>
<property name="allowNull">true</property>
<property name="isInput">true</property>
<property name="isOutput">false</property>
</structure>
</list-property>
<property name="queryText">{call
LMS.GetLogicalDivertSumFromMC(?,?,?)} </property>
</oda-data-set>
</data-sets>
<page-setup>
<simple-master-page name="Simple MasterPage" id="2">
<page-footer>
<text id="3">
<property name="contentType">html</property>
<text-property name="content"><![CDATA[<value-of>new
Date()</value-of>]]></text-property>
</text>
</page-footer>
</simple-master-page>
</page-setup>
</report>

Here is the stored procedure:

CREATE PROCEDURE LMS.GetLogicalDivertSumFromMC
(
@aStartDate udt_CharDateTime,
@aStopDate udt_CharDateTime,
@aSortControllerName VARCHAR(30)
)

AS
/*
** Revision Information:
** $Source:
//Grrp403a/DEPT1/SPE/Data/Product_Components/LMS/03-Developm ent/Code/Source/Database/SP/rcs/GetLogicalDivertSumFromMC.SQ L
$
** $Id: GetLogicalDivertSumFromMC.SQL 1.1 2006/02/14 15:24:10Z guntihs
Developing $
** $Name: SD_5.3_QC PD3.1SP1 PD3.1B445 PD3.1B428 $
**
*/

BEGIN

-- Create a temporary table to put the data to be queried an returned
in to.
CREATE TABLE #TempTable
(
LogicalDestination VARCHAR(32),
PhysicalDestinationID VARCHAR(10),
PhysicalDestination VARCHAR(32),
ScannerID INT,
SortControllerName VARCHAR(32),
Data INT DEFAULT 0
)

-- Preload the data into the temporary table
INSERT IGNORE INTO #TempTable( LogicalDestination, PhysicalDestinationID,
PhysicalDestination, ScannerID, SortControllerName)
SELECT LDM.LogicalDestinationID, P.PhysicalDestinationID,
P.PhysicalDestination, CDM.ScannerID, SC.SortControllerName
FROM SortDirector.SortD.PhysicalDestination P
INNER JOIN SortDirector.SortD.ScannerParameters SP ON P.ScannerID
= SP.ScannerID
INNER JOIN SortDirector.SortD.SortController SC ON
SP.SortControllerID = SC.SortControllerID
INNER JOIN SortDirector.SortD.ControllerDestinationMap CDM ON
P.PhysicalDestinationID = CDM.PhysicalDestinationID
AND P.ScannerID = CDM.ScannerID
INNER JOIN SortDirector.SortD.LogicalDestinationMap LDM ON
LDM.ControllerDestinationID = CDM.ControllerDestinationID
AND CDM.ScannerID = LDM.ScannerID
WHERE @aSortControllerName = 'ALL' OR SC.SortControllerName =
@aSortControllerName

UPDATE #TempTable
SET Data = Total
FROM #TempTable INNER JOIN
( SELECT ClientID , GroupType , Tag, SUM(Data) Total FROM
LMS.StatisticsLog WITH (NOLOCK)
WHERE SubGroupType = 'PDESTARRIVAL'
AND TIMESTAMP >= @aStartDate AND TIMESTAMP <= @aStopDate
AND (@aSortControllerName = 'ALL' OR ClientID =
@aSortControllerName)
GROUP BY ClientID, GroupType, Tag ) Stat
ON ClientID = SortControllerName
AND GroupType = ScannerID
AND Tag = PhysicalDestinationID

SELECT LogicalDestination, ScannerID, PhysicalDestination, Data
LogicalDestinationCount FROM #TempTable

END

This is an example of calling the stored procedure in Query Analyzer:

DECLARE @RC int
DECLARE @aStartDate varchar(50)
DECLARE @aStopDate varchar(50)
DECLARE @aSortControllerName varchar(30)
SELECT @aStartDate = '05/04/2006 7:00'
SELECT @aStopDate = '05/04/2006 12:00'
SELECT @aSortControllerName = 'ALL'
EXEC @RC = [LMS].[LMS].[GetLogicalDivertSumFromMC] @aStartDate,
@aStopDate, @aSortControllerName

And the data returned:
LogicalDestination ScannerID PhysicalDestination
LogicalDestinationCount
-------------------------------- -----------
-------------------------------- -----------------------
Door1 301 1011
0
Door10 301 2021
0
Door2 301 1022
0
Re: Stored Proc dataset not returned [message #168623 is a reply to message #168612] Thu, 08 June 2006 12:09 Go to previous messageGo to next message
Eclipse UserFriend
Originally posted by: keith.chappelow.siemens.com

Note: I am using BIRT 2.0.1
Re: Stored Proc dataset not returned [message #168687 is a reply to message #168623] Thu, 08 June 2006 15:05 Go to previous message
Eclipse UserFriend
Originally posted by: keith.chappelow.siemens.com

I found what was causing the problem. I added "SET NOCOUNT ON" in the
stored procedure. What was happening is the rowcount for the temporary
table was being interpreted as the data set.
Previous Topic:Cannot preview stacked area chart as PDF
Next Topic:Discouraged access
Goto Forum:
  


Current Time: Wed Jun 11 01:19:59 EDT 2025

Powered by FUDForum. Page generated in 0.04112 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top