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

I am attempting (in my own special way) to write a script to ease the entry of data into our ID card system. I have a directory of around 800 student photos all starting with there ID number, and we can easily import the Students Txt details into the access DB that runs the card printer...

BUT (the big butt) Adding each of the photos individually would be very time consuming. So.. I figured I can write a perl script that will strip the ID number from the start of the filename, then search the access DB for the appropriate record, and then insert the Jpeg file there (it has to be stored in the db not as a link to an external file. the field is an ole field) and after much reading of doccumentation I ended up with this:



Code Follows :
use Win32::ODBC; $DSN="students"; if (!($db=new Win32::ODBC($DSN))) { print "Error connecting to $DSN\n"; print "Error: " . Win32::ODBC::Error() . "\n"; } else { print "Database being searched...\n"; } $SqlStatement = "SELECT * FROM ProjectTable"; if ($db->Sql($SqlStatement)) { print "SQL failed.\n"; print "Error: " . $db->Error() . "\n"; } else { print "Inside ELSE \n"; opendir(DIR, "."); @files = readdir(DIR); closedir(DIR); shift (@files); shift (@files); while($db->FetchRow()) { undef %Data; my (%Data) = $db->DataHash(); $test = $Data{"TextField5"}; print "$test \n"; foreach $file (@files) { print "$file\n"; #<- original File Name\n"; $fileName = $file; #print "$file \n";#$file2 = $file; $file =~ s/\D//g; print "$file <- Stripped Id Number\n"; print "Should Enter If Now \n"; Open( FH, "<$fileName" ) || die "unable to open $fileName ($ +!)\n"; { local( $/ ); undef $/; $filecontents = <FH>; } close( FH ); if ($test = $file) { print "Inside IF comparrison\n"; #$sqlinsert = "INSERT INTO ProjectTable (PhotoField1) + VALUES ($fileName)"; $rc = $db->sql("INSERT INTO ProjectTable ('PhotoField +1') VALUES ('$filecontents')"); if($rc){ die qq(SQL error "$sqlinsert": ), $db->Error(), qq(<br>) ; + } else {print "insert success!</ br>";}} print "$test <- supposed to be new data\n"; } } } $db->Close(); exit;

I feel like a blind man stumbling along without his cane. From what I have read it seems that I should have to read the jpeg into a filehandle as binary data and then store that into the access database after finding the Student ID number that it matches. But, It doesn't work I should probably post some of the error messages, but this is getting quite long enough as it is.

Any hints, comments, reccomendations, flames about my badly written code , spelling etc, will all be appriciated :)

Replies are listed 'Best First'.
Re: Storing jpegs as BLOB in M$ Access
by jZed (Prior) on Apr 01, 2004 at 04:04 UTC
    Sorry, I'm too steeped in DBI to be able to muddle through the Win32::ODBC stuff, but a quick grep of your posting shows a very major gap. Three words: perldoc -f binmode
      Cheers
Re: Storing jpegs as BLOB in M$ Access
by inman (Curate) on Apr 01, 2004 at 09:46 UTC
    Just a thought but why don't you try using DBI with the DBD-ODBC driver? You will be able to get more support from us Monks and learning to use DBI will pave the way for a whole range of databases rather than just those using ODBC.

    There is more than one way to do it!