Create thumbnails of all your digital photos in a single table

At least once a week I download my photos to a new folder on one of my computers. As part of that process I run some code that generates thumbnail images into a table. With almost 22,000 pictures, the thumbnail table is almost 100 megabytes (1 Meg for the DBF, and 99 Meg for the FPT). The code below will create the thumbnail table if it doesn’t exist, or add to it only the missing ones if it does. MyPix.dbf is a table with just a few fields: the fullpath of the picture/video, a datetime stamp and a text field for an annotation. I keep these 2 tables separate because mypix.dbf is tiny (about the size of 3 JPGs) making it easy to backup and copy to other machines, whereas the thumbnail table is easily recreateable and thus discardable.

It’s pretty nice to be able to see all the pictures of my daughter (or any other string search using SQL Select) in proof sheets of thumbnails, and be able to scroll quickly around from her ultrasound 9 years ago to the most recent photos a few hours old. Each image has a tooltip which shows the text description (from the Mypix.dbf table). These thumbnail previews are available to me in either a VFP front end rich client or as a web application so I can query my photo database from any online machine in the world.

The code uses the new (in VFP9) GDIPlus Foundation class to make a thumbnail image. (see also this paper) and new VFP9 SQL enhancements. The table has only 2 fields: the full path to the picture and the thumbnail as a blob.

Notice :

  • the CAST function below which takes the binary representation of the thumbnail and converts it to a blob which is then inserted into the table.

  • The INSERT INTO …SELECT …FROM WHERE …NOT IN (SELECT … FROM ) command. Very powerful! The “WHERE..NOT IN” part inserts only those that are not already in the table.

  • There are 2 AS clauses: the “as blob” means the datatype Blob. The “as Thumb” is the name to give the resulting field.

  • Being able to call functions from within a SQL Select statement is very powerful.

My Canon camera records a “THM” file with every AVI movie file, which is just a JPG thumbnail, which is inserted into the blob field.


      ?"now make thumbs"


      SET CLASSLIB TO HOME()+"ffc\_gdiplus"

      IF !FILE("pixthumb.dbf")

            SELECT mypix.fullname,CAST(MakeThumb(PICPATH+ALLTRIM(mypix.fullname)) as blob) as thumb ;

                  FROM mypix.dbf ;

                  INTO table pixthumb

            INDEX on fullname TAG fullname


            INSERT INTO pixthumb ;

                  SELECT mypix.fullname,CAST(MakeThumb(PICPATH+ALLTRIM(mypix.fullname)) as blob) as thumb ;

      FROM mypix WHERE fullname NOT in (SELECT fullname FROM pixthumb)


PROCEDURE makethumb(cFileName as String)

      LOCAL oImage as gpimage OF HOME()+"ffc\_gdiplus",oThumb as gpimage

      IF UPPER(JUSTEXT(cFileName))$"JPG"


            IF VARTYPE(oImage)='O'


                  IF mypix.rotate>0




                  RETURN FILETOSTR("d:\thumb.jpg")



            IF FILE(FORCEEXT(cFileName,"THM"))

                  RETURN FILETOSTR(FORCEEXT(cFileName,"THM"))



      RETURN ""

See also Sharing Disneyland Digital pictures