How can read BLOB from external sqlite into my BLOB field?

Databases
1

How can I read a BLOB field from an external database and write it to a BLOB field in my own database?
I have tried this:
var rs as RowSet
var row As New DatabaseRow
myDB.SelectSQL(“SELECT zBLOB FROM myproduct”)
row.Column(“zBLOB”) = rs.Column(“zBLOB”).BlobValue
myDB.AddRow(“myproduct”,row)

Read from my database:
var s as String
s = DefineEncoding(row.Column(“zBLOB”).StringValue,Encodings.UTF8)

But “s” is empty

2

FIrst of all, do you really need a BLOB field? At the end of your code you show reading it as an encoded string. You can store that in a regular TEXT column in SQLite.

For the first part, you need two separate database connections - one to read from and the other to write to. Your code is using the same connection for both.

Edit: If both databases are SQLite, you can also use the Attach command to access both with the same connection, but you have to qualify the database in all queries.

3

External database “extDB” is a SQLite database only for one time import.
My database “myDB” is a database in my application.

“extDB” has a field “zBLOB” with type “BLOB”.
I can read the contents of the contents of this field with:
var s as String
var sql As String
sql = “zBLOB FROM myproduct”
var results As RowSet
Try
extDB.Connect
results = LicDB.SelectSQL(sql)
If results <> Nil Then
While Not results.AfterLastRow
s = DefineEncoding(row.Column(“zBLOB”).StringValue,Encodings.UTF8)
results.MoveToNextRow
Wend
results.Close
End If

In my application there is a TextArea with StyledText. The contents of this TextArea is store into myDB with this command:
var rs as RowSet
var sql as string
sql = “SELECT * FROM myTable WHERE id = ?”
rs = MyDB.SelectSQL(sql, id)
Try
rs.EditRow
rs.Column(“notes”).StringValue = EncodeBase64(myTextArea.StyledText.RTFData)
rs.SaveRow

And I read this table column with:
Var results As RowSet
Try
myDB.Connect
results = myDB.SelectSQL(sql)
Catch error As DatabaseException
MsgBox("DB Error: " + error.Message)
Return
End Try
If results <> Nil Then
While Not results.AfterLastRow
var s as new StyledText
s.RTFData = DecodeBase64(results.Column(“notes”).NativeValue)
myTextArea.StyledText = s
Wend
results.Close
End If

Now I would import the contents of field “zBLOB”) from “extDB” into the field “notes” into “myDB”.

“myDB”
sql = “SELECT * FROM myTable”
var rs as RowSet
rs = myDB.SelectSQL(sql)
if rs <> nil then
While Not rs.AfterLastRow
row.Column(“myOwnBlob”) = EncodeBase64(results.Column(“zBLOB”).BlobValue)
MyDB.AddRow(“license”,row)
wend
rs.close
end if

But this is not working, because the Blob data was added to my table, but it is not added to the TextArea. If I try to store the data in this way, it always doesn’t work:

var n as new StyledText
n.Text = rs.Column(“zBLOB”).BlobValue
row.Column(“notes”) = EncodeBase64(n.RTFData)

4

This is the solution:

Read and Write into myDB
// Notes
if rs.Column(“zBlob”).StringValue.IsEmpty = False then
var s as new StyledText
s.Text = DefineEncoding(rs.Column(“zBlob”).StringValue,Encodings.UTF8)
row.Column(“notes”).BlobValue = EncodeBase64(s.RTFData)
end if