本文介绍了Qt PL/SQL-赋值运算符-字符串缓冲区太小的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我一直在想办法解决这个问题.

I have been picking my brain for a while trying to figure this one out.

我遇到的问题是我在Oracle中使用的函数返回了BLOB.这是使用||连接在一起的项目的列表.

The problem I am having is that the function I am using in Oracle returns a BLOB. It's a list of items that are concatenated together using ||.

根据我所做的研究,

  1. QSQLQuery文档中,说:不完全支持使用return语句返回值或返回多个结果集的存储过程.有关详细信息,请参阅SQL数据库驱动程序." -这使我相信,如果Qt无法处理它,我可能需要切换到其他代码库.
  2. QOCI驱动程序的文档提到了此可以读取和写入二进制大对象(BLOB),但是请注意,此过程可能需要大量内存.您应该使用仅向前查询来选择LOB字段(请参阅QSqlQuery :: setForwardOnly())."
  1. In the QSQLQuery docs it says "Stored procedures that uses the return statement to return values, or return multiple result sets, are not fully supported. For specific details see SQL Database Drivers." - which leads me to believe I may need to switch to a different codebase if Qt cannot handle it yet.
  2. The documentation for the QOCI driver mentions this "Binary Large Objects (BLOBs) can be read and written, but be aware that this process may require a lot of memory. You should use a forward only query to select LOB fields (see QSqlQuery::setForwardOnly())."

我确实做了
query.setForwardOnly(true);
在我准备或执行语句之前.

I did set
query.setForwardOnly(true);
Before I prepared or executed the statement.

但是,我仍然收到此错误
QSqlError("6502", "Unable to execute statement", "ORA-06502: PL/SQL: numeric or value error: character string buffer too small\nORA-06512: at line 55\n")

However, I still get this error
QSqlError("6502", "Unable to execute statement", "ORA-06502: PL/SQL: numeric or value error: character string buffer too small\nORA-06512: at line 55\n")

我不得不稍微整理一下代码,希望这对我要完成的工作提供背景信息还是有帮助的

I had to scrub the code a bit, I hope this is still helpful to give context to what i'm trying to accomplish

              temp_clob       clob;
              name varchar2(183) := ?;
              start varchar2(16)  := ?;
              end   varchar2(16)  := ?;
              count  integer       := ?;
              return_val    named_redacted_table_object; -- Sorry had to remove this, it's a table with more Date, Varchar, etc
           begin
              dbms_lob.createtemporary(temp_clob, true);
              return_val := package_name.function_name (
                 set_name     => name,
                 start_time   => to_date(start, 'yyyy-mm-dd hh24:mi'),
                 end_time     => to_date(end, 'yyyy-mm-dd hh24:mi'),
                 max_count    => count);

        -- In here was a loop that would break apart the removed table object and make it into strings along the following lines
        -- '' || return_val(i).name || return_val(i).value || etc
        -- and would store these into the CLOB / temp_clob
              ? := temp_clob;
           end;

我无法获得像这样简单的东西

I could not get something as simple as this to work

begin
  ? := 'test123';
end;

假设我至少可以在Qt中读取此字符串.

With the assumption I could at least read this string in Qt.

这是我的Qt代码

QString name = "test";
QSqlQuery query(db);
query.setForwardOnly(true);
query.prepare(sql);
QString test_sql_text = ui->comboBox_test_text->currentText();
qDebug() << name;
query.bindValue(0, name);
query.bindValue(1, "2003-03-14 00:00");
query.bindValue(2, "2005-03-14 23:00");
query.bindValue(3, 2);
query.bindValue(4, QString(""), QSql::Out);
bool query_executed_ok = query.exec();
qDebug() << "did it execute?" << query_executed_ok;

// qDebug() << query.executedQuery();
qDebug() << query.boundValues();
qDebug() << query.lastError();
QSqlRecord rec = query.record();
qDebug() << rec;
int record_count = rec.count();
qDebug() << "Records: " << record_count;
while (query.next()) {
    for(int i=0;i<record_count;i++) {
       qDebug() << query.isValid() << " - " << rec.fieldName(i) << " " << query.value(i).toString();
    }
}

推荐答案

花了些时间,我意识到我给出了相当隐秘的代码.因此,感谢您 belayer 和在我的烂摊子上开了一枪.

It took some fiddling, and I realize I gave fairly cryptic code. So thank you to belayer and Koen for taking a shot at my mess.

我能够确定并为遇到此问题的任何其他人工作的原因:

What I was able to determine and get working for anyone else running into this:

首先让我说我不确定这是否是错误,或者我是否以QSqlQuery(用于处理SQL调用的类)的设计者所不希望的方式执行某些操作.

Let me start off by saying I am not sure if this is a bug, or if i'm doing something in a way that was not intended by the designers of QSqlQuery (The class for handling SQL calls).

该调用将在SQL Developer中工作,并且我将看到具有所有字符的预期CLOB.我无法使DBMS_Output工作,但是,我看到这篇文章说在将字符串绑定到查询之前保留字符串上的空间.

The call would work in SQL developer and I would see the intended CLOB with all characters. I was unable to get DBMS_Output to work, however, I saw this post saying to reserve space on the string before binding it to the query.

它解决了我的问题,并在调试窗口中显示了结果.但是,这提出了新的问题.如果字符串变得大于我的硬编码保留值怎么办?

It solves my issue and shows the result in the debug window. However, it presents a new problem. What if the string becomes larger than my hard coded reserve value?

这是该代码的更新代码

query.prepare(sql);
        QString name= ui->comboBox_name->currentText();
        qDebug() << project;
        query.bindValue(":name", project);
        query.bindValue(":start_date", "2005-03-14 00:00");
        query.bindValue(":end_date", "2006-03-14 23:00");
        query.bindValue(":max_count", 3);
        QString testStr ="*****";
        //testStr.truncate(0); //Maybe this works too?
        testStr.reserve( 1000000 ); // This did it for sure
        qDebug() << testStr.capacity();
        query.bindValue(":result_clob", testStr, QSql::Out);
        bool query_executed_ok = query.exec();
        qDebug() << "did it execute?" << query_executed_ok;
        if (query_executed_ok) {
            testStr = query.boundValue(":result_clob").toString();
            qDebug() << testStr;
        } else {
            qDebug() << "string is empty";
        }

我从帖子.

这篇关于Qt PL/SQL-赋值运算符-字符串缓冲区太小的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-18 21:40
查看更多