my journey through Stored Procedures and Aware

[BLOMASKY]

I am not addressing why / when you should use Stored Procedures (SPs) in Aware, but instead, want to share what I have learned as I have explored the options available when using a SP with Aware.

There seem to be 5 ways to use SPs in Aware

1) Call an SP and have it update a value. I.e. EXEC_SP ‘MYSP’ WITH ‘@field’ = BO.attributeName OUT. While this will SOMETIMES work, most of the time, I need the info in LoggedInRegularUser and I have NOT been able to get consistent results. Therefore, I never use this

2) Call a SP to update an existing Aware Table. This gives me the option of having attributes in my BO updated with current (as of when the SP is run) data and can replace aware batch processes that would run for a very long time. For instance, one of my requirements, is to have an attribute in each customer record for the total sales for the last year (last 365 days) and the previous year (same range of dates). Since this changes each day, to have aware run a process each night to compute this takes about 30 minutes. Stored Procedure, about 1 second.

3) Call a SP to populate a new table that only exists after the SP is run. Again, I have requirements to provide a purchasing view for our inventory with totals for 4 diff. time periods. It is a very complex calculation and could not be done in Aware (or if it could, would take a really really long time). My SP is large (over 100 lines), complex and took a while to design, test, debug. However, it can Delete all rows in the table, run the calc, populate the table, and return back to aware in about 2 seconds. I do put the ID of related BOs into this one, and therefore, can easily drill down to the related tables. This is different from #2, in the fact this table ONLY exists after the SP is called, where in #2, my customer table is created and maintained and used in Aware.

4) Try to do what #3 does, but instead of populating an Aware BO, my SP has a RETURN clause. I.E. EXEC_SP ComputePurchasingData RETURN PurchaseAdvisory. This does NO way to access the data! If I just want to display it, that’s fine, but if I want to have an Operation on Item, and pass the PurchaseAdvisory, nothing gets passed. I assume that Aware will want to read ALL of the data from the selected row, and of course, there is NO data. Not so useful

5) Same as #4, but I return the REAL Aware BO. This requires me to add columns to the BO that are just used in the query, (they will be UNDEFINED if I ever access these attributes in any other Aware process / form / query, etc. but they will display the values that the SP returns. This has allowed me to perform what I could not do any other way. For example. Our system has trucks that are scheduled for each day to make deliveries. Each sales person has different customers assigned to them, when a salesperson logs into our system, it will look up each of the trucks for the day, and compute how many OF THEIR customers are available for each truck. If I didn’t have this option, I would need a new bo that had TruckID, SalesID, # of customers, and have some way to update this when new trucks are entered, or if a customer becomes assigned to a new sales person, etc. Instead, I had to add another attribute to the Truck table for # of your customers and populate that in the SP.

For those who are dipping their feet into using SPs with Aware, I hope this has helped.

Bruce

[BenHayat]

Nice work Bruce [Lee]
I suggest, to have a copy of this in the "Tips & Tricks".