将请求分为1 MB的块（和cursor.close（））后，仍然会发生SQLiteBlobTooBigException [英] SQLiteBlobTooBigException still occurs after dividing the request in chunks of 1 MB (and cursor.close())

查看：140
本文介绍了将请求分为1 MB的块（和cursor.close（））后，仍然会发生SQLiteBlobTooBigException的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

可以将6MB的文本文件直接导入到我的数据库中。但是，由于CursorWindow的限制为2MB，因此无法提取文本。 （我应该使用过文件，但是有些用户已经遇到了这个问题，我需要阅读全文才能将其放入文件中）
我使用了substr（一种特殊的SQL函数）仅请求1 MB并且有效。但是，以下while循环在第二次迭代后不起作用（这意味着即使我调用cursor.close（），也不会清空CursorWindow，因此对于第一次迭代它只有1MB，但是在第二次迭代之后有2MB，并抛出SQLiteBlobTooBigException异常）：

It is possible to import a text file of 6MB directly into my database. However, the text can't be extracted since CursorWindow has a limit of 2MB. (I should have used files, but some users already have this problem and I would need to read the entire text to be able to put it in a file) I used substr (a special SQL function) to only request 1 MB and it worked. However, the following while loop doesn't work after the second iteration (which means that even if I called cursor.close(), the CursorWindow wasn't emptied, so for the first iteration it only has 1MB, but after the second it has 2MB and the exception SQLiteBlobTooBigException is thrown):

        //Load in chunks
        BookDbHelper bookDbHelper = new BookDbHelper(GlobalContext.get());
        SQLiteDatabase readableDatabase = bookDbHelper.getReadableDatabase();
        //Query length
        int chunk_size = (int) Math.pow(2, 20);//mb
        String query_length = "SELECT _id, length(text) FROM " + BookContract.TABLE_NAME + " WHERE _id=" + id;
        Cursor cursor_length = readableDatabase.rawQuery(query_length, null);
        cursor_length.moveToFirst();
        int length = cursor_length.getInt(1);
        cursor_length.close();
        bookDbHelper.close();
        readableDatabase.close();
        //Query text
        int numSteps = length / chunk_size + 1;
        int i = 0;
        while(i < numSteps) {
            BookDbHelper bookDbHelper2 = new BookDbHelper(GlobalContext.get());
            SQLiteDatabase readableDatabase2 = bookDbHelper2.getReadableDatabase();
            int from = i * chunk_size + 1;
            int to = (i + 1) * chunk_size + 1;
            //L.v(from + ", " + to);
            String query = "SELECT _id, substr(text," + from + "," + to + ") FROM " + BookContract.TABLE_NAME + " WHERE _id=" + id;
            Cursor cursor = readableDatabase2.rawQuery(query, null);
            //Read
            cursor.moveToFirst();
            String string = cursor.getString(1);
            cursor.close();
            bookDbHelper2.close();
            readableDatabase2.close();
            //stringBuilder.append(string);
            i++;
        }

相关的列是_id和text（包含非常大的字符串），并且相关的sql函数是length（）（知道必要的迭代次数）和substr（）（这样就不会立即发生SQLiteBlobTooBigException，因为没有达到2MB的限制）。

The relevant columns are _id and text (which contains a very large string), and the relevant sql functions are length() (to know the number of iterations necessary) and substr() (so that the SQLiteBlobTooBigException doesn't occur right away since the 2MB limit wasn't reached).

我尝试关闭bookDbHelper和可读数据库，但并没有帮助。

I tried closing bookDbHelper and readableDatabase and it didn't help.

如何强制CursorWindow关闭，以便我发出请求1MB，清空CursorWinow，然后继续发出其他请求？

How can I force CursorWindow to close so that I make a request of 1MB, empty the CursorWinow, and continue to make other requests?

推荐答案


如何强制CursorWindow关闭以使我发出1MB的请求，清空CursorWinow，然后继续发出其他请求？

How can I force CursorWindow to close so that I make a request of 1MB, empty the CursorWinow, and continue to make other requests?

我不相信关闭游标是您的问题，就像不关闭游标会附加到游标并展开一样。

I don't believe that closing the Cursor is your issue, as if the by not closing the Cursor appends to the Cursor and expands.

相反，您的问题在于所构建的查询。

Rather your issue is with the query that you build.

简而言之， substr 功能不是从 到，而是 from for （ for 是返回字符串的大小/长度。您的计算基于第二个值，即字符的偏移量）。因此，提取的字符串的长度会随着块的大小而增加，直到减小为止超过字符串的末尾（在此之前先吹过CursorWindow）。

In short the substr function is not from to, it is from for (for being the size/length of the returned string. Your calculation is based upon the 2nd value being the offset of the character). As such the extracted string's length is increased by the chunk size until it exceeds the end of the string (blew the CursorWindow before this) when it reduces.

  • see substr(X,Y,Z) substr(X,Y)

因此，使用1MB的第二个块（如果看做是使用偏移量）注定要在第二次运行中失败，因为它实际上是长度（2MB）提取。减小到小于1MB会留出一些余地，但可能会破坏CursorWindow（但会获取其他数据）。

So the second chunk using 1MB (if looked at as using offsets) was doomed to failure on the 2nd run as it's actually the length (2MB) to extract. Decreasing to less than 1MB would allow some leeway but potentially blow the CursorWindow (but get additional data).

不过，作为替代方案，每个块都使用单个光标作为突出的行。结果可以是：-

However, as an alternative that uses a single cursor with each chunk as an extarcted row. The soution could be :-

    //Load in chunks
    BookDbHelper bookDbHelper = new BookDbHelper(/*GlobalContext.get()*/this);
    SQLiteDatabase readableDatabase = bookDbHelper.getReadableDatabase();
    //Query length
    StringBuilder wholeBookText = new StringBuilder();
    int chunk_size = (int) Math.pow(2, 20);//mb
    String query_length = "SELECT length(text) FROM " + BookContract.TABLE_NAME + " WHERE _id=?";
    Cursor cursor = readableDatabase.rawQuery(query_length, new String[]{String.valueOf(id)});
    int length = 0;
    if (cursor.moveToFirst()) {
        length = cursor.getInt(0);
    }
    int numSteps = length / chunk_size + 1;
    int i = 0;
    Log.d("BOOKINFO", "Length of Text is " + length + " Number of Chunks = " + numSteps + " Chunk Size = " + chunk_size);

    StringBuilder sb = new StringBuilder();
    for (i=1; i < length + 1; i+= chunk_size) {
        if (sb.length() > 1) sb.append(" UNION ALL ");
        sb.append("SELECT substr(text,")
                .append(String.valueOf(i)).append(",").append(String.valueOf(chunk_size))
                .append(") FROM ").append(BookContract.TABLE_NAME)
                .append(" WHERE _id=").append(String.valueOf(id));

    }
    sb.append(";");
    Log.d("BOOKINFOV2","SQL generated :-\n\t" + sb.toString());
    cursor = readableDatabase.rawQuery(sb.toString(),null);
    wholeBookText = new StringBuilder();
    while (cursor.moveToNext()) {
        wholeBookText.append(cursor.getString(0));
        Log.d("BOOKINFO","Obtained String who's length is " + cursor.getString(0).length() + "\n\tTotal Extracted = " + wholeBookText.length());
    }

而不是单个查询循环运行。这将生成一个查询，该查询将每个块提取为一行。那就是在所有查询之间建立一个UNION。例如

Rather than indivudal queries run in a loop. This generates a query that extracts each chunk as a row. That is it makes a UNION between all the queries. e.g.

SELECT substr(text,1,1048576) FROM book WHERE _id=4 
    UNION ALL SELECT substr(text,1048577,1048576) FROM book WHERE _id=4 
    UNION ALL SELECT substr(text,2097153,1048576) FROM book WHERE _id=4 
    UNION ALL SELECT substr(text,3145729,1048576) FROM book WHERE _id=4;




  • 从上述测试获得。

  • 可以看到，到（应该为 ）是块的大小。最后一个块将根据剩余数据被截断。

    • taken from a test run of the above.
    • as can be seen the to (should be for) is the chunk size. The last chunk will be truncated according to the remaining data.

      • 测试运行的全部输出：-

      The full output from the test run :-

      2019-12-16 14:21:35.546 D/BOOKINFOV2: SQL generated :-
        SELECT substr(text,1,1048576) FROM book WHERE _id=4 UNION ALL SELECT substr(text,1048577,1048576) FROM book WHERE _id=4 UNION ALL SELECT substr(text,2097153,1048576) FROM book WHERE _id=4 UNION ALL SELECT substr(text,3145729,1048576) FROM book WHERE _id=4;
2019-12-16 14:21:35.555 W/CursorWindow: Window is full: requested allocation 1048577 bytes, free space 1048128 bytes, window size 2097152 bytes
2019-12-16 14:21:35.585 D/BOOKINFO: Obtained String who's length is 1048576
        Total Extracted = 1048576
2019-12-16 14:21:35.599 W/CursorWindow: Window is full: requested allocation 1048577 bytes, free space 1048128 bytes, window size 2097152 bytes
2019-12-16 14:21:35.616 D/BOOKINFO: Obtained String who's length is 1048576
        Total Extracted = 2097152
2019-12-16 14:21:35.653 D/BOOKINFO: Obtained String who's length is 1048576
        Total Extracted = 3145728
2019-12-16 14:21:35.654 D/BOOKINFO: Obtained String who's length is 51
        Total Extracted = 3145779




      • 如您所见，CursorWindow会溢出，但该行不会添加，下次添加并可以访问它。

        • 当然，您可以修改mul提示查询方法，在这种情况下，代码可能是：-

        Of course you could adapt the multiple query approach in which case the code could be :-

            //Load in chunks
    BookDbHelper bookDbHelper = new BookDbHelper(/*GlobalContext.get()*/this);
    SQLiteDatabase readableDatabase = bookDbHelper.getReadableDatabase();
    //Query length
    StringBuilder wholeBookText = new StringBuilder();
    int chunk_size = (int) Math.pow(2, 19);//mb
    chunk_size = (1024 * 1024);
    String query_length = "SELECT length(text) FROM " + BookContract.TABLE_NAME + " WHERE _id=?";
    Cursor cursor = readableDatabase.rawQuery(query_length, new String[]{String.valueOf(id)});
    int length = 0;
    if (cursor.moveToFirst()) {
        length = cursor.getInt(0);
    }
    int numSteps = length / chunk_size + 1;
    int i = 0;
    Log.d("BOOKINFO", "Length of Text is " + length + " Number of Chunks = " + numSteps + " Chunk Size = " + chunk_size);

    int from = 1, to = chunk_size;
    while (i < numSteps && length > 0) {
        if (to > length) to = length;
        String query = "SELECT substr(text," + from + "," + (chunk_size) + ") FROM " + BookContract.TABLE_NAME + " WHERE _id=?";
        Log.d("BOOKINFOSQL",query);
        cursor.close();
        cursor = readableDatabase.rawQuery(query, new String[]{String.valueOf(id)});
        //Read
        if (cursor.moveToFirst()) {
            wholeBookText.append(cursor.getString(0));
            Log.d("BOOKINFO","Obtained String who's length is " + cursor.getString(0).length() + "\n\tTotal Extracted = " + wholeBookText.length());
        }
        cursor.close();
        i++;
        from = (i * chunk_size) + 1;
        to = from + chunk_size;
    }
    if (!cursor.isClosed()) {
        cursor.close();
    }
    Log.d("BOOKINFO", "The length of the extracted data is " + wholeBookText.length());

        以上结果为：-

        2019-12-16 14:16:15.336 D/BOOKINFO: Length of Text is 3145779 Number of Chunks = 4 Chunk Size = 1048576
2019-12-16 14:16:15.336 D/BOOKINFOSQL: SELECT substr(text,1,1048576) FROM book WHERE _id=?
2019-12-16 14:16:15.358 D/BOOKINFO: Obtained String who's length is 1048576
        Total Extracted = 1048576
2019-12-16 14:16:15.358 D/BOOKINFOSQL: SELECT substr(text,1048577,1048576) FROM book WHERE _id=?
2019-12-16 14:16:15.382 D/BOOKINFO: Obtained String who's length is 1048576
        Total Extracted = 2097152
2019-12-16 14:16:15.383 D/BOOKINFOSQL: SELECT substr(text,2097153,1048576) FROM book WHERE _id=?
2019-12-16 14:16:15.409 D/BOOKINFO: Obtained String who's length is 1048576
        Total Extracted = 3145728
2019-12-16 14:16:15.409 D/BOOKINFOSQL: SELECT substr(text,3145729,1048576) FROM book WHERE _id=?
2019-12-16 14:16:15.418 D/BOOKINFO: Obtained String who's length is 51
        Total Extracted = 3145779
2019-12-16 14:16:15.418 D/BOOKINFO: The length of the extracted data is 3145779

        这篇关于将请求分为1 MB的块（和cursor.close（））后，仍然会发生SQLiteBlobTooBigException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
移动开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆