Android Question Limitation in Output file size?

RDCrozier · Apr 20, 2017

Hi

My application creates a cursor from a SQL select statement:

B4X:

    cSQL = "SELECT all Stock.id as Id, Stock.record_type as Record_Type, sum(Stock_Take.qty) as Qty FROM Stock_take JOIN Stock ON Stock_Take.id = Stock.id GROUP BY Stock.id UNION ALL SELECT Stock.id, Stock.Record_Type, 0 FROM Stock where Stock.id not in (select DISTINCT Stock_Take.id from stock_take) order by record_type, id"
    curResult = dbSQL.ExecQuery(cSQL)

I want to right this cursor to a csv file. I have tried to do this two ways, with the Text Writer:

B4X:

oTextWriter.Initialize(File.OpenOutput(tcFolder, tcFile, lAppend))

        curResult.Position=I

        oTextWriter.Write(curResult.GetString("Id") & ",")
        oTextWriter.Write(curResult.GetString("Record_Type") & ",")
        oTextWriter.Write(curResult.GetString("Qty") & ",")
        oTextWriter.WriteLine("")
Next

oTextWriter.Flush
oTextWriter.Close

I then tried to do the same thing with WriteList

B4X:

Dim List_Stock As List
Dim cID, cRecord_Type, cQty As String
Dim cStock_item As String
List_Stock.Initialize 
    '
For I = 0 To curResult.RowCount-1
   
curResult.Position=I 
cID = curResult.GetString("Id")
cRecord_Type = curResult.GetString("Record_Type")
cQty = curResult.GetString("Qty")
cStock_item = cID & "," & cRecord_Type & "," & cQty & ","
       
List_Stock.add(cStock_item)
next
File.WriteList(tcFolder,tcFile,List_Stock)

In both instances curResult is 262 rows in length, and in both instances the output file stop halfway through row 239. What is happening to the missing rows?

Probably worth mentioning that in both instances the for loop is executed 262 times, and List_stock contains 262 rows also.

Thanks
Dave

DonManfred · Apr 20, 2017

the sqlite cursor is limited in size (1 or 2 mb or so)....
If there is too much data you need to split the cursor into smaller parts.

Mahares · Apr 20, 2017

Perhaps before breaking the cursor into smaller segments, I would use the ExecQueryAsync to see it works:

B4X:

cSQL = "SELECT all Stock.id as Id, Stock.record_type as Record_Type, sum(Stock_Take.qty) as Qty FROM Stock_take JOIN Stock ON Stock_Take.id = Stock.id GROUP BY Stock.id UNION ALL SELECT Stock.id, Stock.Record_Type, 0 FROM Stock where Stock.id not in (select DISTINCT Stock_Take.id from stock_take) order by record_type, id"
dbSQL.ExecQueryAsync("SQLindex",cSQL,Null)

Sub SQLindex_QueryComplete(Success As Boolean, curResult As Cursor)
    If Success Then
        Dim List_Stock As List
        Dim cID, cRecord_Type, cQty As String
        Dim cStock_item As String
        List_Stock.Initialize
        '
        For I = 0 To curResult.RowCount-1  
            curResult.Position=I
            cID = curResult.GetString("Id")
            cRecord_Type = curResult.GetString("Record_Type")
            cQty = curResult.GetString("Qty")
            cStock_item = cID & "," & cRecord_Type & "," & cQty & ","
       
            List_Stock.add(cStock_item)
        Next
        File.WriteList(tcFolder,tcFile,List_Stock)
    Else
        Log(LastException)
    End If
    curResult.Close   
End Sub

DonManfred · Apr 20, 2017

Mahares said:
I would use the ExecQueryAsync to see it works

I don´t think it will make any difference.
The total size of a SQLite-Resultset is limited. You can run into this error even with one Databaseentry containing a BIG Blob.

http://stackoverflow.com/questions/5406429/cursor-size-limit-in-android-sqlitedatabase

Mahares · Apr 20, 2017

DonManfred said:
I don´t think it will make any difference

But we are not sure the curResult exceeds 1Mb for a total of 262 rows. Could it be that the time to execute the query takes longer than the usual as his query cSQL which only extracts 3 columns is quite complex as it involves select query, aggregate query, union query, subquery and sorting .

Android Question Limitation in Output file size?

RDCrozier

Member

DonManfred

Expert

Mahares

Expert

DonManfred

Expert

Mahares

Expert

Similar Threads

Android Question Limitation in Output file size?

RDCrozier

Member

DonManfred

Expert

Mahares

Expert

DonManfred

Expert

Mahares

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency