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
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
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 tRecordSetThis 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 tImageWe then close the recordset and the database connection in order, for tidiness:
revCloseCursor tRecordSet
revCloseDatabase tID
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
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.