我目前正在用C++开发Firebird数据库的UDR（用户定义例程）函数，目标是把关联的Category和Product表的查询结果转换成JSON字符串。

先给大家展示我的表结构和测试数据：

CREATE TABLE Category (
    CategoryId INTEGER PRIMARY KEY,
    CategoryName VARCHAR(50)
);

CREATE TABLE Product (
    ProductId INTEGER PRIMARY KEY,
    ProductName VARCHAR(50),
    CategoryId INTEGER,
    Price DECIMAL(10,2),
    FOREIGN KEY (CategoryId) REFERENCES Category(CategoryId)
);

INSERT INTO Category (CategoryId, CategoryName) VALUES (1, 'Electronics');
INSERT INTO Category (CategoryId, CategoryName) VALUES (2, 'Books');

INSERT INTO Product (ProductId, ProductName, CategoryId, Price) VALUES (1, 'Smartphone', 1, 599.99);
INSERT INTO Product (ProductId, ProductName, CategoryId, Price) VALUES (2, 'Laptop', 1, 1299.00);
INSERT INTO Product (ProductId, ProductName, CategoryId, Price) VALUES (3, 'Novel', 2, 19.99);

我希望把这些关联数据的查询结果生成JSON字符串，于是写了以下C++ UDR实现代码：

class iLJsonS_ForJsonPath002 : public Function<iLJsonS_ForJsonPath002, ThrowStatusWrapper> {
public:
    IMaster* master;
    IRoutineMetadata* metadata;

    iLJsonS_ForJsonPath002(const void*, IExternalContext* context, IRoutineMetadata* aMetadata)
        : master(context->getMaster()), metadata(aMetadata) {
    }

    virtual ~iLJsonS_ForJsonPath002() override = default;

    struct InMessage {
        struct Type {
            FbDataType_Blob sqlCommand;
        };
        Type data{};
        MessageDesc desc;

        static void setup(ThrowStatusWrapper* status, IMetadataBuilder* builder) {
            FbDT_BlobUTF8_Setup(status, builder, 0, FB_TEXT, "sqlCommand");
        }

        InMessage(ThrowStatusWrapper* status, IMaster* master)
            : desc(master, status, 3, setup) {
        }

        IMessageMetadata* getMetadata() const {
            return desc.getMetadata();
        }
    };

    struct OutMessage : public OutMessage_JsonResult<ThrowStatusWrapper> {
        using OutMessage_JsonResult::OutMessage_JsonResult;
    };

    void execute(ThrowStatusWrapper* status, IExternalContext* context, void* in, void* out) override {
        internalExecute(status, context, (InMessage::Type*)in, (OutMessage::Type*)out);
    }

    std::string resolveJsonSubqueries(ThrowStatusWrapper* status, IExternalContext* context, const std::string& sqlText) {
        std::regex pattern(R"(ForJsonPath\(\s*'(.*?)'\s*\))", std::regex::icase);
        std::smatch match;
        std::string result = sqlText;
        std::string::const_iterator searchStart(result.cbegin());

        while (std::regex_search(searchStart, result.cend(), match, pattern)) {
            std::string subquery = match[1].str();
            logToFile("subquery", subquery);

            // Recursively execute ForJsonPath with subquery
            std::string subJsonResult = executeSubqueryAsJson(status, context, subquery);

            // Replace the ForJsonPath('...') call with the literal JSON string
            result.replace(match.position(0), match.length(0), "'" + subJsonResult + "'");
            searchStart = result.cbegin() + match.position(0) + subJsonResult.length() + 2;
        }
        logToFile("subJsonResult_result", result);
        return result;
    }

    std::string executeSubqueryAsJson(ThrowStatusWrapper* status, IExternalContext* context, const std::string& sqlText) {
        // Recursively call internalExecute to get JSON result for subquery
        // Simulate BLOB I/O to/from string
        ISC_QUAD fakeInputBlob{};
        ISC_QUAD fakeOutputBlob{};
        writeBlob(status, context, sqlText, fakeInputBlob);

        InMessage::Type in;
        OutMessage::Type out;
        in.sqlCommand.sval = fakeInputBlob;
        in.sqlCommand.svalNull = 0;

        internalExecute(status, context, &in, &out);

        if (out.result.svalNull) return "null";
        std::string result = readBlob(status, context, out.result.sval);
        logToFile("result", result);
        return result;
    }

    void internalExecute(ThrowStatusWrapper* status, IExternalContext* context, InMessage::Type* in, OutMessage::Type* out) {
        out->result.svalNull = 1;
        try {
            if (in->sqlCommand.svalNull) return;

            std::string rawSql = readBlob(status, context, in->sqlCommand.sval);
            //std::string rawSql = "SELECT 1 AS ID, 'TestName' AS NAME";
            logToFile("rawSql", rawSql);

            rawSql = resolveJsonSubqueries(status, context, rawSql);

            //IStatus* s = context->getMaster()->getStatus();
            IAttachment* att = context->getAttachment(status);
            //ITransaction* txn = context->getTransaction(status);
            //ITransaction* txn = att->startTransaction(status, 0, nullptr);

            ITransaction* txn = nullptr;
            try {
                unsigned char tpb[] = { isc_tpb_version1, isc_tpb_read_committed, isc_tpb_rec_version };
                txn = att->startTransaction(status, sizeof(tpb), tpb);
                logToFile("txn_start", txn ? "Transaction started successfully" : "Transaction is null!");
            } catch (...) {
                logToFile("txn_start", "Transaction failed to start.");
                throw; // rethrow for the upper layer to catch
            }

            logToFile("prepare_stmt", "Preparing statement: " + rawSql);
            IStatement* stmt = att->prepare(status, txn, static_cast<unsigned>(rawSql.length()), rawSql.c_str(), 3, IStatement::PREPARE_PREFETCH_METADATA);
            // 原代码此处截断，未展示完整逻辑
        } catch (...) {
            // 异常处理逻辑待补充
        }
    }
};

目前我的实现思路是解析包含ForJsonPath(...)的SQL语句，递归执行子查询并替换成JSON结果，最终生成完整的JSON字符串。想请教各位，这个实现有没有需要优化的点？比如事务管理、Blob处理或者正则解析的细节，有没有潜在的坑需要注意？

内容来源于stack exchange