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;

[download]

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 :)

In reply to Storing jpegs as BLOB in M$ Access by Splintex

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.