Page 1 of 1

blob images in datagrid

Posted: Fri Jul 05, 2019 8:16 am
by paulalsmith1000
Dear Anyone

You'll have to forgive me as I'm very new to forums and in fact livecode, but I would really appreciate some help.

I have found tons of posts and lessons etc on the following, but nothing seems to quite answer the question / solve the problem:-

basically I have an sqlite db with some records and an image (blob) associated with each record (the fields are survey_id, gps_location, species_name, species_image, image_selected)

what i would like to do is import some of the fields of the sqlite table into a datagrid table.

the end result would be 3 columns - species_name, species_image, and then a tick box

the user would scroll down through the table, choose one of the images and then tick the tick box to select it.

this would then save back to the sqlite db in field image_selected

So far it seems fairly straightforward to get the info from any sqlite text fields into the datagrid, but as soon as I add the sqlite field containing the blob images, it goes wrong.

what I think I need is some way of telling the datagrid to set the text as an image? but im lost.

The reason ive posted here is because I started to use the datagrid helper trial version, but still no joy.
code -

command dgtest

put getDatabaseID() into tDatabaseID

put "SELECT Distinct species_name, species_image FROM survey_results" into tSQL

put revDataFromQuery(,,tDatabaseID,tSQL) into tRecords

if tRecords begins with "revdberr" then
answer error "There was a problem accessing the people database: " & tRecords
exit to top
end if

set the dgText of group "picdatagrid" to tRecords
end dgtest

Any help whatsoever would be a godsend,

Many thanks


Re: blob images in datagrid

Posted: Fri Jul 05, 2019 12:06 pm
by sphere
Hi Paul,

you need to edit the row of a datagrid. It has to have a part where an image can be inserted.
So one row has fields for text and image. After this every row will act the same.
Then for the number of lines of data you have, you insert it via a repeat loop.
The text and the image is inserted in a holder and this holder is assigned to the row.

Therefore you need to adjust the script of the DG a bit so the image will be inserted in the img"thisimage".
Also you need to use dgData.

It sound s a bit complex, but when you get it you will understand.

when i have time tonight i can post an example.
But there are examples on this forum and in the LC lessons on how to insert images in the DG.
This lesson will bring more light to your issue: ... -of-people


Re: blob images in datagrid

Posted: Fri Jul 05, 2019 10:31 pm
by paulalsmith1000
Hi Sphere

Thank you so much for the reply, if u could possibly post an example that would be awesome

Thanks again


Re: blob images in datagrid

Posted: Sat Jul 06, 2019 10:26 am
by sphere
Hi Paul,

I don't use the datagrid helper but,
once you know via the lesson how to add fields and images to a row then:

i use a handler to populate the grid, this is an simple example to use a local sqlite db. Assuming you have a sqlite db with data in it.
I don't use that anymore for filling my DG, i now use a remote DB and use php as middleware to fetch the data and sqlite to store local stuff like names of the user etcetera, but since you use sqlite.

Code: Select all

command uiPopulateCategories
   put "Graphics/App/" into theImageFolder
   #read form localDB and put in DG
   local tDatabasePath, tRecords
   put empty into theDataC
   put empty into tLineCounter
   put specialFolderPath("Documents") & "/productslocal.sqlite" into tDatabasePath
   put revOpenDatabase("sqlite", tDatabasePath, , , , ) into gLocalDbID
   put "SELECT * from yourcategorie" into tSQL
  put revDataFromQuery(tab,return,gLocalDbID,tSQL) into tRecords
  set the itemdel to tab
  repeat for each line tLine in tRecords
      add 1 to tLineCounter
     #put item 1 of tRecords into tRow --this is the id i don't want it in my dg
     put item 2 of tLine into theDataC[tLineCounter]["category"] --this is a piece of text
     put theImageFolder & "pijl.png" into theDataC[tLineCounter]["nextimage"] --this is an image
      end repeat
   #get it in the DG	
      lock screen
	  # now put it into the Datagrid which is a group called CategoryGrid in this example
   set the dgData of group "CategoryGrid" to theDataC
   ## Hilite first row
   set the dgHilitedLines of group "CategoryGrid" to 1 #this hilites the first row of the DG
   unlock screen
end uiPopulateCategories
Now you see in the above example i use an image from a folder, but you could also use one which you have stored in your sqlite db.

you can call this handler simply with for example:

Code: Select all

on openCard
end openCard
in the DG script you need to adjust this part so it matches with the field you have in the row:

Code: Select all

on FillInData pDataArray
   -- This message is sent when the Data Grid needs to populate
   -- this template with the data from a record. pDataArray is an
   -- an array containing the records data.
   -- You do not need to resize any of your template's controls in
   -- this message. All resizing should be handled in resizeControl.
   -- These 2 line matches the names of the fields you have on the row
   set the text of field "category" of me to pDataArray["category"]        
   set the filename of image "nextimage" of me to pDataArray["nextimage"]
end FillInData
i hope this helps a bit.


Re: blob images in datagrid

Posted: Sat Jul 06, 2019 4:58 pm
by paulalsmith1000
Thanks a million Sphere

I will give it a go.

Kindest regards


Re: blob images in datagrid

Posted: Sun Jul 07, 2019 12:45 am
by paulalsmith1000
Hi again Sphere

I did try what u said, but its the last bit in the fillin data array, that im still stuck on:

set the text of field "category" of me to pDataArray["category"]

This is fine with text etc, but i dont understand the exact syntax for the blob image

Ive tried

set the text of image "nextimage" of me to pDataArray["nextimage"]

But it just displays the binary not the text

Any more pointers would greatly appreciated


Re: blob images in datagrid

Posted: Sun Jul 07, 2019 3:31 pm
by sphere
did you base64encode the image before storing in the db? as it is binary

then with reading you need to base64decode it, and i think best is to put the text of it into a variable and then put the variable into the theDataC

or retrieve it first each loop and put it into a hidden img"hidden" then set the text of this hidden img to the dg.
an example is here : ... 424#p71161

note the *b which is important for storing binary data like blob

Re: blob images in datagrid

Posted: Tue Sep 03, 2019 12:32 pm
by Zryip TheSlug
Hi Paul,

Data Grid Helper (DGH) is a third party plugin. For questions relative to the datagrid control itself, prefer the beginners or experienced users forums, you have more chance to get answers.