Android Question Result Set crash when fetching NextRow

[janitra]

Hi,

I want to ask a little question.

I have a project that take 8 image using camera intent.
I put the picture in B4XImageView, after that I get the bitmap and save it to SQLite.

Few reason why I must save to SQLite.
- I need to send this bitmap (will be converted to Base64) to web server via HTTP Post. After that the Base64 Image will be converted back as JPG and put in folder.
- User won't able to delete the image because it's won't show in file manager or gallery. It also save the hassle for user to delete the old image because old data will be set to delete from SQLite when the data is over 1 month old.

The problem I have is, when I try to retrieve the file using Result Set (ExecQuery2), it give SQLiteBlobTooBigException on resultSet.NextRow.
I also try cursor and have same exception.

I have read that cursor have max size which is 1 MB.
Is there any work around?

Attached the simplified version of my project.

 

[Erel]

- I need to send this bitmap (will be converted to Base64) to web server via HTTP Post. After that the Base64 Image will be converted back as JPG and put in folder.
- User won't able to delete the image because it's won't show in file manager or gallery. It also save the hassle for user to delete the old image because old data will be set to delete from SQLite when the data is over 1 month old.

Both have nothing to do with SQLite.

I have read that cursor have max size which is 1 MB.
Is there any work around?

No. The actual limit depends on the Android version.

 

[aeric]

You have a table with these columns.

        sqlLite.ExecNonQuery("CREATE TABLE IF NOT EXISTS ProgressChange (DetailProjectCode TEXT, " _
            & "ProgressDate TEXT, " _
            & "Progress TEXT, " _
            & "Picture1 BLOB, " _
            & "Picture2 BLOB, " _
            & "Picture3 BLOB, " _
            & "Picture4 BLOB, " _
            & "Picture5 BLOB, " _
            & "Picture6 BLOB, " _
            & "Picture7 BLOB, " _
            & "Picture8 BLOB, " _
            & "IsSync TEXT)")

When you insert data:

data.Command("INSERT INTO ProgressChange VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0)", _
                Array As Object(1, DateTime.Date(DateTime.Now), _
                pic1Convert, pic2Convert, pic3Convert, pic4Convert, pic5Convert, pic6Convert, pic7Convert, pic8Convert))

What value will be inserted for column "Progress"?

I think it is a bad practice not to specify the column names when inserting data into a table.

Try change the SQL to something like this:

INSERT INTO ProgressChange (DetailProjectCode, ProgressDate, Progress, Picture1, Picture2, Picture3, Picture4, Picture5, Picture6, Picture7, Picture8, IsSync) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0)

 

[janitra]

What value will be inserted for column "Progress"?

Progress column will be inserted an integer value.
I have the field on my complete project.
User won't able to save if progress field is empty or not number.
All column will be inserted with value, hence why I did not specify column name.

 

[aeric]

All column will be inserted with value, hence why I did not specify column name

I guess you miss my point.
Your table has 12 columns but you only insert 11 columns. Thus, your values are inserted wrongly.

 

[janitra]

I guess you miss my point.
Your table has 12 columns but you only insert 11 columns. Thus, your values are inserted wrongly.

Don't worry aeric.
I've make sure the insert statement is fine on my full project.
On my attached project, maybe there's some sort of mis-typed code.

 

[aeric]

The problem I have is, when I try to retrieve the file using Result Set (ExecQuery2), it give SQLiteBlobTooBigException on resultSet.NextRow

And you also missed the part where the error occurs.

Without the code that giving the error, I doubt anyone could guess the problem.
What I could guess is your code is trying to convert binary/blob value into probably a text type column or vice versa, due to the value of Picture1 has been inserted into column Progress.

 

[DonManfred]

The problem I have is, when I try to retrieve the file using Result Set (ExecQuery2), it give SQLiteBlobTooBigException on resultSet.NextRow

then you probably fetch too much data. limit the results so that the resultset-ammount does not go over the limit.

Storing big blob-data in a sqlite-db is a mistake and you should redesign your databasedesign.

 

[janitra]

And you also missed the part where the error occurs.

Without the code that giving the error, I doubt anyone could guess the problem.
What I could guess is your code is trying to convert binary/blob value into probably a text type column or vice versa, due to the value of Picture1 has been inserted into column Progress.

My bad.

The error occur on

Public Sub SelectArray(query As String, param() As Object) As List
    Dim result As List
    result.Initialize
    
    Dim resultSet As ResultSet = sqlLite.ExecQuery2(query, param)
    
    Dim columnCount As Int = resultSet.ColumnCount
    
    Do While resultSet.NextRow
        Dim valueMap As Map
        valueMap.Initialize
        valueMap.Clear
        
        For j = 0 To columnCount - 1
            If resultSet.GetColumnName(j).Contains("ResourceData") Then
                valueMap.Put(resultSet.GetColumnName(j), resultSet.GetBlob2(j))
            Else
                valueMap.Put(resultSet.GetColumnName(j), resultSet.GetString2(j))
            End If
        Next
        
        result.Add(valueMap)
    Loop
    
    resultSet.Close
    
    Return result
End Sub

And it give

android.database.sqlite.SQLiteBlobTooBigException: Row too big to fit into CursorWindow requiredPos=0, totalRows=1

 

[janitra]

then you probably fetch too much data. limit the results so that the resultset-ammount does not go over the limit.

Storing big blob-data in a sqlite-db is a mistake and you should redesign your databasedesign.

True.
But my objective is so user cannot tinkering with the photo (either edit or delete the photo from File Manager or Gallery because its work related).
Also the other reason is user is not always have a network connection because the user is on the site, that's why the system is set to save the data first in the phone instead of sending it directly to server.
Because of that reason is why I try to save to SQLite.

I think I'll make a new table to store the image per row and convert it to byte using OutputStream and compress using quality of 40.
So one main data will have 8 rows of image.
This will make the image per row is not over 1MB and will be cleared periodically.

 

[aeric]

Public Sub SelectArray(query As String, param() As Object) As List

What is the query look like?

I suggest you SELECT one column to test then proceed with next column until you found which column data causing the error.

Example:

SELECT ResourceData1 FROM ProgressChange

 

[Brian Dean]

Don Manfred said ...

Storing big blob-data in a sqlite-db is a mistake

... and you said ...

But my objective is so user cannot tinkering with the photo (either edit or delete the photo from File Manager or Gallery because its work related).

So why don't you save your files in app internal storage where the user and file managers cannot reach it, and just save the filepath and the file creation date in the database? Don Manfred was right.