How to display photo from MySQL table

[gwbasic]

Does anyone have any example on how to display photo from MySQL table.

[Klaus]

Hi,

do you know in what format the images are stored in the database?
We need to know this!


Best from germany

Klaus

[gwbasic]

blob

[Zryip TheSlug]

blob

Hello,

To read a blob from a database and to restore it, you can do this :

put revOpenDatabase("mysql", "127.0.0.1", "myDB", "root", "") into lDBConnector
put theID into lRecordID
put "SELECT myBlob FROM fileTable WHERE id = :1" into lSQLQuery
put revQueryDatabase(lDBConnector,lSQLQuery,"lRecordID") into lTheRecord
put revDatabaseColumnNamed(lTheRecord,"myBlob") into lBinaryData
ask file "Extract file from the DB as..."
put it into lFileToOpen
open file lFileToOpen for binary write
write lBinaryData to file lFileToOpen
close file lFileToOpen
revCloseCursor lTheRecord
revCloseDatabase lDBConnector

HTH,
TheSlug

[gwbasic]

Thanks. Would the other way around work if I modify the code to read the photo file then update the table?

[trevordevore]

Code: Select all

put revDatabaseColumnNamed(lTheRecord,"myBlob") into lBinaryData

The above line of code will not work for binary data. The reason is that the Revolution externals interface (which RevDB uses) does not support binary data through "put ... into ...".

Use this form of revDatabaseColumnNamed instead.

Code: Select all

put revDatabaseColumnNamed(lTheRecord, "myBlob", "lBinaryData") into theError

In this form RevDB stores the binary data in a variable named lBinaryData.

[Zryip TheSlug]

Code: Select all

put revDatabaseColumnNamed(lTheRecord,"myBlob") into lBinaryData

The above line of code will not work for binary data. The reason is that the Revolution externals interface (which RevDB uses) does not support binary data through "put ... into ...".

Use this form of revDatabaseColumnNamed instead.

Code: Select all

put revDatabaseColumnNamed(lTheRecord, "myBlob", "lBinaryData") into theError

In this form RevDB stores the binary data in a variable named lBinaryData.

Interesting Trevor,

The first time I tried to store and restore a blob (a pdf and an application) from a database, I encoded it in base64. To retrieve my blob I used successfully the following code :

Code: Select all

put revDatabaseColumnNamed(lTheRecord,"myBlob") into lBinaryFile
ask file "Extract file from the DB as..."
put it into lFileToOpen
open file lFileToOpen for binary write
write base64decode(lBinaryFile) to file lFileToOpen
close file lFileToOpen

I supposed that the base64encode / base64decode must do the difference.


Regards,
TheSlug

[trevordevore]

Yes, if you base64encode the binary data a text representation of that data is stored in the database. Text data works fine with 'put revDatabaseColumnNamed(...) into ...'

[Zryip TheSlug]

Yes, if you base64encode the binary data a text representation of that data is stored in the database. Text data works fine with 'put revDatabaseColumnNamed(...) into ...'

Thanks to have clarify that.

[askari]

All,
I'm trying to display a photo from a remote MySQL table.
I encode the photo and store it successfully in a blob field (this was also successful if it was a text field) like;
## put file from local disk into tChosenFile and display in img "img1"
put url("binfile:"& tChosenFile) into img "imgFromDisk"
## encode image data into binary to save to database as text
put base64encode (img "imgFromDisk") into tBase64ImgData1
When I retrieve it from the db i use;
put theListingDataB["lPic1"] into binFile
put base64decode (binFile) into decodedBinData
put decodedBinData into img "imgFromDb"

from the variable watcher i get the correct encoded data in binFile that was stored however after i decode i don't see anything in the img
binFile value like; /9j/4UPNRXhpZgAASUkqAAgAAAALAA4BAgAgAAAAkgAAAA8BAgAFAAAAsgAAABABAgAHAAAA ....
while
decodedBinData value like ; ˇÿˇ·CÕExif