I am trying to Add a Picture Blob to a database. I am converting the Blob to a string first and retrieving it via PictureToJPEGStringMBS and reverse function but when I try passing the Blob, the data doesn’t get saved out. All the other Fields save out and retrieve ok except the picture data. Not sure what else to check?
Var sql As String
sql = "CREATE TABLE Team (ID INTEGER NOT NULL, Date TEXT, Council TEXT, CouncilEmail TEXT, MyLat Double, MyLong Double, Mynotes TEXT, Pic1 BLOB, Pic2 BLOB, PRIMARY KEY(ID));"
AddRecord
If Not IsConnected Then
MessageBox("Not conected to the database ")
Return False
End If
Var sql As String = "INSERT INTO Team (Date, Council, CouncilEmail, MyLat, MyLong, MyNotes, Pic1, Pic2) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
Try
DB.ExecuteSQL(sql, date, Council, CouncilEmail, MyLat, MyLong, MyNotes, pic1,pic2)
Catch e As DatabaseException
Return False
End Try
Return True
Just a WAG here, but since the MBS functions deal with a String, why not try defining the SQLite db type as TEXT instead of BLOB? I realize BLOB is one of the datatypes SQLite supports, but the MBS functions are treating them as strings. So it seems worth a shot to test…
You might also try using a generic SQLite DB editor to view the database contents and see if you are failing to store the picture blob data or only failing to retrieve it. It isn’t clear (to me) the timing of what is shown for Pic1 and Pic2 in the screenshot. If this is at the point of the insert, those variables appear to be empty.
Ok I will try the TEXT idea but at the moment the data isn’t making it to the Add Record method so pretty sure it isn’t saving out is the problem. I can’t see why the data can’t make it to the method when all the other data does. I thought there might be a limit on the size so I reduced the data to a small picture but no difference.
Set a breakpoint in what calls that method and make sure pic1 and pic2 have data in them. It looks to me like you are not passing data into the code block.
I suggest encoding the data, either as Hex or Base64. The problem with using binary data in Strings is that null characters (chr(0)) sometimes interact badly with other String functions and cause string truncation.
I think it must be something I am doing in terms of some old screens of clashing variables from copied code from an old project as I have written this before and it has worked fine. I will dig deeper and thanks for the suggestions they prompted some other things that will work better.
Next time, as an alternative, consider not putting the pictures in the database itself. In the database store the name of the picture file, and then keep all the pictures in a folder near the database. Keeps the database smaller.
Just thinking on restructuring the database a bit more.
I am storing a Latitude and longitude of a map location as separate fields but what if I wanted to store all the MapLocation data for that point is that possible?
I basically want to click on the MapLocations that I have tagged and relate them to other data in my DB
