Retrieving Binary Image Data from SQL

[PaulSamuelson]

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]

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) Select Expand


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]

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.

[PaulSamuelson]

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