Saving img to mySQL
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- Posts: 641
- Joined: Tue Jul 29, 2014 12:52 am
- Location: Brazil
Saving img to mySQL
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
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
Simon
________________________________________
To ";" or not to ";" that is the question
________________________________________
To ";" or not to ";" that is the question
Re: Saving img to mySQL
Hi Simon,
what did you script so far?
Best
Klaus
what did you script so far?
Best
Klaus
-
- Posts: 641
- Joined: Tue Jul 29, 2014 12:52 am
- Location: Brazil
Re: Saving img to mySQL
Hi Klaus, many thanks for spending time to look into it, here it goes:
I have a stack with an Image field named "lcLogo"
When the code is executed I get the following error
Many thanks in advance for your customary valuable hints/lessons
Regards
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
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 useYou 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
Many thanks in advance for your customary valuable hints/lessons
Regards
Simon
________________________________________
To ";" or not to ";" that is the question
________________________________________
To ";" or not to ";" that is the question
Re: Saving img to mySQL
Hi Simon
I think you need to use placeholders to work with binary data e.g.
or perhaps
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
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"
Code: Select all
revExecuteSQL gConnectionID, "INSERT INTO images('TimeStamp', 'imagename', 'image') VALUES (:1, :2, :3)", "tCurrentTimestamp", "tImageName", "*btData"
Disclaimer: this is untested
Elanor
Elanor Buchanan
Software Developer
LiveCode
Software Developer
LiveCode
-
- Posts: 641
- Joined: Tue Jul 29, 2014 12:52 am
- Location: Brazil
Re: Saving img to mySQL
Hi Elanor, many thanks for your hints. I have tested both options with no luck
throws
throws
Code: Select all
revExecuteSQL gConnectionID, "INSERT INTO images('TimeStamp', 'imagename', 'image') VALUES (CURRENT_TIMESTAMP, :1, :2)", "tImageName", "*btData"
whileYou 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
Code: Select all
revExecuteSQL gConnectionID, "INSERT INTO images('TimeStamp', 'imagename', 'image') VALUES (:1, :2, :3)", "tCurrentTimestamp", "tImageName", "*btData"
should be so simple...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
Simon
________________________________________
To ";" or not to ";" that is the question
________________________________________
To ";" or not to ";" that is the question
Re: Saving img to mySQL
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
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
Elanor Buchanan
Software Developer
LiveCode
Software Developer
LiveCode
-
- Posts: 641
- Joined: Tue Jul 29, 2014 12:52 am
- Location: Brazil
Re: Saving img to mySQL
Dear Elanor, I really appreciate the time you spend helping me.
The good news: using
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
The good news: using
Code: Select all
revExecuteSQL gConnectionID, "INSERT INTO images(TimeStamp, imagename, image) VALUES (CURRENT_TIMESTAMP, :1, :2)", "tImageName", "*btData"
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
What am I doing so wrong? Is what I'm trying to achieve doable with LC?Unable to expand "images-image.bin". It is in an unsupported format.
Simon
________________________________________
To ";" or not to ";" that is the question
________________________________________
To ";" or not to ";" that is the question
Re: Saving img to mySQL
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
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
Elanor Buchanan
Software Developer
LiveCode
Software Developer
LiveCode
-
- Posts: 641
- Joined: Tue Jul 29, 2014 12:52 am
- Location: Brazil
Re: Saving img to mySQL
Hi Elanor as stated in my original post
Maybe I'm missing something...
Thanks once again for your time
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.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.
Maybe I'm missing something...
Thanks once again for your time
Simon
________________________________________
To ";" or not to ";" that is the question
________________________________________
To ";" or not to ";" that is the question
-
- Posts: 641
- Joined: Tue Jul 29, 2014 12:52 am
- Location: Brazil
Re: Saving img to mySQL
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
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
Simon
________________________________________
To ";" or not to ";" that is the question
________________________________________
To ";" or not to ";" that is the question
Re: Saving img to mySQL
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
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
-
- Posts: 641
- Joined: Tue Jul 29, 2014 12:52 am
- Location: Brazil
Re: Saving img to mySQL
Hi Terry, many thanks for your suggestion. I've tried to adapt your code to my table as follows:
and I get the following error
How have you defined your columns type?
Best
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
I have also tried replacing "*tblob" with "*tbblob" which I believe should be the right syntax but I still get the same 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
How have you defined your columns type?
Best
Simon
________________________________________
To ";" or not to ";" that is the question
________________________________________
To ";" or not to ";" that is the question
Re: Saving img to mySQL
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
If we're treading on thin ice, well you might as well dance.
Re: Saving img to mySQL
You have tried replacing "*tblob" with "*tbblob" but you really need "*btblob" - although I don't know whether a simple typo is the problem.
-
- Posts: 641
- Joined: Tue Jul 29, 2014 12:52 am
- Location: Brazil
Re: Saving img to mySQL
Thanks @SparkOut, nice catch and you are right, fixing the typo didn't fix the error ...
Regards
Regards
Simon
________________________________________
To ";" or not to ";" that is the question
________________________________________
To ";" or not to ";" that is the question