Android Question [SOLVED] MSSQL to SQLITE blob problem

makis_best · May 17, 2019

I have one MSSql database with a table full with blob records TaEBlob (Code 'String', BlobData 'image', Size1 'Integer')

Now.... In sqllite I create a table using...

B4X:

Starter.LocalSQL.ExecNonQuery($"CREATE TABLE IF NOT EXISTS `LOCAL_EBlob` (`Item_Code` TEXT,  `BlobData` BLOB, `Size1` INTEGER)"$)

Then using the sub

B4X:

Sub EBlobInsert As ResumableSub
    Wait For (Connect) Complete (Success As Boolean)
    If Success Then
        Try
            Dim Qry As String
            Qry = $"SELECT Code, BlobData, Size1 FROM TaEBlob"$
            Dim sf As Object = RemoteSQL.ExecQueryAsync("RemoteSQL", Qry, Null)
            Wait For (sf) RemoteSQL_QueryComplete (Success As Boolean, Crsr As JdbcResultSet)
            If Success Then
                Dim i1 As Int = 1
                Do While Crsr.NextRow
                    LocalSQL.ExecNonQuery2($"INSERT INTO LOCAL_EBlob (Item_Code, BlobData, Size1)
                    VALUES (?, ?, ?)"$, Array As Object(Crsr.GetString("Code"), Crsr.GetBlob("BlobData"), Crsr.GetInt("Size1")))
                    i1 = i1 +1
                    Sleep(0)
                Loop
                Crsr.Close
            End If
        Catch
            Success = False
            Log(LastException)
        End Try
        CloseConnection
    End If
    Return Success
End Sub

When the sub finish without errors I go check the table in SQLite and I see that
I have records but all the blob fields are Null.

For example my result is something like

Code BlobData Size1
000035 BLOB 44
000042 BLOB 29
000059 BLOB 28

Why that happens?

Erel · May 19, 2019

What is the output of:

B4X:

Dim b() As Byte = Crsr.GetBlob(...)
Log(b.Length)

makis_best · May 20, 2019

Erel said:
What is the output of:

B4X:

Dim b() As Byte = Crsr.GetBlob(...) Log(b.Length)

I get a number like 25281 or 38705 depends what pic I choose to see

Erel · May 20, 2019

First step is to remove the Sleep(0) call and create a transaction. Never insert multiple records without a transaction.

OliverA · May 20, 2019

makis_best said:
Code BlobData Size1
000035 BLOB 44
000042 BLOB 29
000059 BLOB 28

How are you getting those results? What are you using to "view" your data?

makis_best · May 21, 2019

OliverA said:
How are you getting those results? What are you using to "view" your data?

I use "DB Browser (SQLite)" and "SQLiteSpy."

makis_best · May 21, 2019

Erel said:
First step is to remove the Sleep(0) call and create a transaction. Never insert multiple records without a transaction.

I create the sub with transaction but nothing change

makis_best · May 21, 2019

I find the problem...

When I did the sql select field BlobData to BlobData / 1024 it works...
I don't know why but it works.

Thank you all for your help.

OliverA · May 21, 2019

I know the thread is marked solved. Just to clarify:

makis_best said:
Code BlobData Size1
000035 BLOB 44
000042 BLOB 29
000059 BLOB 28

"BLOB" is the normal output of DB Browser to designate that the content of the column/field is a blob. It does not mean that there is no (Null) content. To see the size of the blob data, you could have done something like this:

Select *, length(BlobData) From Local_EBlob;

Within DB Browser's Execute SQL tab.

Android Question [SOLVED] MSSQL to SQLITE blob problem

makis_best

Well-Known Member

Erel

B4X founder

makis_best

Well-Known Member

Erel

B4X founder

OliverA

Expert

makis_best

Well-Known Member

makis_best

Well-Known Member

makis_best

Well-Known Member

OliverA

Expert

Similar Threads

Android Question [SOLVED] MSSQL to SQLITE blob problem

Well-Known Member

B4X founder

Well-Known Member

B4X founder

Expert

Well-Known Member

Well-Known Member

Well-Known Member

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency