Passing Multiple References to an SQL Stored Procedure

UnionSystems

My objective is to enable users to define and save "queries". I've chosen not to use the built in User Defined Queries and I'm trying to use SQL Stored Procedures and the EXEC_SP function in Aware IM.

I've stored the query parameters in Aware IM (StoredProcedure BO below) and query reference attributes by using the BusinessSpace_BusinessObject_RID column in the underlying SQL table to query the reference objects ID.

The stored procedure SQL SELECT command is

SELECT * FROM BusinessSpace_PERSON WHERE ps_BusinessObject_RID = PassedID;

The AwareIM Query Rule that passes data to the SQL SELECT is

EXEC_SP 'PersonQuery' WITH '@PassedID'=StoredProcedure.pm_Attribute.ID RETURN Person

StoredProcedure.pm_Attribute.ID above actually contains multiple references but only 1 ID seems to actually get used by the SQL query. It feels like I'm only passing 1 ID to the SQL query but perhaps its the syntax of the SQL query thats wrong. I don't know how I can inspect what parameters the stored procedure is receiving as I really do not know SQL. Any ideas?

johntalbott

AIM is passing the single value that StoredProcedure.pm_Attribute.ID is pointing to at the time of calling the SP.

If AIM is treating StoredProcedure.pm_Attribute.ID as an array "under the covers" it may be possible to do what you want.

In any event, it's not going to be as straightforward as you are likely hoping. The reason being that SQL by default will not recognize that you are passing an array of values, so the SP end will be more complicated than the simple Select statement in your example. If you Google .. "passing an array to a sql stored procedure" ... you'll see what I mean.

MS SQL Server has a "fairly" straightforward to handle it. I'm not sure about MySQL.

UnionSystems

Thanks John,

I have found away to do this.

In AIM I'm building a plaintext list of the required IDs with the following AIM Attribute rule.

StoredProcedure.Attribute_Query='<<LIST_LINE(StoredProcedure.pm_Attribute,``,`,`,`,`,`ID`)>>'

Storing this in calculated attribute (handy for debugging) and it ends up looking like

17083,17085,17088

Then I've found a FIND_IN_SET option in mysql that searches the text list. The mySQL command is now

SELECT * FROM BusinessSpace_PERSON WHERE  (Attribute_Query="" OR FIND_IN_SET(ps_Attribute_RID,Attribute_Query));

johntalbott

How are you calling the SP?

Is your SELECT statement in the SP exactly like that?

btw ...what made you decide to not use AIM User Defined Queries?

UnionSystems

Hi John,

The code I presented before had names changed to make it more generic so it was not exactly what I'm doing. I did get the search through EXEC_SP working but discovered I needed to do SQL JOINs to included reference attributes in the search criteria and it all got to hard.

That said I've now got a better understand of the strengths of SQL Stored Procedures (thanks to BLOMASKY's excellent video https://recordings.join.me/TNfswyFvc0aA76GVKJmwgg). Looking at the operations for an equivalent SQL query has made me realise I could get the same result using a normal AwareIM Query (which is so much easier).

That said I do have an additional future requirement to do geocoding queries (eg get all the geo points that fall within a randomly drawn geo region). I suspect this query will need to be done in SQL as many SQL's now have native geocoding functionality. I'd prefer to do it all in Aware but I'm suspecting geocoding queries are a valid reason to do that in SQL?? I'm thinking I might still do all the other attribute criteria FINDing in Aware and pass the results to the stored procedure (eg "FIND Person with Age between 10 and 30 and gender Female", then pass that result to the SQL to filter by geo location).

Regarding your question on why I'm bypassing User Defined Queries, my reason were :

Change the User Interface
Restrict what objects and attributes different users had access to
Store different queries in different categories and possible owned by different users