Question

Extracting photos from Microsoft SQL Database to network drive

  • 4 December 2013
  • 6 replies
  • 7 views

Badge
Hi everyone,

 

 

I have photos store in a Microsoft SQL Spatial Database which I am trying to extract out on our job drive (i.e. a normal network drive).  I've had a search of previous responses and it seems that the AttributeFileWriter is the relevant transformer, however I can't get it to work!

 

 

The photo is stored in an Attribute field called P_Picture.  I added the relevant reader and connected the AttributeFileWriter with the following parameters:

 

 

Source: P_Picture

 

Target Filename: P_Picture

 

If Target Exists: Overwrite File

 

Target File Character Encoding: System Default (fme-system)

 

 

When I run it, I get an error:

 

"AttributeFileWriter: This FME edition does not recognise the 'o' function.  Please ensure that the current platform supports this function, the function name is spelled correctly, and that you have installed all the required plugins"

 

 

I'm am running:

 

Win 7, 64-bit,

 

FME 32-bit, SP4 ESRI Edition (should be able to read db format in)

 

 

Any pointers would be greatly appreciated!

 

 

 

Thanks, Amit

6 replies

Userlevel 4
Hi,

 

 

I'm not sure what the error means, but I suspect that you shouldn't be using any character encodings for a binary file (photo).

 

 

Try setting the target file character encoding to fme-binary and try again.

 

 

David
Badge +3
I tried that.

 

 

Tried it while avoiding to have to built a script.

 

Found one in VB script from a professional Photogrpher who uses mdb's for photostorage.

 

 

Photo's were stored blob's in mdb's.

 

I failed to read or write those using FME (2012).

 

I looked up my last attempt, wich was intended to built an .emf symbol database). Last attempt there tried ole_object format in an mdb writer.

 

It gave me the same error message u got.

 

 

And here is also a read/write vbscript form the ms site:

 

http://support.microsoft.com/kb/103257

 

Badge
Thank you both for your replies, I nanaged to get it to work.  The settings are not complex, however the help file is quite sparse (i'll ask Safe to add a bit more detail!).

 

 

To get it to work I first used a StringConcatenator to control the filename and where to write the file.  Inside the tranformer I had:

 

 

Constant: global addres, e.g. \\\\global\\europe\\jobs\\myjob\\ (or you could just have c:\\myjob)

 

Attribute Value: your photoname, could be an ID field

 

Constant: .jpg (or file extension)

 

 

For me this created:

 

 

\\\\global\\europe\\jobs\\myjob\\@VALUE(ID).jpg

 

 

In the AttributeFileWriter I had:

 

 

Source Attribute: Blob field, e.g. Picture

 

Target filename: Concatenated result from above

 

If Target Exists: Overwrite

 

Character Encoding: Binary

 

 

I don't think you need to connect a writer, but I just closed on a Logger.

 

 

Hope that helps others!

 

 

Amit

 

 

 

 

 

Userlevel 4
Good to hear. Thanks for sharing your solution, Amit :-)

 

 

David
Badge +3
Using the filereader i manage to read the (B)LOB and the transformer does write something. To a file.

 

But i cant open or see it.

 

 

data looks like

 

Marker Symbols ID Name Category Object Tags 21 Fietsenkluis IMGEO 2.1   picture;emf 22 NS     picture;emf 23 volkstuin IMGEO 2.1   picture;emf 24 Bewoning IMGEO 2.1   picture;emf 28 geleidewerk IMGEO 2,1   picture;emf 29 bushalte IMGEO 2,1   picture;emf 31 Marker Symbol     picture 32 New Marker Symbol     picture

 

 

In the object filed resides the BLOB, beware!

 

 

Maybe anyone can tell me how to retrieve such (and write it?)

 

Using FME of course.

 

This is a .mdb, btw.

 

 

Btw,i tried to emulate your error using the filewriter, but ould not.

 

 

 

Userlevel 3
Badge +13
Raster replacer + extractor

Reply