Imagine you have a Stored Procedure that returns 100 records to an Aware Query.
These are not written to the DB but rather are displayed in the Query by using the RETURN functionality.
Does anyone know of a way to identify which record is selected for an Operations With Records action.
Seems like Aware has no idea?!
Any clues would be appreciated.
Virtual Records Returned to Query by Stored Procedure
-
- Posts: 2392
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
Virtual Records Returned to Query by Stored Procedure
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
-
- Posts: 197
- Joined: Fri Jun 17, 2016 7:10 am
- Location: Brisbane Australia
- Contact:
Re: Virtual Records Returned to Query by Stored Procedure
Hi Mark,
To resolve this we’ve used a stored procedure to actually INSERT records into a temporary mySQL/mariaDB table and then querying that table so we can pass data to “Operations With Records”. Having real rows behind the query also seems to enable the column filters to work in the Query (filters don’t work for us on stored procedure “RETURNS” operation).
We’ve done this in a few different places. Typically we include a column in the temporary table that binds the resulting records to the LIRU so we can delete them as required.
To resolve this we’ve used a stored procedure to actually INSERT records into a temporary mySQL/mariaDB table and then querying that table so we can pass data to “Operations With Records”. Having real rows behind the query also seems to enable the column filters to work in the Query (filters don’t work for us on stored procedure “RETURNS” operation).
We’ve done this in a few different places. Typically we include a column in the temporary table that binds the resulting records to the LIRU so we can delete them as required.
AWS Linux, Windows Server, AIM 8.4 & 8.6
-
- Posts: 2392
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
Re: Virtual Records Returned to Query by Stored Procedure
Thanx Dave.
Yes, I am aware of that and normally would use that methodology. But, in this instance needing to write the data to another table and then
read that data back into an Aware query negates the advantage of using a Stored Procedure.
Yes, I am aware of that and normally would use that methodology. But, in this instance needing to write the data to another table and then
read that data back into an Aware query negates the advantage of using a Stored Procedure.
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Re: Virtual Records Returned to Query by Stored Procedure
Mark,
The time it takes sql to select the records and do the insert is less than a millisecond. The query will display without delay to the user. I do this in quite a few places and it is instant.
It’s worth leveraging the power and speed of sql.
The time it takes sql to select the records and do the insert is less than a millisecond. The query will display without delay to the user. I do this in quite a few places and it is instant.
It’s worth leveraging the power and speed of sql.
Roger Ross
AwareIM 8.7 (build 3025) ~ MS-SQL ~ Windows 10 ~
AwareIM 8.5 (build 2828) ~ MS-SQL ~ Windows 10 ~
AwareIM 8.7 (build 3025) ~ MS-SQL ~ Windows 10 ~
AwareIM 8.5 (build 2828) ~ MS-SQL ~ Windows 10 ~