Saving img to mySQL

[simon.schvartzman]

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

[Klaus]

Hi Simon,

what did you script so far?


Best

Klaus

[simon.schvartzman]

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

[elanorb]

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

[simon.schvartzman]

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

[elanorb]

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

[simon.schvartzman]

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?

[elanorb]

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

[simon.schvartzman]

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

[simon.schvartzman]

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

[terryho]

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

[simon.schvartzman]

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

[dalkin]

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

[SparkOut]

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

[simon.schvartzman]

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

Regards