EXEC_SP Call not working

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
MarkP
Posts: 20
Joined: Tue Jul 28, 2020 2:57 am
Location: Brisbane AUS

EXEC_SP Call not working

Post by MarkP »

Hi everyone,
I have a stored procedure in SQL Server that is just producing a unique customer only list i.e. SELECT DISTINCT ... from a set of CustomerFarms
The procedure works fine if run in SSMS.
I created a B/O called CustomerList with the necessary attributes that I am retrieving from the procedure.
However, when I use EXEC_SP in Aware (8.6) and try to use the CustomerList B/O as the Return object to store the output into, I am getting the following error information from the Server Log:
ServerLogOutput.jpg
ServerLogOutput.jpg (70.48 KiB) Viewed 2951 times
I know these are Aware system generated fieldnames which are created as part of the CustomerList object so I initially tried removing these from the table structure directly in SQL Server so I was just left with the 2 columns I really wanted.
Still no luck.
Is anyone able to advise me what I might be doing wrong or need to do? I did try a suggested workaround I found in the forum re: IF EXISTS statements to build the unique list manually and this worked but OMG - it takes forever.
Thanks
UnionSystems
Posts: 197
Joined: Fri Jun 17, 2016 7:10 am
Location: Brisbane Australia
Contact:

Re: EXEC_SP Call not working

Post by UnionSystems »

Typing this on my phone so a bit restricted.

I think you need to include ID,BASTIMESTAMP & BASVERSION columns in your SQL SELECT statement.
AWS Linux, Windows Server, AIM 8.4 & 8.6
MarkP
Posts: 20
Joined: Tue Jul 28, 2020 2:57 am
Location: Brisbane AUS

Re: EXEC_SP Call not working

Post by MarkP »

Thanks UnionSystems
That got rid of the error messages - but unfortunately it killed the whole approach of trying to use a SELECT DISTINCT sql clause to build a unique customer name list as the ID field is always unique. :cry:
I've worked out a different process approach on how to build the list - still a bit slow but it does work ok.
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: EXEC_SP Call not working

Post by Jaymer »

no, this should have worked
plz post your code
its just a SQL issue
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
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: EXEC_SP Call not working

Post by PointsWell »

As long as you are not going to do any operation with the records you can put whatever you want into they ID and BASVersion fields.

I use Stored Procs to do dashboard calculations and just put arbitrary numbers into these fields as in my case the data returned doesn't relate to real entities.

You obviously cannot do that if you want to do something with the data, but as you are looking for a list of distinct names it doesn't suggest that you want to do anything but read.
BLOMASKY
Posts: 1470
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

Q

Post by BLOMASKY »

As others have said, there are 3 "hidden" columns in every Aware table. ID, BASVERSION, BASTIMESTAMP. (ok, the ID is not really hidden). When you call a SP and want data returned, you have 2 choices:

1st, If you just need a couple of fields returned, they can be output statements in your SP, so your SP can look like
CREATE PROCEDURE test1
@x integer -- THIS IS AN INPUT PARAM
@y integer output -- THIS IS RETURNED TO AWARE

as
select @y = .... from .... where ... = @x

and your Aware SP call looks like EXEC_SP 'test1' WITH '@x' = BO.attr, '@y' = bo.attr2 OUT

OR, if you want to return a list, a table, you always need to return the 3 hidden fields for each row returned. the ID HAS to be unique. the other 2 can be 1 for BASVERSION and getdate() for BASTIMESTAMP or any other contstants.

Now, to solve the problem of the SELECT DISTINCT you could write your SQL SP to have an inner and outer select so it could look like

SELECT ROW_NUMBER() OVER (order by custname) as ID, 1 as BASVERSION, getdate() as BASTIMESTAMP, custname from
(select distinct custname from sometable) as X

The ROW_NUMBER() OVER (order by somefield) will give a sequential range of numbers. Many other ways to do that. you can also do a NEXT VALUE FOR BAS_IDGEN_SEQ as ID

Bruce
Post Reply