未能在GridDB中使用存储过程。

huangapple go评论94阅读模式
英文:

Failure to Use a Stored Procedure in GridDB

问题

由于我之前曾与关系型数据库一起工作,使用存储过程是一种常见的做法,但相当长一段时间以来,我已经转向使用GridDB,一个NoSQL数据库。我最近遇到的问题是使用存储过程时出现了以下错误:

[错误代码]: 151001

[描述](语法错误)

我认为在GridDB中存储过程是不支持的操作。是否有一种方法可以在GridDB中创建用户定义的函数(UDF),有人可以帮助提供下面我的代码示例的编辑,以在查询期间执行自定义数据处理操作吗?

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)

英文:

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)

答案1

得分: 1

我将为您创建一个UDF,该UDF根据订单中每个商品的数量和价格计算总价格:

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);

然后,在您的查询中调用它以计算总价格:

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");
    // 使用 orderId 和 totalPrice 进行其他操作
}

请注意,上述代码是用Java编写的,并且在查询中使用了创建的UDF函数来计算总价格。

英文:

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 = &quot;function calculateTotalPrice(quantity, price) { return quantity * price; }&quot;;

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

String containerName = &quot;OrderItems&quot;;
TimeSeries&lt;Row&gt; 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(&quot;SELECT order_id, calculateTotalPrice(quantity, price) AS total_price FROM %s&quot;, containerName);

Query&lt;Row&gt; query = store.query(queryStr, Row.class);
RowSet&lt;Row&gt; rs = query.fetch();

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

huangapple
  • 本文由 发表于 2023年7月23日 17:54:31
  • 转载请务必保留本文链接:https://go.coder-hub.com/76747615.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定