LiveCode Forums.

Posted: **Thu Oct 07, 2021 5:29 pm**

Hi all, this is a very basic subject but since it is taking me too long to make it work I decided to ask for help.

I want to export a snapshot of the screen (as an image) to mySQL DB in such way that once downloaded from the DB "as is" (by any non LC client) it could be opened by any standard image viewer.

I guess it involves saving to a BLOB field and/or using the *b type of variables but I haven't being able to make it work and therefore will really appreciate any help (hopefully a simple example) with it.

Many thanks in advance.

Regards

Posted: **Fri Oct 08, 2021 4:33 pm**

Hi Simon,

what did you script so far?

Best

Klaus

Posted: **Sat Oct 09, 2021 4:00 pm**

Hi Klaus, many thanks for spending time to look into it, here it goes:

I have a stack with an Image field named "lcLogo"

Code: Select all

command SaveTomySQL
   
   if gConnectionID is not a number then
      exit to top
   end if
   
   export snapshot from image "lcLogo" with metadata theMetadataArray to tData as JPEG
   
   put formatDate() into tImageName -- use current datetime as picture name
   put specialFolderPath("documents") & "/" & tImageName & ".jpg" into tFileName
   -- save image to local storage
   put tData into url ("binfile:" &  tFileName)
   -- insert record into mySQL
   revExecuteSQL gConnectionID, "INSERT INTO `images`(`TimeStamp`, `imagename`, `image`) VALUES (CURRENT_TIMESTAMP, '" & tImageName & "',*btFileName)"
   put the result
   revCloseDatabase gConnectionID
end SaveTomySQL

function formatDate
   -- Format Date to be used as the file name
   set the numberformat to "00"
   put the date into tDate
   convert tDate to dateitems
   put item 1 of tDate into Year
   put (item 2 of tDate + 0) into Month --- formated with two digits
   put (item 3 of tDate + 0) into Day --- formated with two digits
   
   put the long time into tTime
   convert tTime to dateitems
   put (item 4 of tTime + 0) into Horas -- formated with two digits
   put (item 5 of tTime + 0) into Minutos -- formated with two digits
   put (item 6 of tTime + 0) into Segundos -- formated with two digits
   
   -- format as YYYYMMDDHHMMSS
   put Year & Month & Day & Horas & Minutos & Segundos into formatedDate   

end formatDate

When the code is executed I get the following error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*btFileName)' at line 1

I have played with all the quotes, double quotes and ampersands combinations I can imagine and still can't figure out the right syntax to use

Many thanks in advance for your customary valuable hints/lessons

Regards

Posted: **Mon Oct 11, 2021 4:04 pm**

Hi Simon

I think you need to use placeholders to work with binary data e.g.

Code: Select all

revExecuteSQL gConnectionID, "INSERT INTO images('TimeStamp', 'imagename', 'image') VALUES (CURRENT_TIMESTAMP, :1, :2)", "tImageName", "*btData"

or perhaps

Code: Select all

revExecuteSQL gConnectionID, "INSERT INTO images('TimeStamp', 'imagename', 'image') VALUES (:1, :2, :3)", "tCurrentTimestamp", "tImageName", "*btData"

If you are saving the binary data of the image I also think you should be using tData, not tFileName, as that is the image data rather than the path.

Disclaimer: this is untested

Elanor

Posted: **Mon Oct 11, 2021 4:31 pm**

Hi Elanor, many thanks for your hints. I have tested both options with no luck

Code: Select all

revExecuteSQL gConnectionID, "INSERT INTO images('TimeStamp', 'imagename', 'image') VALUES (CURRENT_TIMESTAMP, :1, :2)", "tImageName", "*btData"

throws

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''TimeStamp', 'imagename', 'image') VALUES (CURRENT_TIMESTAMP, "20211011122750", ' at line 1

while

Code: Select all

revExecuteSQL gConnectionID, "INSERT INTO images('TimeStamp', 'imagename', 'image') VALUES (:1, :2, :3)", "tCurrentTimestamp", "tImageName", "*btData"

throws

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''TimeStamp', 'imagename', 'image') VALUES ("20211011122856", "ˇÿˇ‡\0JFIF\0' at line 1

should be so simple...

Posted: **Mon Oct 11, 2021 5:16 pm**

Hi Simon,

The column names don't need single quotes, sorry I should have noticed that.

https://www.w3schools.com/sql/sql_insert.asp

Also double check that the variables you are using have the expected values in them.
Elanor

Posted: **Mon Oct 11, 2021 5:39 pm**

Dear Elanor, I really appreciate the time you spend helping me.

The good news: using

Code: Select all

  revExecuteSQL gConnectionID, "INSERT INTO images(TimeStamp, imagename, image) VALUES (CURRENT_TIMESTAMP, :1, :2)", "tImageName", "*btData"

there is no error and a new record is added to the table with the correct data in the first two columns and a binary on the third column (the one that was set to BLOB in order to receive the Binary file)

The bad news: when I download the BLOB to my mac I get a file named

images-image.bin

and when I try to open it I get

Unable to expand "images-image.bin". It is in an unsupported format.

What am I doing so wrong? Is what I'm trying to achieve doable with LC?

Posted: **Tue Oct 12, 2021 9:59 am**

Hi Simon,

I am not totally clear on what you are trying to achieve, how are you downloading the BLOB? Are you doing it in LiveCode and trying to display it in an image control?

If you are doing it some other way I would think you would need to create a file of the same type as the original image, PNG or JPG etc.

Kind regards

Elanor

Posted: **Tue Oct 12, 2021 10:18 am**

Hi Elanor as stated in my original post

I want to export a snapshot of the screen (as an image) to mySQL DB in such way that once downloaded from the DB "as is" (by any non LC client) it could be opened by any standard image viewer.

In other words when I click on the BLOB column of the mySQL server being used, it automatically downloads its contents. I understand that it should be possible to store a binary file on the mySQL BLoB column such that when downloaded (without any further handling) it will be exactly equal to the original file. Hope I clarified my goal.

Maybe I'm missing something...

Thanks once again for your time

Posted: **Tue Oct 12, 2021 2:01 pm**

For the records.

After reading more about mySQL column types ( I should have done this sooner...) my conclusion is that is not possible to achieve what I was trying to.

The way to go is to store the image as a file in the server and have the link to the file stored in the DB.

Many thanks to all of you who helped and sorry for wasting your time.

Best

Posted: **Wed Oct 13, 2021 2:34 pm**

Hi

The following statement will work
put 1 into tgen
put 123 into trefno
put "c:\temp\savefile.png" into mblobfile
put url("binfile:" & mblobfile) into tblob
Put "Insert into blobtable (Guid, refno, saveblob) values (:1, :2, :3)" into tsql
revExecuteSQL gdbid, tsql, "tgen", "trefno", "*tblob"

Regards

Terry Ho

Posted: **Wed Oct 13, 2021 10:27 pm**

Hi Terry, many thanks for your suggestion. I've tried to adapt your code to my table as follows:

Code: Select all

if gConnectionID is not a number then
      exit to top
   end if
   
   export snapshot from image "lcLogo" with metadata theMetadataArray to tData as JPEG
   
   put formatDate() into tImageName -- use current datetime as picture name
   put specialFolderPath("documents") & "/" & tImageName & ".jpg" into tFileName
   -- save image to local storage
   put tData into url ("binfile:" &  tFileName)
   
   put currentTime into tgen
   put tImageName into trefno
   put url("binfile:" & tFileName) into tblob
   Put "Insert into images (TimeStamp, imagename, image) values (:1, :2, :3)" into tsql
   revExecuteSQL gConnectionID, tsql, "tgen", "trefno", "*tblob"
   put the result
   revCloseDatabase gConnectionID

and I get the following error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

I have also tried replacing "*tblob" with "*tbblob" which I believe should be the right syntax but I still get the same error.

How have you defined your columns type?

Best

Posted: **Wed Oct 13, 2021 11:59 pm**

You will probably find that one of your columns is the wrong format. There's a good description here: https://dev.mysql.com/doc/refman/8.0/en/blob.html

Posted: **Thu Oct 14, 2021 7:21 am**

You have tried replacing "*tblob" with "*tbblob" but you really need "*btblob" - although I don't know whether a simple typo is the problem.

Posted: **Thu Oct 14, 2021 10:11 am**

Thanks @SparkOut, nice catch and you are right, fixing the typo didn't fix the error ...

Regards

LiveCode Forums.

Saving img to mySQL

Saving img to mySQL

Re: Saving img to mySQL

Re: Saving img to mySQL

Re: Saving img to mySQL

Re: Saving img to mySQL

Re: Saving img to mySQL

Re: Saving img to mySQL

Re: Saving img to mySQL

Re: Saving img to mySQL

Re: Saving img to mySQL

Re: Saving img to mySQL

Re: Saving img to mySQL

Re: Saving img to mySQL

Re: Saving img to mySQL

Re: Saving img to mySQL