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.

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 datbase 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

 

5 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

Add your comment

E-Mail me when someone replies to this comment