Stored Procedure Return BO, using Result?

[BLOMASKY]

The documentation on calling stored procedures says:

EXEC SP ‘procAlertGetAll’ RETURN Alert
This stored procedure does not require any parameters. It “selects” all alert records in the native database, that Aware IM will automatically convert to instances of the Alert object and put in the context or make available for a query

My question is the end of this, where it says "make available for query." How do I do this?

If after the EXEC_SP .... RETURN Alert
I have DISPLAY AlertQuery and even though records have been returned (I can see that in SEARCH_COUNT), the query is empty.


Thanks
Bruce

[johntalbott]

Along the same lines, it would be helpful to see examples of the prescribed approach(es) to:

1. Call a SP from a query and display the results
2. Call a SP from a process and use the results for further processing

EXEC SP ‘proc1’ OF SQLServer WITH ‘@param1’=1,’@param2’=2 RETURN SomeObject

Based on the syntax above taken from the User Guide, it appears that calling a SP from a process would make the most sense so that parameters can be populated on the fly. If called from a query, it appears the parameters will have to be static or require two steps: #1 saving a value to loggedinuser.someparameter and #2 calling the SP using loggedinuser.someparameter, which is not efficient.

Also, the "RETURN SomeObject" portion makes it appear that the fields returned in the query must map to a specific BO. Is that the case? Meaning ... is there no way to return the results from a SP that is the result set of joined tables?

[BLOMASKY]

John, I am not smart enough to call a SP from a query. I figured on the SQL tab I could call the SP, but I get an error "has to start with a SELECT"

So, I figured that with the SP called in a process, with a RETURN someBO, I would have someBO in context, but I am not sure how to pass this to a query.


However, I am sure that someone (maybe Vlad) will clue me in soon... (ish)


Bruce

[johntalbott]

With the EXEC_SP being an AwareIM specific function, I believe one has to use the Rule Form tab in the query.

[BLOMASKY]

as usual, you are right John (and better looking than me!) I was trying to use it in the SQL tab and in a procedure, but I can confirm it works FINE in the rule form.

Thanks
Bruce

[johntalbott]

Hmm ... I can't get it to work for some reason. It's a touch frustrating.

[johntalbott]

Ok. I can call get result set from a query that calls a SP if the SP is a simple "Select * From Table".

There must be more that I'm missing, yes?

[BLOMASKY]

John, Here is the good news (and the bad news...)

1st the good news. You HAVE to return the ID, BASVERSION and BASTIMESTAMP in addition to the column you really want. (You don't see that the last 2 are in your BO, but Aware creates them. YOU can put any number into basversion (just used for aware to know if someone else has updated this version before it attempts to save any new changes. Also put any timestamp value into bastimestamp, so my selects all start with SELECT ID, 1 as BASVERSION, getdate() as BASTIMESTAMP, attributeA, attributeB

The bad news... This can ONLY be used for display. You can not do (what I was attempting to do) have a process run when a user clicks on a row. The BO that is passed to your process is NULL! DARN! GOSH DARN!!!

Therefore, this is fine if you want to just display info, but you have no way to access any of the values. I really wanted to pass the selected customer to a process that would do something like "FIND Orders where Orders.customer = Customer' that fails!

Even a DISPLAY MESSAGE Customers.ID will fail

oh well

[johntalbott]

Good tip on adding the AIM standard columns.

Are you able to return a result set that includes a a JOIN?

[BobK]

John, Here is the good news (and the bad news...)

1st the good news. You HAVE to return the ID, BASVERSION and BASTIMESTAMP in addition to the column you really want. (You don't see that the last 2 are in your BO, but Aware creates them. YOU can put any number into basversion (just used for aware to know if someone else has updated this version before it attempts to save any new changes. Also put any timestamp value into bastimestamp, so my selects all start with SELECT ID, 1 as BASVERSION, getdate() as BASTIMESTAMP, attributeA, attributeB

The bad news... This can ONLY be used for display. You can not do (what I was attempting to do) have a process run when a user clicks on a row. The BO that is passed to your process is NULL! DARN! GOSH DARN!!!

Therefore, this is fine if you want to just display info, but you have no way to access any of the values. I really wanted to pass the selected customer to a process that would do something like "FIND Orders where Orders.customer = Customer' that fails!

Even a DISPLAY MESSAGE Customers.ID will fail

oh well

WHAT?????

Bruce, I think you are missing something (or maybe it is me).

I have a menu item that runs a query. On the query Rule Form I have the code to call a stored procedure. On the query’s Display Results I select the fields to display and add an Operations that starts a process that uses the BO returned from the stored procedure. Everything just like using a FIND.

The Stored procedure uses several CURSOR to find intermediate data records. Data in these intermediate records is used to create a SQL SELECT statement dynamically. This SELECT statement is stored in a TEXT field in the stored procedure and will find the final BO records I am interested in. The last thing the stored procedure does is use the TEXT field with the SQL SELECT statement to create a PREPARED statement which gets executed and returns the desired records to Aware.

The selected fields of the returned records are displayed and the user can click on the operation to run a process with the selected record.

FYI: I am using MySQL.

[johntalbott]

Bobk - thanks for the additional info. Does the result set from the SP need to map to the attributes of a single BO?

[BLOMASKY]

John: The result HAS to map to a single aware BO. Your stored procedure (SP) can join to multiple tables, have IF THEN ELSE logic, and anything that is valid in your flavor of SQL. This is one of the main reasons to use a SP, to do complex calcs that would either take way too long to do in aware procs or just too complicated.

Bob, you must be a lot smarter (and better looking than me. I have a BO, I have a SP that RETURNS the BO and I can see the fields in the query. Under Operations with Items, I have a process that is sending the BO to it. It says:

DISPLAY MESSAGE CustomerVDashboard.ID

and nothing is displayed (even though I see the ID on the query!

Looking at the log Viewer, I get "value of attribute ID of object CustomerVDashboard could not be resolved

Looking at the Tomcat log, It looks like the process was called properly:
Got XML request <root><start_process_action process_name="showV" force_no_collection="false" param_values="CustomerVDashboard" screen_width="1920" screen_height="1076" screen_orientation="landscape" ><object_reference object_name="CustomerVDashboard" object_id="3" />

So, I have NO way to work with the returned data.

Bruce

[BobK]

John: Bruce is right, the result has to map to a single aware BO.

Bruce:

and better looking than me

Come on, you’re not that bad looking.

I don’t know why you are experiencing issues using stored procedures. I created a simple stored procedure and aware process similar to what you describe and it worked for me.

From your previous post:

my selects all start with SELECT ID, 1 as BASVERSION, getdate() as BASTIMESTAMP,

At first I thought that was your problem. In my stored procedures, I always do SELECT * because I assumed that aware always returned all attributes. But then I changed my stored procedure to only return some attributes and it still worked. So, something else must be going on.

[BLOMASKY]

I could never do a select * (besides the many reasons to not do that.) since I am always joining multiple tables. If I just want the fields from one BO, there is usually not a reason to use a SP.

So, I will have to try with simple selects and see where it "breaks"
I am also using MSSQL not MYSQL but figure that should not make a difference.

bruce