Passing Multiple References to an SQL Stored Procedure

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
UnionSystems
Posts: 197
Joined: Fri Jun 17, 2016 7:10 am
Location: Brisbane Australia
Contact:

Passing Multiple References to an SQL Stored Procedure

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

Code: Select all

SELECT * FROM BusinessSpace_PERSON WHERE ps_BusinessObject_RID = PassedID;
The AwareIM Query Rule that passes data to the SQL SELECT is

Code: Select all

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?
AWS Linux, Windows Server, AIM 8.4 & 8.6
johntalbott
Posts: 619
Joined: Wed Jun 17, 2015 11:16 pm
Location: Omaha, Nebraska
Contact:

Re: Passing Multiple References to an SQL Stored Procedure

Post by 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.
VocalDay Solutions - Agility - Predictability - Quality

We specialize in enabling business through the innovative use of technology.

AwareIM app with beautiful UI/UX - https://screencast-o-matic.com/watch/crfUrrVeB3t
UnionSystems
Posts: 197
Joined: Fri Jun 17, 2016 7:10 am
Location: Brisbane Australia
Contact:

Re: Passing Multiple References to an SQL Stored Procedure

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

Code: Select all

StoredProcedure.Attribute_Query='<<LIST_LINE(StoredProcedure.pm_Attribute,``,`,`,`,`,`ID`)>>'
Storing this in calculated attribute (handy for debugging) and it ends up looking like

Code: Select all

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

Code: Select all

SELECT * FROM BusinessSpace_PERSON WHERE  (Attribute_Query="" OR FIND_IN_SET(ps_Attribute_RID,Attribute_Query));
AWS Linux, Windows Server, AIM 8.4 & 8.6
johntalbott
Posts: 619
Joined: Wed Jun 17, 2015 11:16 pm
Location: Omaha, Nebraska
Contact:

Re: Passing Multiple References to an SQL Stored Procedure

Post by 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?
VocalDay Solutions - Agility - Predictability - Quality

We specialize in enabling business through the innovative use of technology.

AwareIM app with beautiful UI/UX - https://screencast-o-matic.com/watch/crfUrrVeB3t
UnionSystems
Posts: 197
Joined: Fri Jun 17, 2016 7:10 am
Location: Brisbane Australia
Contact:

Re: Passing Multiple References to an SQL Stored Procedure

Post by 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
AWS Linux, Windows Server, AIM 8.4 & 8.6
Post Reply