I am sorry to say that after spending a full day trying to get my app to work under v10.1, I had to roll-back to v10.0 again. It appears that 10.1 can’t handle (good enough) the queries that are using Stored Procedures. I am using MariaDB 12.3, don’t know if the same issues exist for MySQL and SQL Server.
v10.1 introduced the “Ability to paginate, Filter and sort queries implemented as stored procedures or SQL, just like it is done with native AwareIM queries”. Sounds great. However, I found that this is achieved by adding a ‘wrapper’ around the SP, which gets the SELECT statement from the SP as a parameter and executes it. In my case, this only worked for very simple SP, and even then only with modifications to the SP, and failed for a more complicated SP which had several subqueries.
My original SP followed the proven pattern:
SET @BO_ID := 0;
SELECT
@BO_ID := @BO_ID + 1 AS `ID`,
1 AS `BASVERSION`,
CURRENT_TIMESTAMP AS `BASTIMESTAMP`,
c1,
c2FROM table;
This is the simplified version, in reality the ‘table’ is often a subquery with various joints.
In v10.1, the ID and BASVERSION is added to the SELECT statement, even though it was already provided for. This means that the SP would be translated into something like
SELECT c1, c2, ID, BASVERSION FROM table
This fails because table does not have a column ID.
For a simple query I managed to work around this by creating an additional SELECT:
SET @BO_ID := 0;
-- BEGIN MAIN SELECTSELECT
SELECT c1, c2 FROM
(SELECT
@BO_ID := @BO_ID + 1 AS `ID`,
1 AS `BASVERSION`,
CURRENT_TIMESTAMP AS `BASTIMESTAMP`,
c1,
c2FROM table)
AS query;
-- END MAIN SELECT
But when I tried to use the same approach to a more complicated SP, it didn’t work. The SP worked fine in MariaDB itself, and there was no error in the server output in AIM, but there were no rows returned (or at least the resulting query was empty). I then tried to split this into two steps: first create the table with all relevant data, and then do a simple SELECT of the rows from that table. In itself this SP was correctly interpretated by AIM (what happens is that the stuff outside the SELECT statement is moved to the wrapper, and then the SELECT statement is executed), but there was still no data returned. This was even the case with a regular table (no temporary table), i.e. in MariaDB the table was correctly created, but somehow the SELECT does not return the data from that table.
I hope this can be fixed, and even better, that there will be the choice between the new behaviour (supporting paginating, filtering, sorting) and the old behaviour (without that support but also much more robust because it does not interfere with the SP). It would be great if there would be setting to control this, or to have a variation of the EXEC_SP statement, say regular EXEC_SP and EXEC_SPQUERY where the latter would support the filtering etc.
