How to display photo from MySQL table

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
gwbasic
Posts: 34
Joined: Tue Mar 16, 2010 6:39 am

How to display photo from MySQL table

Post by gwbasic » Tue Mar 23, 2010 12:14 am

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

Klaus
Posts: 11207
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: How to display photo from MySQL table

Post by Klaus » Tue Mar 23, 2010 2:56 pm

Hi,

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


Best from germany

Klaus

gwbasic
Posts: 34
Joined: Tue Mar 16, 2010 6:39 am

Re: How to display photo from MySQL table

Post by gwbasic » Tue Mar 23, 2010 11:52 pm

blob

Zryip TheSlug
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 159
Joined: Tue Jan 26, 2010 10:15 pm
Contact:

Re: How to display photo from MySQL table

Post by Zryip TheSlug » Wed Mar 24, 2010 12:38 am

gwbasic wrote: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
TheSlug
http://www.aslugontheroad.com - Tutorials, demo stacks and plugins for LiveCode
Data Grid Helper - An intuitive interface for building LiveCode's Data Grids
Excel Library- Extends the LiveCode language for controlling MS Excel

gwbasic
Posts: 34
Joined: Tue Mar 16, 2010 6:39 am

Re: How to display photo from MySQL table

Post by gwbasic » Wed Mar 24, 2010 2:59 am

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

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 960
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: How to display photo from MySQL table

Post by trevordevore » Wed Mar 24, 2010 2:33 pm

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.
Trevor DeVore
ScreenSteps - http://www.screensteps.com

Levure Application Framework: https://github.com/trevordevore/levure
LiveCode Resources for Developers: http://livecode.bluemangolearning.com

Zryip TheSlug
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 159
Joined: Tue Jan 26, 2010 10:15 pm
Contact:

Re: How to display photo from MySQL table

Post by Zryip TheSlug » Wed Mar 24, 2010 8:34 pm

trevordevore wrote:

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
TheSlug
http://www.aslugontheroad.com - Tutorials, demo stacks and plugins for LiveCode
Data Grid Helper - An intuitive interface for building LiveCode's Data Grids
Excel Library- Extends the LiveCode language for controlling MS Excel

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 960
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: How to display photo from MySQL table

Post by trevordevore » Thu Mar 25, 2010 4:41 pm

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 ...'
Trevor DeVore
ScreenSteps - http://www.screensteps.com

Levure Application Framework: https://github.com/trevordevore/levure
LiveCode Resources for Developers: http://livecode.bluemangolearning.com

Zryip TheSlug
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 159
Joined: Tue Jan 26, 2010 10:15 pm
Contact:

Re: How to display photo from MySQL table

Post by Zryip TheSlug » Mon Mar 29, 2010 11:19 pm

trevordevore wrote: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.
TheSlug
http://www.aslugontheroad.com - Tutorials, demo stacks and plugins for LiveCode
Data Grid Helper - An intuitive interface for building LiveCode's Data Grids
Excel Library- Extends the LiveCode language for controlling MS Excel

askari
Posts: 19
Joined: Fri Jul 01, 2011 11:45 pm

Re: How to display photo from MySQL table

Post by askari » Wed Jul 13, 2011 11:52 pm

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

Post Reply

Return to “Databases”