Author Topic: Retrieving Binary Image Data from SQL  (Read 3071 times)

PaulSamuelson

  • Newbie
  • *
  • Posts: 40
Retrieving Binary Image Data from SQL
« on: January 12, 2015, 08:30:40 am »
I have images (both JPG and PNG) stored on a FileMaker 13 Server. I would like to use SQL to retrieve them, preferably as binary data in a binary buffer. If I had to, I could save them temporarily and read the files, but I would prefer to save that step.

I believe the format of my SQL is correct:
cSQL="SELECT GetAs(bgImage, 'JPEG'), GetAs(fgImage, 'PNGf') FROM GSImages"; where gsBgID = 1"

RS101.Open(cSQL,cConnFMS,3,3)

How do I get the results of the query into a binary buffer or save them as a file?


Thanks,

Paul Samuelson

JTaylor

  • Pundit
  • *****
  • Posts: 1015
    • Data & Stuff Inc.
Re: Retrieving Binary Image Data from SQL
« Reply #1 on: January 12, 2015, 09:56:44 am »
Not sure what you are trying to accomplish so not sure if this will be helpful but my need is to save the image to a file from a MySQL database and the following approach works very well.   If your need is to manipulate the data in the buffer then this may not be helpful but for what it is worth....

Code: Winbatch
While RS101.EOF == @FALSE

  ;read back out as binary stream
  S = CreateObject("ADODB.Stream")
  S.Type    = 1
  S.Open()
  S.Write( RS101.Collect("JPEG") )
  S.SaveToFile(file_name,2)
  S.close()
  S=0

  RS101.MoveNext()

EndWhile
RS101.Close()
RS101=0
 


Jim

td

  • Tech Support
  • *****
  • Posts: 3019
    • WinBatch
Re: Retrieving Binary Image Data from SQL
« Reply #2 on: January 12, 2015, 11:10:13 am »
You could check the variant type of the return value from RS101.Collect with the ObjectTypeGet function.  If it happens to be of type 'Array|I1' or 'Array|u1',  you might be able to use the  BinaryAllocArray to convert the return directly to a binary buffer.

Note that you will need to assign the return value from RS101.Collect to a variable in order to use ObjectTypeGet.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

PaulSamuelson

  • Newbie
  • *
  • Posts: 40
Re: Retrieving Binary Image Data from SQL
« Reply #3 on: January 12, 2015, 02:06:48 pm »
Ok, the following works to write the image data to a stream and save the stream to JPG and PNG files (Thanks, Jim):

cSQL="SELECT GetAs(bgImage, 'JPEG'), GetAs(fgImage, 'PNGf') FROM GSBackgrounds where gsBgID = 1"
RS101.Open(cSQL,cConnFMS,3,3)

;read back out as binary stream
Stream = CreateObject("ADODB.Stream")
Stream.Type = 1

;write bg (jpg) to file
Stream.Open()
Stream.Write( RS101.Collect("bgImage") )
Stream.SaveToFile("c:\bg1.jpg",2)
Stream.close()

;write fg (png) to file
Stream.Open()
Stream.Write( RS101.Collect("fgImage") )
Stream.SaveToFile("c:\fg1.png",2)
Stream.close()

Stream=0

RS101.Close()



And after Tony's tips, this will read the stream directly to a buffer:

Stream.Open()
Stream.Write( RS101.Collect("bgImage") )
Stream.Position = 0
x = Stream.Read()
bJPG = BinaryAllocArray(x)

;confirmed to work, by writing the buffer to a file. Resulting file is the same as the Stream.SaveToFile())
BinaryWrite(bJPG, "c:\jpg bb.jpg")


Thank you,

Paul