Advice on storeing images

[Jumtorus]

I have an existing FoxPro 6.0 database with the UI created there as well. Problem is that they now want to insert images into the system. Example is that they want to now select and save images via the UI of the inventory item and be able to use that pic when printing out orders etc.

Question:
1. How do I store images to the database? I believe that it should be a type (memo(binary)).
2. Not sure which is better, should the field store the picture or point to the location of the pic.
3. Are there any suggestion as to how do this entry on a form to accomplish this?

Any links to this topic would be appreciated. I have never dealt with images and databases before, any help or suggestions would be of great value to me.

 

[Koen Piller]

Hi,

I believe in VFP 6 the best option is to store only the reference to the picture (fully qualified name and extension e.g. C:\myuApplication\MyPicture.bmp ) into a text field.

Version 9 has the possibility to store the image to a BLOB field, with a remark that tables containing BLOB fields can easily grow over the 2 GG limit.

Jockey (2)

 

[Jumtorus]

Thanks Jockey2. I had a feeling that a reference to the location would be best.

Is the memo(binary) the best type to set up on the table?
And I am thinking of using some type of browser to locate the location on the path for the pic that they choose.
Or is there a better or a more simplistic way for the user to select the pic's location?

 

[Jumtorus]

never mind on that last post

I figured it out
used a command button and an editbox with
code:

lcPict = getpict()
if not empty(lcPict)
thisform.myeditbox.value = lcPict
endif

it stores the path for me.
Thanks for the help

 

[cbasoz]

Storing just the path&filename is what generally suggested and what I have believed as it was the best for many years. However in recent years I saw that it was not the best option. Now I suggest:
1) Use SQL server as a backend, preferably SQL 2008. Store your images in a varbinary(max) field with filestream attribute turned on if the average file size is over 1Mb (1 Mb is MS' suggestion).

2) If you would use VFP anyway, use memo binary (unfortunately with this approach, VFP6 users need to copy the file out, to be able to show it. VFP9 users have the luxury to simply set the PictureVal property to memo content).

One or the other I suggest storing the picture in a separate table (connected one-to-one).

Cetin Basoz
MS Foxpro MVP, MCP

 

[Mike Lewis]

Jumtorus,

You said you were doing this:

lcPict = getpict()
if not empty(lcPict)
thisform.myeditbox.value = lcPict
endif

That won't even come close to achieving what you set out in your original question. GETPICT() has got nothing to do with storing images in a table. It merely prompts the user for the filename and path of the image. Your code will then display the filename and path in an edit box. It won't store the image anywhere, and it definitely won't actually display the image.

Assuming you still want to do what you originally asked, the best solution is keep the actual images in their original physical files, outside the table, and to add a character field to the table that just stores the path and filename.

When you print your orders, add an OLE/Image field to your report, and point it at the field within the table that contains the filename. This is much simpler and more flexibile than trying to store the image in the database, and will avoid the bloated tables that you would otherwise get.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Mike Lewis]

Cetin,

Use SQL server as a backend, preferably SQL 2008.

We've had this discussion before, but I really must disagree with your advice. Moving an application from native VFP tables to a client-server back end is a major undertaking -- and one that could have implications for the company's entire database strategy. There might or might not be good reasons for Jumtorus's company to do that, but a simple problem of how to store images is not one of them.

The other day my wife complained that the shower was leaking. Applying your solution, we would be preparing to move house today.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

Mike,
Yes we disagree in two points:

1) Moving to SQL server. It is not as hard and scary as you made an irrelevant analogy. And most importantly you don't need to move all of your data to SQL server but you could simply store only the images in an SQL server express table. It would be much wiser to do than to store on disk.

2) Keeping the images on disk and storing just the path and filename is NOT the best solution. It is a myth. I was thinking just like you until I needed it for myself.

Cetin Basoz
MS Foxpro MVP, MCP

 

[Jumtorus]

thanks for the debate, but as it stands for this client the point is mute. They only have foxpro and to add on sql of any type just for storage of images is not an expectable cost avenue. How ever I do know sql server would be a better option.
Anyways like I stated earlier I now have the problem solved with the storage of the path.
Now to figure out the best means of recalling the image to the screen for use on the form and for printing out onto the reports and order forms.

thanks again

 

[markros]

Cetin,

Can you elaborate on 2) 2) Keeping the images on disk and storing just the path and filename is NOT the best solution. It is a myth. I was thinking just like you until I needed it for myself.

Are you using SQL Server 2008? What did you chose for your image files?

 

[cbasoz]

I have multiple cases. For those where I could use SQL server I got the best result. Where I needed to use native tables I first did it "store the path" way. As image count increased it turned to be a nightmare. Then I stored them in a table's memo binary field and it turned out to be the best option (in case need to use VFP native tables).

Cetin Basoz
MS Foxpro MVP, MCP

 

[Mike Lewis]

Jumtorus,

Now to figure out the best means of recalling the image to the screen for use on the form and for printing out onto the reports and order forms.

To get the image into a report, do the following:

1. Add a "Picture/OLE Bound" control to the report.

2. In its properties, set "Control Source Type" to "Expression or Variable Name".

3. In the Control Source box, enter the alias.fieldname of the field holding the filename.

To display the image on a form:

1. Add an Image control to the form.

2. Set its Picture property to the filename.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

I still wonder what would be a nightmare of storing paths of pictures. The only nightmare is, if pictures move. As long as this is restricted there is no nightmare. It's only data, nobody has to look at the paths.

Plus you can manage the paths in a normalized way eg by defining a base path and storing relative paths to that base you can allow the moving of a whole picture repository.

Plus: this picutre repository or repositories is not necessarily managed ba the user, you can copy pictire files there and users can still grab the pictures from wherever they want, including only temp picture files.

@Jumtorus:
To store paths, you use a normal C(254) field. In case you know the paths are short, eg pictures are always located in a folder "pics" on a mapped drive, you can also use a shorter C() field. To make sure you get long paths you couls also use a memo field for the paths, but a memo (binary) field would only be needed to store binary data, eg the pictures themselves.

The 2GB limit is serious here, as it is not only about the overall image sizes, memo fpt files tend to bloat when data is updated too.

Bye, Olaf.

 

[Mike Lewis]

Cetin,

Moving to SQL server. It is not as hard and scary as you made an irrelevant analogy.

I'm sure it's not scary, but it's not exactly trivial. If anyone tells you otherwise, I would suggest they've never done it.

Keeping the images on disk and storing just the path and filename is NOT the best solution. It is a myth. I was thinking just like you until I needed it for myself.

I'd be interested to know on what basis you say that.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

I'm sure it's not scary, but it's not exactly trivial. If anyone tells you otherwise, I would suggest they've never done it.

Well I thought I have done it a number of times, I should have been dreaming I even have helped one of my friends do porting of a fairly complex application in less than 3 days, I should again have been dreaming. Even if I have been dreaming, this time what I am saying is trivial even for the newbies, just use SQL 2008 express and a table on it to store the images.

This is an unnecessary debate that we would only agree to disagree. I would simply ask you in turn, how many times you have done an application that needs to store "many" images. I would suggest those who say "store on disk" have never done it.

BTW I am not talking about 1-10-100 or a few thousands of images, more than that, at least a few 10 thousands. My basis is the application(s) where I needed to store such images (and I really was storing just their path&filename). Do you think I was just seeking for fun to switch a working "just path" to memo binary.



Cetin Basoz
MS Foxpro MVP, MCP

 

[Olaf Doschke]

Well Cetin,

what I can imagine is, that you switched to SQL Server to store images, but not to memo binary, if you talk about 10 thousands of images.

We do support a database of probands for a customer and their ID photos are stored as files in the file share along with the database and that just works fine. It's not 10s of thousands but its between 1000 and 10000 pictures.

In this case the pictures are stored with their integer ID number, which is taken apart to form several directories, eg ID 1234 is stored in path \photos\1\234.jpg. This helps a lot in handling the problems you have, if you ever need to manually browse picture directories with more than 1000 pictures in it and that can easily be expanded.

Bye, Olaf.

 

[BubbaDan]

Cetin,
I am a total newbie at programing Foxpro. Taught myself with 2.6 well enough to create a work order program for our service department.

Now I am trying something with VisFP9.

Would you give me a quick tutorial on storing pictures into a database, displaying them and printing them in a report?

basically, it's just a company logo for the top of the workorder. on the form i'll have an image and a button to insert image.

using filetostr() to put pic in a memo(bin) works.

using strtofile() produces a file, but not a useable image,

the image placeholder on the form never shows my image.

 

[BubbaDan]

never mind, persistence has paid off again. I figured it out.

 

[cbasoz]

You need FileToStr() part only. Using VFP9, you can directly set the PictureVal property of an image control to the content of a file - FileToStr(yourimagefile) or a memo bin, blob field.

Here is a form sample that creates a simple table, if not exists already, inserts/updates image as company logo (you can select all the code and either save as a prg or temporarily paste into new code window select all and execute to see it in action - converting to scx is not hard.

Public oForm
oForm = Createobject('pictureSaver')
oForm.Show()

Define Class pictureSaver As Form
  Height = 250
  Width = 545
  BorderStyle = 2
  Caption = "Company Logo"

  Add Object lblLogo As Label With ;
    BackStyle = 0, Caption = "Logo", ;
    Left = 10, Top = 10, Width = 45

  Add Object txtlogo As TextBox With ;
    Left = 57, Top = 10, Width = 400

  Add Object cmdBrowse As CommandButton With ;
    Top = 10, Left = 460, Width = 80, Caption = "Browse"

  Add Object lblPreview As Label With ;
    BackStyle = 0, Caption = "Preview", ;
    Left = 10, Top = 50, Width = 45

  Add Object imglogo As Image With ;
    Left = 57, Height = 150,  Top = 50, Width = 150, Stretch=1

  Add Object cmdSave As CommandButton With ;
    Top = 50, Left = 215, Caption = "Save", AutoSize = .T.


  Procedure Init
    If !File('CompLogo.dbf')
      Create Table CompLogo (Id i, Logo w)
    Endif
    Use CompLogo
    Thisform.imglogo.PictureVal = CompLogo.Logo
  Endproc


  Procedure cmdBrowse.Click
    This.Parent.txtlogo.Value = Getpict()
    Thisform.imglogo.Picture = Thisform.txtlogo.Value
  Endproc


  Procedure cmdSave.Click
    Local Logo
    Logo = Filetostr(Thisform.txtlogo.Value)
    Update CompLogo Set Logo = m.logo Where Id = 1 && maybe would use multiple blobs in this table
    If _Tally = 0
      Insert Into CompLogo (Id,Logo) Values (1,m.logo)
    Endif

    Thisform.imglogo.PictureVal = CompLogo.Logo

    Messagebox("Updated the logo.", 0+64, "My App",15000)
  Endproc
Enddefine

OK now we have a 'logo' stored in table. In an application you could use this in a report in a variety of ways. Thinking it would be tedious to make sure CompLogo table is open each time a report is used, it is a good idea to store this image in a property of a custom application object (many developers create a custom class in main.prg to keep application wide variables -instead of public variables- and methods). For this sample though I would assume oApp is your application's custom class but create here anew:

public oApp && normally this is the only public variable
            && in an application
oApp = createobject('Custom')

** This would be in application object's
** methods, probably called in init
** here we do by hand
use CompLogo
oApp.AddObject('CompanyLogo','Image')
oApp.CompanyLogo.PictureVal = complogo.logo
USE IN 'complogo'

Now we have our logo as a property of oApp object. Create a test report from a test data, insert an Ole/Picture control (the one that looks like a disk icon), set its 'Control Source Type' to 'Expression or Variable name' and type:

oApp.CompanyLogo

as the expression. Also select 'Scale contents, retain shape' - better yet experiment with different settings. You are done, preview.

Remember oApp.CompanyLogo is like a memory variable, it needs to be available whenever you run this report.


Cetin Basoz
MS Foxpro MVP, MCP

 

[BubbaDan]

Awesome, thanks very much. I had figured out how to store and extract, using another field to store the original name, but could not get it working directly from the database.

After reading your post, I have it working. Thank you ever so much.