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

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
ddumas
Posts: 389
Joined: Tue Apr 23, 2013 11:17 pm

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

Post by 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
Last edited by ddumas on Tue Dec 24, 2019 6:38 pm, edited 1 time in total.
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: RUN QUERY does not display rows when I call a DB stored

Post by 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
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: RUN QUERY does not display rows when I call a DB stored

Post by Jaymer »

also, whenever something "doesn't work", the Tomcat Output and Server Output tabs are EXTREMELY helpful
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
ddumas
Posts: 389
Joined: Tue Apr 23, 2013 11:17 pm

Re: RUN QUERY does not display rows when I call a DB stored

Post by 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
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

About Unique ID #s in Tables

Post by 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:

Code: Select all

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.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
ddumas
Posts: 389
Joined: Tue Apr 23, 2013 11:17 pm

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

Post by 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
Post Reply