Loading an image from a database

This lesson will show you how to access and display an image file that is stored in a database as a 'BLOB' record type, which is the standard way of storing binary data (such as an image) in a database.

 

You can download the sample stack from this url: https://tinyurl.com/yah4yz5w

Setting up the stack

Setting up the stack

Firstly, we will set up a simple stack with 3 objects: a button named "Load", a field named "id", and an image named "imagecontainer". We will use the button to make the connection to the database and load the image, we will use the field to indicate which image we would like returned, and the image object to contain the image.

Add code to retrieve image

Add code to retrieve image

IMPORTANT: The following method is used as opposed to using the revDataFromQuery() function, which cannot be used to retrieve binary data due to limitations in the current database API used by LiveCode.

To the "Load" button, we add the following code:

put revOpenDatabase("mysql", "jasmine.on-rev.com", "jasmined_imageexample", "jasmined_image", "r8kWHcxRJrB0") into tID 

This opens the connection to the database and stores the connection id in tID. In this instance it is a remote MySQL database located at jasmine.on-rev.com - you should replace "jasmine.on-rev.com" with the name of your server, "jasmined_imageexample" with the name of your database, "jasmined_image" with the name of your database user and "r8kWHcxRJrB0" with your database user's password. See the dictionary entry for revOpenDatabase() for more information.

put revQueryDatabase(tID, "SELECT * FROM images WHERE id=" & field "id" & ";") into tRecordSet

This performs the query on the database. The table containing the images is called "images", and has two columns: "id" and "image". We select from the table the record matching the ID specified in our "id" field, and the resultant record set (containing one record) is stored in the variable tRecordSet

get revDatabaseColumnNamed(tRecordSet, "image", tImage) 

This function selects the column named "image" from the recordset we generated in the previous line, and places it into a container variable - tImage.

The tImage variable now contains the binary data of the image from the database. The property of an image which corresponds to it's binary 'source' is the text property, so we set this to tImage:

set the text of image "imagecontainer" to tImage

We then close the recordset and the database connection in order, for tidiness:

revCloseCursor tRecordSet

revCloseDatabase tID

Load the image

Load the image

Now, by pressing the "Load" button, we can load in an image from the database. There are 5 images in the test database to select (ids 1 through 5). You can download the stack used in this lesson here: http://jasminedavid.on-rev.com/dbimage.livecode

 

17 Comments

Steve

Thanks a million for this lesson. I struggled with this for a while and, as promised by RunRev support staff, a lesson/tutorial was created to assist. Your commitment to assist LiveCoders is remarkable. I will try to incorporate this method in my application and hope for the best!

Razvan

Hi!How can I put an image into database?Do you have a example for this?Thank you!

Elanor Buchanan

Hi Razvan

You need to treat images as binary data when putting them into a database. Have a look at the Dictionary entry for revDataFromQuery which explains how to do this in the comments section.

There is also a lesson you might find useful here

http://lessons.runrev.com/m/4071/l/263324-loading-an-image-from-a-database

It's an example of retrieving an image from a database but the method is similar.

I hope that helps.

Elanor

Razvan

Thank you Elanor .Working!

Uederson

Hello! I am a complete beginner, and need to know how to fill a database with some text from a text box. I would like to see an example with a text box (where I can write some text), and a button. When I click the button, the text within the text box will be recorded in a database and shown to a table.

Elanor Buchanan

Hi Uederson

There are a few steps you need to take.

1. Connect to the database
2. Write an SQL query to update the database using the text from the text box
3. Execute the SQL query to update the database

There is a lesson which covers all these steps, and a few more, using an SQLite database as an example at

http://lessons.runrev.com/m/4071/l/30516-how-to-create-and-use-an-sqlite-database

I hope that is useful.

Kind regards

Elanor

Simon Schvartzman

Hi Elanor, I would like to setup a remote SQL DB (including an image field as described in this lesson) to be populated by data sent from a LC mobile App. Information from the DB to be retrieved ideally using a WEB page but could also be a LC desktop App. After reading several lessons and posts on the forum I'm confused regarding where to host the DB (ideally for free initially just for testing purposes). Also remarks about the need to have a "middle PHP something" in order not to expose the DB to the "open internet" really confuse me. Could you please help by guiding me as where to start with a very simple DB let say having one text field and one image field that I would be able to write from a LC mobile App and read from the Web and/or LC desktop App? Many thanks in advance. Regards

Heather Laine

Hello Simon. If you want to use a remote SQL database, then you need a hosting service to host your SQL database. We offer this as part of our hosting service if you want to investigate that: https://www.livecode.com/hosting. You can connect directly to mySQL databases from LiveCode, but it is not best practice. For security purposes, as you have read, it is best to use an intermediate web service - we have a lesson on this here: http://lessons.livecode.com/m/4070/l/40932-accessing-web-services-using-livecode or you could check our youtube channel for some lessons on using JSON and connecting to databases. I will add your request for a basic lesson on the whole process to our lessons request queue.

Simon Schvartzman

Heather many thanks for your prompt answer. Since the remote DB access is password protected I'm still a bit confused about the potential security issues. Two more questions for you:
1 - How is this any different (or more risky) than using the dropbox library for instance?
2 - Assuming I'm OK to take the risks where would you advice me to start from (LC hosting is not an option at the moment as I'm not in the mood for a monthly fee just for an initial test)
Many thanks!

Heather Laine

This is getting somewhat outside the scope of this lesson and indeed is not LiveCode specific. I suggest you try some reading around the topic of webservices and database security, this might be a good starting point:

https://stackoverflow.com/questions/2142070/why-should-a-developer-use-web-services-instead-of-direct-connections-to-a-db

I cannot advise you on a free hosting option. Again, Google might be your best friend. A quick google produced this link which might be a starting point:

https://www.techradar.com/uk/best/best-free-web-hosting

Simon Schvartzman

Thanks Heather, point taken about being off-topic. Many thanks, regards

hilke

Hello,
I would like to use the retrieved image as icon on a button, but I can't figure out how.
Is it even possible?
Thanks

Elanor Buchanan

Hi Hilke

To use an image as an icon on a button you set the icon property to the ID of the image. So for the above example something like

set the icon of button to the ID of image "imagecontainer"

I hope that helps.

Kind regards

Elanor

hilke

Thank you, Elanor!
I hoped it could be done more directly, but it seems that all images have to be loaded into the stack, before they can be used as an icon. So, if I want to go through multiple SQL rows I have to create an imagecontainer for each dataset to prevent the image and icon being overwritten by the next one.
I found a solution for my case, but it's a bit "bulky" - especially because I wanted the images to be stored away in a separate card in the background.

I'm learning by doing - it's much fun, but veeeery slow sometimes :-)
Thanks & regards

Elanor Buchanan

Hi Hilke

I'm glad you got it working and you are enjoying LiveCode.

You can store the images on a separate card by referencing the card you want to store them on e.g.

set the text of image "imagecontainer" of card "iconResouces" to tImage

You could also create your image containers in code inside a loop that loops over each record from the query and do

create image ("icon_" & tIconNumber) in card "iconResouces"
set the text of image ("icon_" & tIconNumber) of card "iconResouces" to tImage

You can also check whether the image already exists etc

I hope that helps, and good luck with your project!

Kind regards

Elanor

Roland Hüttmann

Latest Livecode version: 1.6.1
I do not get a connection id for the trial db. Is the db running? There is no connection ID coming.
"button "Load": execution error at line n/a (External handler execution error: revdberr,invalid connection id) near "revdberr,invalid connection id""

Strangly enough, on my own MySQL database the "text of image xxx" is not working. I can save "the imagedata" in the mySQL column of type "BLOB" and also retrieve it and set the imagedata to the returned value.

Heather Laine

Hello Roland. Thank you for pointing this out, the link to the database was broken when the server moved. We've now redirected it to the new server and the lesson is working again.

Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.