Hi Bogs,
don't know if this helps. But i was also busy putting images into sqlite on Android and i found it hell to get it done, but finally got it to work. I thought it could give speed advantage and less data consuming to let the app update the internal database instead of getting all data and picture every time from the server. And although most advice is to not put images in a database, i wanted to see how it works. But i guess it is still easier to read pictures as a file from the server. There is almost no speed advantage, only some data. Well anyway long story short.
I retrieve data/blob via PHP from my server then with that update the existing database, that is, with updating sqlite i had some troubles, so now i delete all tables and data when an update is available and recreate all and put all data in it again. (sqlite is not exactly the same in working as mysql/mariadb i noticed)
Also retrieving other data and BLOB at the same time gave strange issues and problems. So i retrieve them apart. Any data except BLOB and then BLOB.
Code: Select all
put "CREATE TABLE products (id INT PRIMARY KEY, category TEXT, product TEXT, blablabla TEXT, artnr TEXT, price DECIMAL(5,2), tax DECIMAL(5,2), blabla DECIMAL(5,2), artpic BLOB)" into tSQL
revExecuteSQL gLocalDbID, tSQL
then after retrieving all "normal" data i get the BLOB info:
Code: Select all
post varSendData to URL "https://mywebsite.nl/a folde/getmyblobs.php"
put it into tPic
if char 1 of line 1 of tPic is space then delete char 1 of line 1 of tPic --somehow my 1st character is always a space when getting info via PHP into LC, this is not the case directly in a browser, a bugreport for this was created
put base64decode(tPic) into img"invisiblephoto" of card"whatever"
wait 50 milliseconds with messages --why so hasty
export image "invisiblephoto" of card"whatever" to tGT as PNG
put base64encode(tGT) into tPrdFt
put "UPDATE products SET artpic=:1 WHERE id=" & tId & " ;" into tSQL
revExecuteSQL gLocalDbID,tSQL,"*btPrdFt"
This is in a repeat loop to the number of id's in the database.
This was finally how i could get them in the sqlite db which worked, i also checked the link from MaxV which helped a it too.
Just putting the data directly into the DB after retrieving seemed not to work ok, so that's why i have it first in an invisible image first and export it again.
Now after this getting it into a DG was also not straight forward, i needed to put it again in an invisible image:
Code: Select all
put revQueryDatabase(gLocalDbID, "SELECT artpic FROM table WHERE id=" & tId & " ;") into tRecordSet
put revDatabaseColumnNamed(tRecordSet, "artpic", tImage) into tError #tImage contains data, tError contains errors
put base64decode(tImage) into img"invisibleimage"
export img"invisibleimage" to URL ("binfile:" & specialFolderPath("Documents") & "/prdft" & tLineCounter & ".png") as PNG ---ok
put specialFolderPath("Documents") & "/prdft" & tLineCounter & ".png" into tLocStat ---ok
put tLocStat into theDataA[tLineCounter]["articleimage"]
Well the rest you know how to get it in the DG
Of course there will be better ways on how to do this, but this was the way it worked for me after a lot of trial and error.