RUN QUERY displays no rows for DB stored proc (SOLVED)

ddumas

I have created the simplest of stored procs, which just does a SQL select. No parameters.

It is tested in SQL Server, and runs fine.

I call it with a Run Query command (qryTopAttendees) in a Visual Perspective (Member access level) frame tab. The Section displays with the correct columns, but no data.

In my QUERY qryTopAttendees, I call this as : EXEC_SP 'SP_TopAttendees' RETURN TopAttendees

I created a BO TopAttendees with the same attributes and data types that the sql select statement returns in the stored proc. Member has full access to TopAttendees .

Not sure why I had to create a persisted BO TopAttendees (not persisted gives a runtime error), to just to return query results from a stored proc, but looks like EXEC_SP requires a BO. I do not want to store these records returned in a database table. I just want to display the query results in a grid.

No rows display.

Dave

Jaymer

usually, cause you didn't return the 3 required columns aware needs:
id, ver, TS

use row_number for the ID, then version can be 1, and timestamp can even be null, but you could use getdate() but it really will not be used to null is fine

Jaymer

also, whenever something "doesn't work", the Tomcat Output and Server Output tabs are EXTREMELY helpful

ddumas

WOW, Thanks, worked perfect! That's documented nowhere that I can find.

This deserves a full stored proc 🙂 MSSQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE SP_TopCompetitors
AS
BEGIN

DECLARE @now datetime = getdate()

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT 
		count(1) as [NumberOfTimesAttended],
		m.FullName,
                -- magic columns needed by AWAREIM
                ROW_NUMBER() OVER(ORDER BY m.FullName ASC) AS ID,
		1 as [BASVERSION],   -- arbitrary integer, sounds like
		@now as [BASTIMESTAMP]
	from 
              [dbo].[ATTENDEES] att
	      inner join MEMBER m on (att.Member_RID = m.ID)
	group by m.FullName
	order by 1 DESC

        RETURN 0	
	
END
GO

Jaymer

this brings to mind another little known fact...

you may have heard that IDs need to be unique in aware or "you may have problems"

truth is (as far as I know it) that row #s ONLY need to be unique IN TABLES WHERE they are part of a business object GROUP.

Using Profiler, if you have 2 BOs in a group, you can see there is a UNION done in SQL.
Similar to if you made a VIEW with Unions:

CREATE VIEW V_TicketGroup AS
SELECT ID, Description, DateClosed, Type, ob_ProjROGroup_RID, ROorProj, Status, (CASE WHEN Type='Estimate' THEN 'TicketE' WHEN Type='Repair' THEN 'TicketR' WHEN Type='Pickup' THEN 'TicketPD' WHEN Type='Delivery' THEN 'TicketPD' END) as TikType FROM TicketE UNION ALL 
SELECT ID, Description, DateClosed, Type, ob_ProjROGroup_RID, ROorProj, Status, (CASE WHEN Type='Estimate' THEN 'TicketE' WHEN Type='Repair' THEN 'TicketR' WHEN Type='Pickup' THEN 'TicketPD' WHEN Type='Delivery' THEN 'TicketPD' END) as TikType  FROM TicketPD UNION ALL 
SELECT ID, Description, DateClosed, Type, ob_ProjROGroup_RID, ROorProj, Status, (CASE WHEN Type='Estimate' THEN 'TicketE' WHEN Type='Repair' THEN 'TicketR' WHEN Type='Pickup' THEN 'TicketPD' WHEN Type='Delivery' THEN 'TicketPD' END) as TikType  FROM TICKETR UNION ALL
SELECT ID, Description, DateClosed, Type, ob_ProjROGroup_RID, ROorProj, Status, (CASE WHEN Type='Estimate' THEN 'TicketE' WHEN Type='Repair' THEN 'TicketR' WHEN Type='Pickup' THEN 'TicketPD' WHEN Type='Delivery' THEN 'TicketPD' WHEN Type='Labor' THEN 'TicketL'END) as TikType  FROM TICKETL UNION ALL
SELECT ID, Description, DateClosed, Type, ob_ProjROGroup_RID, ROorProj, Status, (CASE WHEN Type='Estimate' THEN 'TicketE' WHEN Type='Repair' THEN 'TicketR' WHEN Type='Pickup' THEN 'TicketPD' WHEN Type='Delivery' THEN 'TicketPD' WHEN Type='Checklist' THEN 'TicketQ'END) as TikType  FROM TICKETQ

If there are RowIDs in any of these tables that already exist in another table in the UNION, then only 1 (don't know how it decides) will be in the final set. ("You may have problems")
So all your Master Tables in your DB could start at ID #1 because you're prolly not going to have those in Groups (for example).

If you ever did a Query (grid) on a Group of 2 tables, lets say it returns 20 recs. If you edit some IDs on 1 table to make them DUPs, then your grid won't have 20 when you re-run it. There won't be any error, per se, just the UNION will filter out the DUPs.
Just something to keep in mind as you do things externally of Aware.

ddumas

So, at least for SQL Server I am seeing a single database sequence called BAS_IDGEN_SEQ. That would seem to indicate that all ID columns are guaranteed to be unique across BOs.

Also, the TopAttendees BO has no rows (good), even though it is setup as persisted. Otherwise the IDs would be stepping on top of each other across different sprocs returning SQL selects into the same TopAttendees BO