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

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

 

10 Comments

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

Add your comment

E-Mail me when someone replies to this comment