SQLite Images and Standalone Issues

Creating desktop or client-server database solutions?

Moderators: heatherlaine, Klaus, FourthWorld, robinmiller, kevinmiller

bogs
Posts: 2093
Joined: Sat Feb 25, 2017 10:45 pm

Re: SQLite Images and Standalone Issues

Post by bogs » Thu Mar 01, 2018 11:19 pm

Well, I suppose I could, but maybe this will give you an idea of why I would pass on that one :mrgreen: These are the counts of just the movies as of 2 years ago
Image
Then we have books, music, and house hold inventory. I know the movies has grown to about 2x what you see here, my wife is a very prolific movie purchaser. She also likes many varieties of music. As bad as she is with those two items, I am nearly as bad with books. We're doomed I'm sure :twisted:

However, I *think* I have almost solved the riddle of the covers, thanks to some tips and clues from everyone else who contributed, I'm like one to two more days out from solving it :D

Max popped in while I was still composing, I'll certainly read through the posting you linked Max, thank you!
Image

bogs
Posts: 2093
Joined: Sat Feb 25, 2017 10:45 pm

Re: SQLite Images and Standalone Issues

Post by bogs » Thu Mar 01, 2018 11:49 pm

I'm sorry to say Max that, while your code looked interesting, it did not actually solve the problem :|

I am curious though, about this -

Code: Select all

put the rect of image 1 into tempR   
put base64decode(tRecord) into temp
put temp into image 1         
set the rect of image 1 to tempR
Putting the rect of image 1 into tmpR, doesn't that just set it to the size image 1 is before the picture enters it?
Also, "put temp into image 1" for some reason there are a variety of things that do not seem to work that theoretically *should* work. This was one of those.

Along the way, I have tried putting the text of tImage into image "imgCover" (didn't work), tried putting the variable directly into the image container, didn't work, tried export the variable to a file which I then intended to set the image container to, didn't work.

The only method I've gotten to work with any reliability is set the imageData of the image container to the variable, although I'm not sure why exactly. Just plain weird the luck I have I guess heh.
Image

Saman Sjr.
Posts: 41
Joined: Sat Nov 30, 2013 6:40 am

Re: SQLite Images and Standalone Issues

Post by Saman Sjr. » Fri Mar 02, 2018 3:32 am

how about :
set the text of image "image1" to url ("binfile:"&tFilename)

bogs
Posts: 2093
Joined: Sat Feb 25, 2017 10:45 pm

Re: SQLite Images and Standalone Issues

Post by bogs » Fri Mar 02, 2018 4:27 am

Hey Saman, that was my thinking at one point where I was experimenting with 'export', which is what I alluded to here-
bogs wrote:
Thu Mar 01, 2018 11:49 pm
tried export the variable to a file which I then intended to set the image container to, didn't work.
When I try this tomorrow (because I really don't see why it shouldn't work heh), maybe I will have better mojo going :D
Image

MaxV
Posts: 1535
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: SQLite Images and Standalone Issues

Post by MaxV » Mon Mar 05, 2018 6:04 pm

bogs wrote:
Thu Mar 01, 2018 11:49 pm
I'm sorry to say Max that, while your code looked interesting, it did not actually solve the problem :|

I am curious though, about this -

Code: Select all

put the rect of image 1 into tempR   
put base64decode(tRecord) into temp
put temp into image 1         
set the rect of image 1 to tempR
Putting the rect of image 1 into tmpR, doesn't that just set it to the size image 1 is before the picture enters it?
Also, "put temp into image 1" for some reason there are a variety of things that do not seem to work that theoretically *should* work. This was one of those.
...
The code commented:

Code: Select all

   answer file "Scegli il nuovo file per l'immagine da salvare:"
   if the result is not "Cancel" then
      put it into tpath #save the path
      lock screen #if you don't lock, user will shocked
      put the rect of image "logo" into temp # store the the rect that you like for the image
      put url ("binfile:" & tpath) into image 1 #image is stored into livecode image object, this change the rect to the real image size
      set the rect of image 1 to temp # this way we resize image to our preferred rect without problem. Livecode handle resizing perfectly
      unlock screen #if you don't unlock , program seem frozen
   end if   
If you want to upload image into a database, use the TEXT property.
to store image data:

Code: Select all

put base64encode(the text of image 1) into temp
put "UPDATE myImages SET imagedata='"& temp &"' WHERE name='myLovedImage' ;" into tSQL
revExecuteSQL connID,tSQL
now to retrieve the image:

Code: Select all

put "SELECT imageData FROM myImages WHERE nome='myLovedImage' "   into tSQL
put revDataFromQuery(tab,return,connID,tSQL) into tRecord
lock screen
put the rect of image 1 into tempR   
put base64decode(tRecord) into temp
put temp into image 1         
set the rect of image 1 to tempR
unlock screen
If you don't believe me, try this test:

Code: Select all

 put base64encode(the text of image 1) into  tRecord
   lock screen
   set the width of image 1 to random(280)
   set the height of image 1 to random(280)
   put the rect of image 1 into tempR   
   put base64decode(tRecord) into temp
   put temp into image 1         
   set the rect of image 1 to tempR
   unlock screen
Livecode Wiki: http://livecode.wikia.com
My blog: http://livecodeitalia.blogspot.it
To post code use this: http://tinyurl.com/ogp6d5w

bogs
Posts: 2093
Joined: Sat Feb 25, 2017 10:45 pm

Re: SQLite Images and Standalone Issues

Post by bogs » Mon Mar 05, 2018 8:29 pm

Heh, I never said I don't believe you, I've seen too much of what you do to doubt it a lot. I will test further when I have time tonight, lots of appointments today unfortunately :?
Image

bogs
Posts: 2093
Joined: Sat Feb 25, 2017 10:45 pm

Re: SQLite Images and Standalone Issues

Post by bogs » Tue Mar 06, 2018 4:35 am

After playing with Max's code, specially that last snippet, I am really confused now, I managed to completely clear out 3 entries in the db before I turned on the debugger to figure out what was going on heh. Good thing I'm playing with an older version of it, eh? :P

The last snippet does indeed return all different sizes of a picture, but that is a regular picture file from my drive, not one processed from the previous language and returned from the db. Ah well, guess I'll crack open the previous language and write all the pics out, then attempt to use Lc to write them all back in again. Either that, or I'll wind up writing the whole db out to file, then write it all back into a new sqlite file.

Thank you to all that chipped in, it was a lot of fun and good mental exercise :D I sure wish I understood Ken Ray's explanation of imageData better, it is a good read for anyone venturing into this kind of unknown. I may have to re-read it a few times to fill in some missing pieces.
Image

bogs
Posts: 2093
Joined: Sat Feb 25, 2017 10:45 pm

Re: SQLite Images and Standalone Issues

Post by bogs » Sun Mar 11, 2018 7:22 pm

So, the upshot of all my adventures was that I finally caved in, and saved out the pictures in the DB using the previous language they were saved in with, which took me the better part of this morning :shock:

The biggest problem I ran across was that I couldn't automate the process due to poor design decisions I made when I initially wrote the program. Live and learn :)

I'll be starting a new thread to discuss some of the advice and tips I was given in this thread, since they are really pertaining to image handling in Lc and not so much to this problem.
Image

sphere
Posts: 662
Joined: Sat Sep 27, 2014 10:32 am
Location: the Netherlands

Re: SQLite Images and Standalone Issues

Post by sphere » Tue Mar 13, 2018 12:46 pm

Hi Bogs,

don't know if this helps. But i was also busy putting images into sqlite on Android and i found it hell to get it done, but finally got it to work. I thought it could give speed advantage and less data consuming to let the app update the internal database instead of getting all data and picture every time from the server. And although most advice is to not put images in a database, i wanted to see how it works. But i guess it is still easier to read pictures as a file from the server. There is almost no speed advantage, only some data. Well anyway long story short.
I retrieve data/blob via PHP from my server then with that update the existing database, that is, with updating sqlite i had some troubles, so now i delete all tables and data when an update is available and recreate all and put all data in it again. (sqlite is not exactly the same in working as mysql/mariadb i noticed)
Also retrieving other data and BLOB at the same time gave strange issues and problems. So i retrieve them apart. Any data except BLOB and then BLOB.

Code: Select all

 put "CREATE TABLE products (id INT PRIMARY KEY, category TEXT, product TEXT, blablabla TEXT, artnr TEXT, price DECIMAL(5,2), tax DECIMAL(5,2), blabla DECIMAL(5,2), artpic BLOB)" into tSQL
   revExecuteSQL gLocalDbID, tSQL
then after retrieving all "normal" data i get the BLOB info:

Code: Select all

post varSendData to URL "https://mywebsite.nl/a folde/getmyblobs.php"
      put it into tPic
      if char 1 of line 1 of tPic is space then delete char 1 of line 1 of tPic --somehow my 1st character is always a space when getting info via PHP into LC, this is not the case directly in a browser, a bugreport for this was created
      put base64decode(tPic) into img"invisiblephoto" of card"whatever"
      wait 50 milliseconds with messages --why so hasty
      export image "invisiblephoto" of card"whatever" to tGT as PNG
      put base64encode(tGT) into tPrdFt
       put "UPDATE products SET artpic=:1 WHERE id=" & tId & " ;" into tSQL
       revExecuteSQL gLocalDbID,tSQL,"*btPrdFt"
This is in a repeat loop to the number of id's in the database.
This was finally how i could get them in the sqlite db which worked, i also checked the link from MaxV which helped a it too.
Just putting the data directly into the DB after retrieving seemed not to work ok, so that's why i have it first in an invisible image first and export it again.

Now after this getting it into a DG was also not straight forward, i needed to put it again in an invisible image:

Code: Select all

put revQueryDatabase(gLocalDbID, "SELECT artpic FROM table WHERE id=" & tId & " ;") into tRecordSet
      put revDatabaseColumnNamed(tRecordSet, "artpic", tImage) into tError #tImage contains data, tError contains errors
      put base64decode(tImage) into img"invisibleimage"
      
      export img"invisibleimage" to URL ("binfile:" & specialFolderPath("Documents")  & "/prdft" & tLineCounter & ".png") as PNG ---ok
   put specialFolderPath("Documents") & "/prdft" & tLineCounter & ".png" into tLocStat ---ok
    
      put  tLocStat into theDataA[tLineCounter]["articleimage"]
Well the rest you know how to get it in the DG

Of course there will be better ways on how to do this, but this was the way it worked for me after a lot of trial and error.
Please, stop quoting things my brother did not say. Thank you. Hans Zweistein.

bogs
Posts: 2093
Joined: Sat Feb 25, 2017 10:45 pm

Re: SQLite Images and Standalone Issues

Post by bogs » Tue Mar 13, 2018 2:43 pm

That is very clever sphere!

I did learn that pulling the regular (text, integer) data was the quickest easiest way to pop the dg, The pictures I pulled on a 'as needed' basis when a line was selected from the grid, since that is by far the slowest part of the whole operation.

There were three obstacles from pulling these images, though. They were written into the db in a way similar to that of using imageData encoded to base64 would do would do in Lc, and of course they are all different sizes, and lastly I think RB put some of it's own jim-crackery into the mix since in several of the binary extractions it lead off with 'Rbpc' and not just standard binary data. I read about a similar issue in Lc back somewhere around the late 2.x to 3 series, but forgot where exactly.

What I mainly found out through this experiment is that they needed to be pulled back out and put into the image through imageData, unfortunately as Sparkout & Jacque pointed out though, if the target image isn't dead on right, you get the garbage look I wound up getting.

I am sure that there is a way to work around this, probably if I had understood that article on Ken Ray's site I might have gotten there, since that looks very much like what the plugin I had been using was written like.

It would have been great to be able to use the text of the image, instead of imageData, since that appears to need no help in resizing the image to fit into an image container, but attempting that on this data produced no image.

Setting up images without putting them on the form also did not help, if anything, the tmpImages were even more distorted than the set size ones.

Ultimately, I copped out and went back to Rb to pull them. Sticking them back in will be done with Lc, I'll make sure the resizing code I write for it will be more perfect this time heh, and while using the text of the images is very much easier in Lc for playing with them, I will probably still resort to using imageData and encoding that to stick them back in. After of course I wrap my head around imageData better Image
Image

Post Reply

Return to “Databases”