Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

Handling pictures with DBI and Access

by kevin_i_orourke (Friar)
on Jul 25, 2001 at 15:07 UTC ( #99624=perlquestion: print w/replies, xml ) Need Help??

kevin_i_orourke has asked for the wisdom of the Perl Monks concerning the following question:

One of my personal projects at the moment is a database of slides I have taken over the past few years (see my website for some examples, it's a bit broken just now though). I started off by using MS Access (ick!) so I'm sticking with it for now (although MySQL looks good as a replacement).

One of the fields in the database is a thumbnail of the slide. In the past I've added a few of these manually, but it's a bit of a pain to open the file in Paint Shop Pro, copy it and paste it into the DB.

I'm looking for a way to automate this process but of course Access doesn't seem to make this easy. The images go into an OLE object field (ick ick ick!).

I can dump out an existing object via DBI::ODBC but I can't work out how to create an OLE object from a file using Perl. Would something in Win32::OLE help?

Or should I just move the whole thing across to a proper database and store the images as BLOBs? I had thought about keeping the images as separate files but that makes copying/moving the database more difficult.

Kevin O'Rourke

Replies are listed 'Best First'.
Re: Handling pictures with DBI and Access
by dmmiller2k (Chaplain) on Jul 25, 2001 at 17:18 UTC

    It's possible to manipulate Access as an OLE object using Win32::OLE. This is essentially using it as an OLE automation server. You'll have to familiarize yourself with the MS Access object model, of course.

    Not too long ago, I was faced with the task of printing customer invoices, each of which consisted of a variable number of (from five to seven) separate PDF files, which were generated by our system. These had to be collated in a particular order, which depended upon the individual invoice.

    My solution was a perl program running on a dedicated NT box, which instantiated an Acrobat COM object (OLE automation server), and called methods on that object to Open, Print, then Close each file in sequence.

    In your case (you'll need to investigate Access' object model to be able to do this), you'll want to open your database, then for each image you want to insert, select the relevant row (record) in a result set, instantiate an appropriate object type from the file containing the image, and then update the appropriate column (field) of the result row with that object.

    This can all be done from Perl :)

    It's rather tedious wading through Microsoft's docs, looking for info about the Access object model; which properties, collections, etc., to use. but it's worth all the trouble when you have a working automated solution.

Re: Handling pictures with DBI and Access
by AidanLee (Chaplain) on Jul 25, 2001 at 15:42 UTC

    You should indeed consider moving your images out of the database (and simply referring to their name/location in the DB) if/when you switch to a full RDBMS (eg MySQL). Moving the database + images isn't too bad, I don't think. For MySQL at least you can do a dump of the table structures and data. Then you can simply tar/zip that up along with your image directory, and you should be good to go. Same can be done with an Access .mdb file

    As to your actual question on OLE I'm afraid you'll have to wait for a monk worthier than I for some wisdom on that.

Re: Handling pictures with DBI and Access
by wardk (Deacon) on Jul 25, 2001 at 17:31 UTC

    I really agree with AidanLee. You may want to just stay away from saving blob data. It's my bias (but it's an educated bias) against Access, but it seems that doing blobs is gonna eventually see performance issues as the access db grows (my experience is big access tables are trouble). Not to mention portability issues (when you toss the yoke and move to mySql or another RDBMS).

    I would use some standard naming that makes matching thumnails to their big brothers intuitive and easy to code for, then perhaps store the name/path info in the db, and just say no to stuffing images into blobs....even if NOT using MS Access, images do just fine sitting in a file structure and reading them from the file system is going to be much more efficient that having Access between you and the files.

    ...and using any manual process above and beyond the minimal seems like too much work, then again perhaps perl is just making me lazy ;-)

    good luck whichever way you go!

      I think the original decision to store the image data in Access was probably false laziness.

      I wanted the picture to be displayed within an Access form and this seemed like the easiest way to do that. I'm sure (given a bit of VB code) I could load an image from the filesystem into a form.

      Must port to MySQL soon, it's approaching the top of the things to do list.

      Kevin O'Rourke

Re: Handling pictures with DBI and Access
by John M. Dlugosz (Monsignor) on Jul 25, 2001 at 19:13 UTC
    Here's how I did it:

    I created both the thumbnail and the screen-sized compressed image using ImageMagick from the Perl script. The original, higher fidelity large size, image is on the server.

    Every image is assigned a random code number. Not sequencial numbers, but random, for two reasons: so they hash nicely, and so people can't guess at numbers for pictures they were not shown in the album.

    Anyway, it associates a fully qualified file name of the original image with the code number.

    Now, when an image is needed of some size and quality (thumbnail or screen-sized), it forms a filename by combining the code number with option values.

    If that file name exists in the image cache directory, serve it up! If it doesn't exist, generate it first using ImageMagick, then serve it up. When cache gets too large (e.g. 1GB) oldest files are deleted (could be better).

    The file system is part of my database! As for copying/moving, it's a automatically-generated cache, so big deal, just delete it. For moving or renaming source images, the primary input to the system is actually the directory of full-sized images, and it scans that for available files. It will automatically notice changes when an album is displayed.

    Now, that's a complex system with security and billing and everything, and selectable resolutions and quality and monitor calibration. It was a bit to bite off, so I also made a simpler (really simple) server that I could use without such a large development effort. It fills my needs of delivering immediate results to models who just had portfolio shots taken. There is one working directory that the script uses for its stuff. I put a configuration file, e.g. foobar123.ini, into that directory and include lines in it pointing at the source directory of full-size images (it can reach my work area via the network, where her files actually live), the desired resolution and quality settings, how many "up" to list, album title, etc. With stock settings, a simple perl script generates this ini file automatically given the target directory name.

    The name, foobar123 in this case, is actually randomly chosen, so simply knowing it amounts to a password. Put that "personal password" in on the main page, and it reads the ini file of that name, and also looks in a subdirectory of the same name for its image files. When one album page's HTML is generated, it makes sure that all the thumbnails and screen images exist, creating them if needed. So the first time the page is served it is generated, and the IMG files and links are all normal static files, not scripts to serve them up. All the generated files for that album are in one place in a subdir, so it is easily deleted when I'm done with it. There is no database: static settings only in the .ini, and files in a subdirectory make up the whole system.


Re: Handling pictures with DBI and Access
by InfiniteSilence (Curate) on Jul 25, 2001 at 22:43 UTC
    When an access DB grows large it requires regular compressions to keep it from turning to crap. Blobs are possible in MSAccess though. Even though I probably would not put all of my image data in MSAccess, I would probably consider a distributed cache of images to reside in an .mdb. For instance, let's say you distribute a CD with your images and other marketing information. Are you going to send them MySQL? I don't think so. Here is the location for some code to make BLOBs in MSAccess. I tested it on Access 97 but it should work fine on 2000: click. Never mind the other stuff there. I was once planning to write a book on MSAccess and wanted to encourage my readers to submit their own modular code. Just click the Blob's link and the Documenting Class. You will need both in order for the Blob class to work, or just gut out all references in the Blob class to the Document class. Have fun!

    Celebrate Intellectual Diversity

Re: Handling pictures with DBI and Access
by zebedee (Pilgrim) on Jul 26, 2001 at 12:06 UTC
    Using ADO seems easier than using DAO/Jet. Here is a bit that reads a database (never got as far as looking at writing, let alone writing a blob, so might not help much).
    #Using ADO from PERL use Win32::OLE; my $Conn=Win32::OLE->new('ADODB.Connection') or die "Unable to create +ADODB.Connection"; #NB.You will need to set up the DSN in the ODBC Data Manager (Control +Panel) # MUST be done on the System DSN tab, you may to enter user name an +d password #Don't or die on these methods - it dies every time! $Conn->Open("<db name in Data Manager>","<optional userid>","<optional + password>"); $RS=$Conn->Execute("SELECT * FROM <table>"); $RS->MoveFirst(); until ($RS->Eof()) { print $RS->Fields->{'Name'}->Value,"\n"; $RS->MoveNext(); } $RS->Close; $Conn->Close;
Re: Handling pictures with DBI and Access
by Ay_Bee (Monk) on Jul 25, 2001 at 18:11 UTC
    If you are approaching this as a learning project I do not yet have th +e skills to advise you. But if you wish a quick ready made answer using MySQL try for a MySQL database that does exactly what you describe -_-_-_-_-_-_-_-_-_-_-_- My memory concerns me - but I forget why !!!

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://99624]
Approved by root
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (None)
    As of 2021-10-16 03:54 GMT
    Find Nodes?
      Voting Booth?
      My first memorable Perl project was:

      Results (69 votes). Check out past polls.