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