Linking pictures from SQLExpress to Filemaker 9

Hi,

I have my master database on SQLExpress and I am using filemaker Advance 9 for different reporting stuff. I have linked different table from SQL and can do what I want but can't manage photos. My person table have all people info and have person ID, photoid.

MY photo table have photoid, filename, date, status and photo. The photo field is binary in SQL and don't know how to link with FileMaker and display photos.

Thanks
za_pakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lesouefCommented:
yes, this is a problem, fm won't use the longblobs.
All you can do is store the images outside the database, store the file path in the SQL table, and have filemaker to use that to show it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
za_pakAuthor Commented:
There are two things to do this;

duplication of disk space because all images are already available in database
least security of images because they are available out of database

Any way would you like to explain how to do this way I will try.

Thanks
0
lesouefCommented:
true, the volume must accessible to users.
disk drive duplication: yes, at least to start with.

how I would do?
from yr existing solution, I would export each image as a file, and keep the path/filename in a SQL field.
from filemaker, I would change the file/path to a filemaker compatible one to have filemaker to display it, using the web viewer for instance, or import (link only) it in a container, but that is slow and handles less image formats.
my other approach would be to use a tool which can read binaries from SQL, Servoy for instance.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

za_pakAuthor Commented:
Thanks for your guidance, What tool is available which can read binaries from SQL?
0
lesouefCommented:
Servoy, but this a complete IDE to generate java apps which interfaces with several SQL brands. there are probably others but I never looked for them
0
za_pakAuthor Commented:
I don't have idea about this... sorry

Can you tell me the steps which you said in the first post than I can try that........... when I save externally how to identify which image belong to which because I have about 1200 users.

Thanks
0
lesouefCommented:
You must save them and use a name which is relevant of the user_ID to be able to calculate it in filemaker.
Say for instance you would save the image on a web site, you should end up with a path like:
http://www.mysite.com/users/images/875421.jpg where 987421 would be the user_id.
this way you can display the image in filemaker using the web viewer with a calculated path:
"http://www.mysite.com/users/images/" & user_id & ".jpg"
Same method may be used for a local path too if used on a LAN.
0
za_pakAuthor Commented:

Please see the attached. I am doing on LAN not on web. I have crated one extra table which can be used to import all pictures in. There is one ID filed and I can use that ID field to link with parent folder. So according to ID it will show the picture.

But when I am going to import pictures, File> Import records> folder

and match the fields, I don't have any option in the source to match the ID, if I match with file name it don't work because filename have extension.

Is any other way to import all pictures? or I am doing something wrong?

Thanks


ScreenShot029.JPG
0
lesouefCommented:
I don't why the extension would be skept...
But first correct the import field match which is wonrg according to your screen capture: image and filename are inverted
0
za_pakAuthor Commented:

That is fine, the image is only for reference. I will match the fields when I will import it but the question is how to match with ID?
0
lesouefCommented:
you can't. to be able to do it, you should export the images from SQL using a name which is relevant of the sql ID, than get this ID to act as the filemaker ID by importing it in filemaker. DO you have any other way to make relate the persons and the images?
0
za_pakAuthor Commented:
Hi,

I have managed to sort this out, just want to share with you.

Exported all complete photo table from SQL to FM.

Rename all the filenames as I have in external images.

Imported the images as update record, matched the file name with image name.

Now it is working fine.

Thanks for all your inputs.

Regards

Z.Ahmad
0
lesouefCommented:
but you had to rename the files manually I guess? hope not too many!
0
za_pakAuthor Commented:
Well, my file names were same except extension. You can use find and replace, like; .bmp to .jpg.

and its work. I had about 1200 file names :)


0
lesouefCommented:
or rename the files with a shell command. ok, good. how do you display images now? container field or web viewer?
0
za_pakAuthor Commented:
I am using container field, Thanks for your all input and time. I believe, as you spent your value-able time to help me you deserve the points on this question.

But to do that I need to accept the solution than you get the points. Can you tell me how to do that? or you can create one same solution and I will award the points. Thanks


0
lesouefCommented:
as the question originator, you should have an "accept" button somewhere at the bottom (there are 2 interfaces, I use the advanced one, and I know it is a bit different).

about the container, if you've left your images external (linked), try the web viewer as this displays all formats supported by IE, and that's more than filemaker, for instance gif's will be animated, etc... can also display flash files,...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.