Мобильная версияКод: Выделить всё
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);
< /code>
Я хочу сделать этот выбор в JSonstring. Поэтому у меня есть код в соответствии с ниже < /p>
class iLJsonS_ForJsonPath002 : public Function {
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 {
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(rawSql.length()), rawSql.c_str(), 3, IStatement::PREPARE_PREFETCH_METADATA);
logToFile("prepare_stmt", stmt ? "Statement prepared" : "Statement is NULL");
if (!stmt) {
throw std::runtime_error("Failed to prepare statement.");
}
logToFile("get_meta", "Getting output metadata...");
IMessageMetadata* meta = stmt->getOutputMetadata(status);
logToFile("get_meta", meta ? "Metadata retrieved" : "Metadata is NULL");
unsigned colCount = meta->getCount(status);
unsigned rowSize = meta->getMessageLength(status);
logToFile("col/row", std::to_string(colCount) + '/' + std::to_string(rowSize));
std::vector buffer(rowSize, 0);
logToFile("buffer_alloc", "Buffer allocated of size: " + std::to_string(buffer.size()));
logToFile("buffer_preview", std::string(buffer.data(), buffer.size()));
logToFile("NOTE", "Running COUNT(*) on Category");
auto count = executeScalar(status, context, "SELECT COUNT(*) FROM Category");
logToFile("Category_Count", std::to_string(count));
IResultSet* cursor = stmt->openCursor(status, txn, NULL, NULL, NULL, 0);
if (!cursor) {
throw std::runtime_error("Failed to open cursor.");
}
json resRoot = json::array();
logToFile("resRoot_init", resRoot.dump());
bool hasRows = false;
logToFile("buffer_data", buffer.data());
logToFile("fetch", std::to_string(cursor->fetchNext(status, buffer.data())));
while (cursor->fetchNext(status, buffer.data())) {
hasRows = true;
fprintf(stderr, "[Debug] Row fetched\n");
json resRow = json::object();
logToFile("resRow", resRow.dump());
for (unsigned i = 0; i < colCount; ++i) {
const char* name = meta->getField(status, i);
unsigned offset = meta->getOffset(status, i);
unsigned nullOffset = meta->getNullOffset(status, i);
short* isNull = reinterpret_cast(&buffer[nullOffset]);
if (*isNull) continue;
switch (meta->getType(status, i)) {
case SQL_TEXT:
case SQL_VARYING: {
const char* p = reinterpret_cast(&buffer[offset]);
resRow[name] = std::string(p, strlen(p));
break;
}
case SQL_LONG: resRow[name] = *reinterpret_cast(&buffer[offset]); break;
case SQL_SHORT: resRow[name] = *reinterpret_cast(&buffer[offset]); break;
case SQL_INT64: resRow[name] = *reinterpret_cast(&buffer[offset]); break;
case SQL_FLOAT: resRow[name] = *reinterpret_cast(&buffer[offset]); break;
case SQL_DOUBLE: resRow[name] = *reinterpret_cast(&buffer[offset]); break;
case SQL_BLOB: {
ISC_QUAD* blobId = reinterpret_cast(&buffer[offset]);
std::string blobText = readBlob(status, context, *blobId);
try { resRow[name] = json::parse(blobText); }
catch (...) { resRow[name] = blobText; }
break;
}
default: resRow[name] = ""; break;
}
}
resRoot.push_back(resRow);
logToFile("resRoot_Loop", resRoot.dump());
}
if (!hasRows) {
logToFile("fetchNext", "No rows fetched from result set");
}
cursor->close(status);
cursor->release();
stmt->release();
meta->release();
logToFile("bufferFinal", buffer.data());
logToFile("resRoot_final", resRoot.dump());
writeBlob(status, context, resRoot.dump(), out->result.sval);
out->result.svalNull = 0;
txn->rollback(status);
}
catch (const std::exception& ex) {
intptr_t err[] = { isc_arg_gds, isc_random, isc_arg_string, (intptr_t)ex.what(), isc_arg_end };
status->setErrors(err);
}
catch (...) {
intptr_t err[] = { isc_arg_gds, isc_random, isc_arg_string, (intptr_t)"Unknown error in ForJsonPath", isc_arg_end };
status->setErrors(err);
}
}
};
< /code>
и когда я тестирует на следующем коде < /p>
SELECT "ayjhm_JsonSel_ForJsonPath"(
CAST('SELECT c.CategoryId, c.CategoryName FROM Category c' AS BLOB SUB_TYPE TEXT)
)
FROM RDB$DATABASE;
< /code>
Файл журнала показывает: < /p>
rawSql: [SELECT c.CategoryId, c.CategoryName FROM Category c]
subJsonResult_result: [SELECT c.CategoryId, c.CategoryName FROM Category c]
txn_start: [Transaction started successfully]
prepare_stmt: [Preparing statement: SELECT c.CategoryId, c.CategoryName FROM Category c]
prepare_stmt: [Statement prepared]
get_meta: [Getting output metadata...]
get_meta: [Metadata retrieved]
col/row: [2/210]
buffer_alloc: [Buffer allocated of size: 210]
buffer_preview: [ ]
NOTE: [Running COUNT(*) on Category]
Category_Count: [2]
resRoot_init: [[]]
buffer_data: []
fetch: [0]
fetchNext: [No rows fetched from result set]
bufferFinal: []
resRoot_final: [[]]
Подробнее здесь: https://stackoverflow.com/questions/797 ... processing
