Query using stored procedure

himanshu

Build Number	Database	OS
{8.8}	{MySQL}	{Windows}

What I did…

Used stored procedure inside query to fetch results, and using SUM calculation on few columns.

What I expected to happen

Same standard query having SUM at grid footer works fine but replacing with stored procedure SUM total is not showing correct data.

What happened

Attached example BSV
Attached log files

BLOMASKY

You have 2 choices when using a stored procedure to retrieve data for a query. You “CAN” use it just like you did, by having something like this in the query "EXEC_SP MyStoredProcedure RETURNS MyBO. The plus with this method is that it is real easy to write, However, there are a LOT of limitations doing it this way. You can not use the data to drill down to other tables, can not run Aggregates since Aware is not really retrieving the data in the way it has to for the aggregates.

I use stored procedures extensively in my applications. There are many advantages, Speed, ability to “flatten” out data, dynamic queries to list a few. However, this requires you to do 3 additional things;

Create a BO that contains the data that you want display in the query (and you don’t need ALL of the columns in the source BOs, just the data to be displayed.
Create a stored procedure that will 1st, delete the records in this new SQL table and then insert the results of your query into this new table.
Instead of just calling your query your process will have 2 lines. 1st, will call the storedProcedure and the 2nd, will display your query that is pointing to the new result BO.

Now you have all of the functionality of any native Aware query with the speed and flexibility that SQL offers.

Hope this helps
Bruce

nhofkes

An alternative to the approach suggested by Bruce is to write an additional render script for the query to display the total in the column. In addition, with a render script you can also implement row operations for the query (for example the possibility to drill down to other tables).
The javascript approach is more complicated than creating a new table in the SP, but for various reasons I didn’t want to make a new table each time that the SP runs.

himanshu

Thanks, Bruce — your stored procedure approach and implementation are truly impressive. I also make extensive use of SPs in many areas, as they offer noticeable performance improvements across various operations.

The suggestion you mentioned above could work as a last alternative; however, given the complexity of the query, there are a few caveats. It might end up being a more complex solution just to address a single summation issue.

@nhofkes I also do not want to make new table, deletes and insert as there are multiple users same time and there are few operations on frontend.

It would be nice if AwareIM can resolve these tiny issues when loading SP results. I will try either Bruce way or other way. 😃

nhofkes

himanshu
Here is an example how I calculate and display column totals for a grid that is generated by a SP.

const grid = widget;
const instrumentNames = "<<Session.InstrumentNames>>"; // This contains the column labels, concatenated by '|' (pipe character)`
// Attach Kendo grid event handlers
grid.bind("dataBound", onDataBound);
// Object numberColumns contains information about the number columns in the table and the functions to update and display the totals for each column.
// This code assumes that number columns (as generated by the SP) are named C01, C02, C03 etc.
const numberColumns = {
  numberOfColumns: 0,
  colFields: [],
  formats: [],
  totals: [],
  overallTotal: 0,
  isInitialised: false,
  initialise: function (instrumentNames) {
    // create array with column fields for the pivot table columns (C01, C02, C03 etc)
    let count = 0;
    instrumentNames.split("|").forEach(() => {
      count++;
      numberColumns.colFields[count - 1] = "C" + count.toString().padStart(2, "0");
    });
    this.numberOfColumns = count;
    this.totals.length = count;`
   // create array with formats for the number columns
    for (let i = 0; i < this.numberOfColumns; i++) {
      let gridColumn = grid.columns.find((x) => x.field === this.colFields[i]);
      this.formats[i] = gridColumn ? gridColumn.format : "";
    }
  },

  // Calculate the totals for each column and visible row in the grid
  calculateTotals: function () {
    this.totals.fill(0);
    grid.tbody.find("tr").each(function () {
      let dataItem = grid.dataItem(this);
      if (dataItem) {
        for (let i = 0; i < numberColumns.numberOfColumns; i++) {
          numberColumns.totals[i] += Number(
            dataItem[numberColumns.colFields[i]]
          );
        }
      }
    });`
  `  this.overallTotal = this.totals.reduce(
      (accumulator, currentValue) => accumulator + currentValue
    );
  },

  displayTotals: function () {
    // put the (sub)totals for each column as formatted string in an array
    let totalString = this.totals.map((x) => kendo.toString(x, "#,##0")); 

    // display the (sub)totals and possibly grand totals at the bottom cell of each column
    // this assumes that these HTML cells have an ID equal to EP_[datafield], e.g. EP_C01. This ID must be set in the template of the grid (column settings) `
  `  for (let i = 0; i < this.numberOfColumns; i++) {
      grid.footer.find("div#EP_" + this.colFields[i])
        .children()
        .html(totalString[i]);
    }
  },
}; 

// Handles the data bound event.
 // This function is called when the grid has been bound to the data.

function onDataBound(e) {
  if (!numberColumns.isInitialised) {
    numberColumns.initialise(instrumentNames);
    numberColumns.isInitialised = true;
  }
  numberColumns.calculateTotals();
  numberColumns.displayTotals();
}

BLOMASKY

himanshu When I have multiple users who might want to run the same SP to create the data, I add the regularUserID to the new BO and instead of deleting all records in the table prior to inserting the new ones, I just delete the ones attached to this RegularUser. Of course, when I insert, I add the user ID. (which means, I have to pass it to the Stored Procedure).

Performance wise, creating a new BO for the results, means I need less bandwidth to return the results since I am only returning the columns that the query users.

himanshu

@nhofkes Thanks for sharing the snippet will give a try to this also, and let you know.

@BLOMASKY Yes, you are absolutely correct — I share the same thought on how to proceed. I’m listing down the steps of changes required to make this work.