Android Question [SOLVED] MSSQL to SQLITE blob problem

[makis_best]

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...

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

Then using the sub

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]

What is the output of:

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

 

[makis_best]

What is the output of:

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]

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

 

[OliverA]

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]

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

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

 

[makis_best]

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]

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]

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

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.