Failure to Use a Stored Procedure in GridDB

Since I have previously worked with relational databases, using stored procedures is a common practice, but for quite a long time, I have shifted to using GridDB, a NoSQL database. The problem that I recently faced was the use of a stored procedure which gave me the following error:

[Error code]: 151001

[Description] (Syntax error)

I think the stored procedure is an unsupported operation in GridDB. Is there a way of creating a user-defined function (UDF) in GridDB, and can anyone help provide edits in my code example below on how to use it to perform a custom data processing operation during the query?

import com.toshiba.mwcloud.gs.Collection;
import com.toshiba.mwcloud.gs.GSException;
import com.toshiba.mwcloud.gs.GridStore;
import com.toshiba.mwcloud.gs.GridStoreFactory;
import com.toshiba.mwcloud.gs.Query;
import com.toshiba.mwcloud.gs.RowKey;
import com.toshiba.mwcloud.gs.RowSet;

static class Order{
    int order_id;
        int total_amount;
}

Properties props = new Properties();
props.setProperty("notificationAddress", "239.0.0.1");
props.setProperty("notificationPort", "31999");
props.setProperty("clusterName", "defaultCluster");
props.setProperty("user", "admin");
props.setProperty("password", "admin");
GridStore store = GridStoreFactory.getInstance().getGridStore(props);


CREATE PROCEDURE CalculateOrderTotal(IN orderId INT, OUT totalAmount DECIMAL(10, 2))
BEGIN
    DECLARE itemPrice DECIMAL(10, 2);
    SET totalAmount = 0;

    DECLARE done INT DEFAULT FALSE;
        SELECT price FROM OrderItems WHERE order_id = orderId;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

END 

query = f"SELECT order_id, total_amount {CalculateOrderTotal}(quantity, price) AS total_price FROM {myCluster}"
    results = container.query(query)

Solution

I would create a udf that calculates the total price based on the quantity and price of each item in the order:

import com.toshiba.mwcloud.gs.GridStore;
import com.toshiba.mwcloud.gs.GridStoreFactory;
import com.toshiba.mwcloud.gs.GridStoreFactoryFactory;
import com.toshiba.mwcloud.gs.Row;
import com.toshiba.mwcloud.gs.RowSet;
import com.toshiba.mwcloud.gs.TimeSeries;

String totalPriceFunction = "function calculateTotalPrice(quantity, price) { return quantity * price; }";

GridStore store = GridStoreFactory.getInstance().getGridStore(props);
store.putFunction("calculateTotalPrice", totalPriceFunction, null);

String containerName = "OrderItems";
TimeSeries<Row> container = store.putTimeSeries(containerName, Row.class);

then call it in your query to calculate total price:

import com.toshiba.mwcloud.gs.Query;
import com.toshiba.mwcloud.gs.RowKey;
import com.toshiba.mwcloud.gs.RowSet;

String queryStr = String.format("SELECT order_id, calculateTotalPrice(quantity, price) AS total_price FROM %s", containerName);

Query<Row> query = store.query(queryStr, Row.class);
RowSet<Row> rs = query.fetch();

while (rs.hasNext()) {
    Row row = rs.next();
    int orderId = row.getInteger("order_id");
    double totalPrice = row.getDouble("total_price");
    // do something with orderId and totalPrice
}