DBD::ADO and Access IMAGE (OLE Object) fields...

Hello all,
  I'm trying to insert images into an Access database (they're small, and that's how the app
was built, not my choice), but I'm running into errors.  I'm using...

Windows XP SP1
ActiveState Perl: This is perl, v5.8.3 built for MSWin32-x86-multi-thread
DBI 1.43
DBD::ADO 2.91
Win32::OLE 0.1701
CGI 3.01
CGI::Carp 1.27
Text::CSV_XS 0.23

  I'd appreciate any feedback, and would love to have a solution...

Here's a complete stand-alone script that *should* work, but doesn't, quite...
Oh, you'll need to find the graphics located at the end of the script...

#!perl -w
use strict;

eval { use DBI; };
if ($@) { die "This system does not have the DBI installed!\n"; }
eval { use DBD::ADO; };
if ($@) { die "Database type ADO not supported!\n"; }
eval { use CGI; };
if ($@) { die "CGI module not supported!\n"; }
eval { use CGI::Carp; };
if ($@) { die "CGI::Carp module not supported!\n"; }

my ($dbh, $Access, $AccessDB, $Workspace);
my $db_name = 'C:\development\web\PicsDB\myPics.mdb';
my $ConnStr  = "dbi:ADO:Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;Data Source=". $db_name;

my $q = new CGI;
my $load = $q->param('load');
my $showPic = $q->param('showPic');
my $show_picID = $q->param('picID');
if ($load) {
  doDBLoad();
} else {
  connectDB();
  if (($showPic) && ($show_picID)) {
    showPic();
  } else {
    showPicLinks();
  }
}
$dbh->disconnect();
exit;

sub showPic {
  my $sqlStatement = "select picType, picData from myPics where picID = ?";
  my $sthSelect = $dbh->prepare($sqlStatement);
  eval {$sthSelect->execute($show_picID); };
  if ($@) { carp("Select statement '$sqlStatement' failed!\nErrors: $dbh->errstr \n"); exit; }
  my ($picType, $picData) = $sthSelect->fetchrow_array;
  $sthSelect->finish;
  print $q->header($picType);
  print $picData;
}

sub showPicLinks {
  print $q->header('text/html');
  print $q->start_html("myPics DB Display");
  my $sqlStatement = "select picID, picComment from myPics";
  my $sthSelect = $dbh->prepare($sqlStatement);
  eval {$sthSelect->execute; };
  if ($@) { carp("Select statement '$sqlStatement' failed!\nErrors: $dbh->errstr \n"); exit; }
  while (my ($picID, $picComment) = $sthSelect->fetchrow_array ) {
    print "<a href='myPics.pl?showPic=1&picID=$picID'><b>$picComment:</b></a><br><img src=myPics.pl?showPic=1&picID=$picID><br>\n";
  }
  $sthSelect->finish;
}

sub connectDB {
  eval { $dbh = DBI->connect( $ConnStr, "Admin", "", {RaiseError => 0, PrintError => 0, AutoCommit => 1} ); };
  if ($@) { die("Database connection failed!\n$@\n"); }
  $dbh->{LongReadLen} = 2000000;
  $dbh->{LongTruncOk} = 0;
}

sub doDBLoad {
  no strict 'subs';
  eval { use Text::CSV_XS; };
  if ($@) { die "Text::CSV_XS not supported...\n"; }
  my $csv = Text::CSV_XS->new;
  print "Creating database...\n";
  CreateAccessDB();
  print "Done!\n";
  connectDB();
  eval { 
    use Win32::OLE;
    Win32::OLE->Option(CP => Win32::OLE::CP_UTF8);
  };
  if ($@) { die "Win32::OLE maybe not supported...?\n"; }
  my $create_statement = "create table [myPics] ([picID] INT NOT NULL, [picComment] VARCHAR (50), [picType] VARCHAR (50), [picData] IMAGE , ".
                         "PRIMARY KEY ([picID] ), CONSTRAINT myPic_PK UNIQUE ([picID] ))";
  my $sth = $dbh->prepare($create_statement);
  eval {$sth->execute; };
  if ($@) { die "Create staement failed!\nErrors: $dbh->errstr \n"; }
  my $sqlStatement = "INSERT INTO myPics (picID, picComment, picType, picData) VALUES (?, ?, ?, ?)";
  $sth = $dbh->prepare($sqlStatement);
  my $picList = PicList();
  foreach (split("\n", $picList)) {
    if ($csv->parse($_)) {
      my ($picID, $picComment, $picType, $picImage) = $csv->fields;
      if (-e $picImage) {
        print "Loading $picImage into database...";
        my $picData = readblobfile($picImage);
        $sth->bind_param(1, $picID);
        $sth->bind_param(2, $picComment);
        $sth->bind_param(3, $picType);
        #########
        # Errors 
        # 1) Database seems to load, but has extreme bloat, and images do not work...
        # 2) OLE exception from "Microsoft JET Database Engine":\n\nParameter ?_4 has no default value.
        # 3) OLE exception from "ADODB.Command":\n\nApplication uses a value of the wrong type for the current operation.
        # 4) OLE exception from "ADODB.Parameter":\n\nArguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
        # 5) OLE exception from "Microsoft JET Database Engine":\n\nUnspecified error
        #Attemped Binding                                                           # Error code
        $sth->bind_param(4, $picData);                                             # 1
        #$sth->bind_param(4, $picData, DBI::SQL_GUID );                             # 5
        #$sth->bind_param(4, $picData, DBI::SQL_WLONGVARCHAR );                     # 1
        #$sth->bind_param(4, $picData, DBI::SQL_WVARCHAR );                         # 1
        #$sth->bind_param(4, $picData, DBI::SQL_WCHAR );                            # 1
        #$sth->bind_param(4, $picData, DBI::SQL_BIT );                              # 1
        #$sth->bind_param(4, $picData, DBI::SQL_TINYINT );                          # 2
        #$sth->bind_param(4, $picData, DBI::SQL_LONGVARBINARY );                    # 3
        #$sth->bind_param(4, $picData, DBI::SQL_VARBINARY );                        # 3
        #$sth->bind_param(4, $picData, DBI::SQL_BINARY );                           # 3
        #$sth->bind_param(4, $picData, DBI::SQL_LONGVARCHAR );                      # 1
        #$sth->bind_param(4, $picData, DBI::SQL_UNKNOWN_TYPE );                     # 1
        #$sth->bind_param(4, $picData, DBI::SQL_ALL_TYPES );                        # 1
        #$sth->bind_param(4, $ImageFile, DBI::SQL_CHAR );                           # 1
        #$sth->bind_param(4, $picData, DBI::SQL_NUMERIC );                          # 2
        #$sth->bind_param(4, $picData, DBI::SQL_DECIMAL );                          # 2
        #$sth->bind_param(4, $picData, DBI::SQL_INTEGER );                          # 2
        #$sth->bind_param(4, $picData, DBI::SQL_SMALLINT );                         # 2
        #$sth->bind_param(4, $picData, DBI::SQL_FLOAT );                            # 2
        #$sth->bind_param(4, $picData, DBI::SQL_REAL );                             # 1
        #$sth->bind_param(4, $picData, DBI::SQL_DOUBLE );                           # 2
        #$sth->bind_param(4, $picData, DBI::SQL_DATETIME );                         # 2
        #$sth->bind_param(4, $picData, DBI::SQL_DATE );                             # 2
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL );                         # 1
        #$sth->bind_param(4, $picData, DBI::SQL_TIME );                             # 1
        #$sth->bind_param(4, $picData, DBI::SQL_TIMESTAMP );                        # 2
        #$sth->bind_param(4, $picData, DBI::SQL_VARCHAR );                          # 1
        #$sth->bind_param(4, $picData, DBI::SQL_BOOLEAN );                          # 2
        #$sth->bind_param(4, $picData, DBI::SQL_UDT );                              # 2
        #$sth->bind_param(4, $picData, DBI::SQL_UDT_LOCATOR );                      # 1
        #$sth->bind_param(4, $picData, DBI::SQL_ROW );                              # 4
        #$sth->bind_param(4, $picData, DBI::SQL_REF );                              # 4
        #$sth->bind_param(4, $picData, DBI::SQL_BLOB );                             # 3
        #$sth->bind_param(4, $picData, DBI::SQL_BLOB_LOCATOR );                     # 4
        #$sth->bind_param(4, $picData, DBI::SQL_CLOB );                             # 1
        #$sth->bind_param(4, $picData, DBI::SQL_CLOB_LOCATOR );                     # 4
        #$sth->bind_param(4, $picData, DBI::SQL_ARRAY );                            # 4
        #$sth->bind_param(4, $picData, DBI::SQL_ARRAY_LOCATOR );                    # 4
        #$sth->bind_param(4, $picData, DBI::SQL_MULTISET );                         # 4
        #$sth->bind_param(4, $picData, DBI::SQL_MULTISET_LOCATOR );                 # 4
        #$sth->bind_param(4, $picData, DBI::SQL_TYPE_DATE );                        # 4
        #$sth->bind_param(4, $picData, DBI::SQL_TYPE_TIME );                        # 2
        #$sth->bind_param(4, $picData, DBI::SQL_TYPE_TIMESTAMP );                   # 2
        #$sth->bind_param(4, $picData, DBI::SQL_TYPE_TIME_WITH_TIMEZONE );          # 4
        #$sth->bind_param(4, $picData, DBI::SQL_TYPE_TIMESTAMP_WITH_TIMEZONE );     # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_YEAR );                    # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_MONTH );                   # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_DAY );                     # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_HOUR );                    # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_MINUTE );                  # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_SECOND );                  # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_YEAR_TO_MONTH );           # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_DAY_TO_HOUR );             # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_DAY_TO_MINUTE );           # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_DAY_TO_SECOND );           # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_HOUR_TO_MINUTE );          # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_HOUR_TO_SECOND );          # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_MINUTE_TO_SECOND );        # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTEGER);                           # 4
        eval { $sth->execute; };
        if ($@) {
          print "Graphic import failed for image $picImage\n";
          $dbh->disconnect;
          exit(255);
        }
        print "  Done!\n";
      } else { print "Could not find image $picImage; not loaded!\n"; }
    } else { print "CSV parsing failed!\n"; }
  }
}

sub readblobfile($) {
  my $file = shift; #get file name
  local( $/, *FILE); #see perldoc perlvar for an explanation here
  open(FILE, "$file") or die "$!";
  binmode(FILE);
  my $content = <FILE>;
  close(FILE);
  return $content;
}

sub CreateAccessDB {
  if ( -e "$db_name") { # if the file already exists, delete it
    unlink("$db_name") || die("Could not delete the old database file $db_name\n");
  }
  eval { use Win32::OLE; };
  if ($@) { die "Win32::OLE not supported...\n"; }
  eval { use Win32::OLE::Const 'Microsoft ActiveX Data Objects'; };
  if ($@) { die "ADO maybe not supported...?\n"; }
  eval {
    $AccessDB = Win32::OLE->new("ADOX.Catalog");
    $AccessDB->Create("Provider='Microsoft.Jet.OLEDB.4.0';Jet OLEDB:Engine Type=5;Data Source='". $db_name ."'");
  };
  if ($@) { die "Couldn't create the database $db_name...!\n"; }
  Win32::OLE->Uninitialize;
}
  
sub PicList {
  my $picList = <<'EOF';
1,The Charter Communications Logo,image/gif,C:\development\web\PicsDB\Charter_Logo.gif
2,The Google Logo,image/gif,C:\development\web\PicsDB\Google_Logo.gif
3,The Yahoo Logo,image/gif,C:\development\web\PicsDB\Yahoo_Logo.gif
4,The AOL Logo,image/gif,C:\development\web\PicsDB\AOL_Logo.gif
EOF
  return($picList);
}


Thanks much!
amonotod

--

    `\|||/         amonotod@    | sun|perl|windows
      (@@)         charter.net  | sysadmin|dba
  ooO_(_)_Ooo____________________________________
  _____|_____|_____|_____|_____|_____|_____|_____|
0
amonotod
11/7/2006 10:01:11 PM
perl.dbi.users 11098 articles. 1 followers. Follow

8 Replies
549 Views

Similar Articles

[PageSpeed] 33

amonotod wrote:
>   I'm trying to insert images into an Access database (they're small, and that's how the app
> was built, not my choice), but I'm running into errors.  I'm using...
> 
> Windows XP SP1
> ActiveState Perl: This is perl, v5.8.3 built for MSWin32-x86-multi-thread
> DBI 1.43
> DBD::ADO 2.91
> Win32::OLE 0.1701
> CGI 3.01
> CGI::Carp 1.27
> Text::CSV_XS 0.23
> 
>   I'd appreciate any feedback, and would love to have a solution...
> 
> Here's a complete stand-alone script that *should* work, but doesn't, quite...

Hello again,
  I'm resending, after adding an additional subroutine to export the images after 
importing them to do a quick (-s) size verification.  I've also fixed a typo.  However,
the script still does not successfully load the data.  I'd appreciate any pointers that
anyone may have to offer...

  To run this script, you'll need some images (the ones I used are available at 
http://geocities.com/amonotod/picsDB_images.zip).  Create and load the database
with:

perl myPics.pl load=1

  To view the images (if the load works), set up the script to work under your favorite
web server (Apache2 for me), and view myPics.pl

#!perl -w
use strict;

eval { use DBI; };
if ($@) { die "This system does not have the DBI installed!\n"; }
eval { use DBD::ADO; };
if ($@) { die "Database type ADO not supported!\n"; }
eval { use CGI; };
if ($@) { die "CGI module not supported!\n"; }
eval { use CGI::Carp; };
if ($@) { die "CGI::Carp module not supported!\n"; }

my ($dbh, $Access, $AccessDB, $Workspace);
my $db_name = "C:/development/web/PicsDB/myPics.mdb"; # Will be created by doDBLoad()...
my $connStr  = "dbi:ADO:Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;Data Source=$db_name";
my $tempDir = "C:/Temp/dbd_temp/"; # Must pre-exist...
my $status = 1;

my $q = new CGI;
my $load = $q->param('load');
my $showPic = $q->param('showPic');
my $show_picID = $q->param('picID');
if ($load) {
  print "Doing database load...\n";
  eval { use Text::CSV_XS; };
  if ($@) { die "Text::CSV_XS not supported...\n"; }
  doDBLoad();
  exportDB();
  print "All done!\n";
  if ($status) { print "\n\tOperation was a success! :-)\n\n"; }
  else         { print "\n\tOperation was a failure! :-(\n\n"; }
} else {
  connectDB();
  if (($showPic) && ($show_picID)) {
    showPic();
  } else {
    showPicLinks();
  }
}
$dbh->disconnect();
exit;

sub showPic {
  my $sqlStatement = "select picType, picData from myPics where picID = ?";
  my $sthSelect = $dbh->prepare($sqlStatement);
  eval {$sthSelect->execute($show_picID); };
  if ($@) { carp("Select statement '$sqlStatement' failed!\nErrors: $dbh->errstr \n"); exit; }
  my ($picType, $picData) = $sthSelect->fetchrow_array;
  $sthSelect->finish;
  print $q->header($picType);
  print $picData;
}

sub showPicLinks {
  print $q->header('text/html');
  print $q->start_html("myPics DB Display");
  my $sqlStatement = "select picID, picComment from myPics";
  my $sthSelect = $dbh->prepare($sqlStatement);
  eval {$sthSelect->execute; };
  if ($@) { carp("Select statement '$sqlStatement' failed!\nErrors: $dbh->errstr \n"); exit; }
  while (my ($picID, $picComment) = $sthSelect->fetchrow_array ) {
    print "<a href='myPics.pl?showPic=1&picID=$picID'><b>$picComment:</b></a><br><img src=myPics.pl?showPic=1&picID=$picID><br>\n";
  }
  $sthSelect->finish;
}

sub connectDB {
  eval { $dbh = DBI->connect( $connStr, "Admin", "", {RaiseError => 0, PrintError => 0, AutoCommit => 1} ); };
  if ($@) { die("Database connection failed!\n$@\n"); }
  $dbh->{LongReadLen} = 2000000;
  $dbh->{LongTruncOk} = 0;
}

sub doDBLoad {
  my $csv = Text::CSV_XS->new;
  print "Creating database...";
  CreateAccessDB();
  print "  Done!\n";
  connectDB();
  eval { 
    use Win32::OLE;
    Win32::OLE->Option(CP => Win32::OLE::CP_UTF8);
  };
  if ($@) { die "Win32::OLE maybe not supported...?\n"; }
  my $create_statement = "create table [myPics] ([picID] INT NOT NULL, [picComment] VARCHAR (50), [picType] VARCHAR (50), [picData] IMAGE , ".
                         "PRIMARY KEY ([picID] ), CONSTRAINT myPic_PK UNIQUE ([picID] ))";
  my $sth = $dbh->prepare($create_statement);
  eval {$sth->execute; };
  if ($@) { die "Create statement failed!\nErrors: $dbh->errstr \n"; }
  my $sqlStatement = "INSERT INTO myPics (picID, picComment, picType, picData) VALUES (?, ?, ?, ?)";
  $sth = $dbh->prepare($sqlStatement);
  my $picList = PicList();
  foreach (split("\n", $picList)) {
    if ($csv->parse($_)) {
      my ($picID, $picComment, $picType, $picImage) = $csv->fields;
      if (-e $picImage) {
        print "Loading $picImage into database...";
        my $picData = readblobfile($picImage);
        $sth->bind_param(1, $picID);
        $sth->bind_param(2, $picComment);
        $sth->bind_param(3, $picType);
        #########
        # Errors 
        # 1) Database seems to load, but has extreme bloat, and images do not work...
        # 2) OLE exception from "Microsoft JET Database Engine":\n\nParameter ?_4 has no default value.
        # 3) OLE exception from "ADODB.Command":\n\nApplication uses a value of the wrong type for the current operation.
        # 4) OLE exception from "ADODB.Parameter":\n\nArguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
        # 5) OLE exception from "Microsoft JET Database Engine":\n\nUnspecified error
        #Attemped Binding                                                           # Error code
        $sth->bind_param(4, $picData);                                             # 1
        #$sth->bind_param(4, $picData, DBI::SQL_GUID );                             # 5
        #$sth->bind_param(4, $picData, DBI::SQL_WLONGVARCHAR );                     # 1
        #$sth->bind_param(4, $picData, DBI::SQL_WVARCHAR );                         # 1
        #$sth->bind_param(4, $picData, DBI::SQL_WCHAR );                            # 1
        #$sth->bind_param(4, $picData, DBI::SQL_BIT );                              # 1
        #$sth->bind_param(4, $picData, DBI::SQL_TINYINT );                          # 2
        #$sth->bind_param(4, $picData, DBI::SQL_LONGVARBINARY );                    # 3
        #$sth->bind_param(4, $picData, DBI::SQL_VARBINARY );                        # 3
        #$sth->bind_param(4, $picData, DBI::SQL_BINARY );                           # 3
        #$sth->bind_param(4, $picData, DBI::SQL_LONGVARCHAR );                      # 1
        #$sth->bind_param(4, $picData, DBI::SQL_UNKNOWN_TYPE );                     # 1
        #$sth->bind_param(4, $picData, DBI::SQL_ALL_TYPES );                        # 1
        #$sth->bind_param(4, $picData, DBI::SQL_CHAR );                             # 1
        #$sth->bind_param(4, $picData, DBI::SQL_NUMERIC );                          # 2
        #$sth->bind_param(4, $picData, DBI::SQL_DECIMAL );                          # 2
        #$sth->bind_param(4, $picData, DBI::SQL_INTEGER );                          # 2
        #$sth->bind_param(4, $picData, DBI::SQL_SMALLINT );                         # 2
        #$sth->bind_param(4, $picData, DBI::SQL_FLOAT );                            # 2
        #$sth->bind_param(4, $picData, DBI::SQL_REAL );                             # 1
        #$sth->bind_param(4, $picData, DBI::SQL_DOUBLE );                           # 2
        #$sth->bind_param(4, $picData, DBI::SQL_DATETIME );                         # 2
        #$sth->bind_param(4, $picData, DBI::SQL_DATE );                             # 2
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL );                         # 1
        #$sth->bind_param(4, $picData, DBI::SQL_TIME );                             # 1
        #$sth->bind_param(4, $picData, DBI::SQL_TIMESTAMP );                        # 2
        #$sth->bind_param(4, $picData, DBI::SQL_VARCHAR );                          # 1
        #$sth->bind_param(4, $picData, DBI::SQL_BOOLEAN );                          # 2
        #$sth->bind_param(4, $picData, DBI::SQL_UDT );                              # 2
        #$sth->bind_param(4, $picData, DBI::SQL_UDT_LOCATOR );                      # 1
        #$sth->bind_param(4, $picData, DBI::SQL_ROW );                              # 4
        #$sth->bind_param(4, $picData, DBI::SQL_REF );                              # 4
        #$sth->bind_param(4, $picData, DBI::SQL_BLOB );                             # 3
        #$sth->bind_param(4, $picData, DBI::SQL_BLOB_LOCATOR );                     # 4
        #$sth->bind_param(4, $picData, DBI::SQL_CLOB );                             # 1
        #$sth->bind_param(4, $picData, DBI::SQL_CLOB_LOCATOR );                     # 4
        #$sth->bind_param(4, $picData, DBI::SQL_ARRAY );                            # 4
        #$sth->bind_param(4, $picData, DBI::SQL_ARRAY_LOCATOR );                    # 4
        #$sth->bind_param(4, $picData, DBI::SQL_MULTISET );                         # 4
        #$sth->bind_param(4, $picData, DBI::SQL_MULTISET_LOCATOR );                 # 4
        #$sth->bind_param(4, $picData, DBI::SQL_TYPE_DATE );                        # 4
        #$sth->bind_param(4, $picData, DBI::SQL_TYPE_TIME );                        # 2
        #$sth->bind_param(4, $picData, DBI::SQL_TYPE_TIMESTAMP );                   # 2
        #$sth->bind_param(4, $picData, DBI::SQL_TYPE_TIME_WITH_TIMEZONE );          # 4
        #$sth->bind_param(4, $picData, DBI::SQL_TYPE_TIMESTAMP_WITH_TIMEZONE );     # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_YEAR );                    # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_MONTH );                   # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_DAY );                     # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_HOUR );                    # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_MINUTE );                  # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_SECOND );                  # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_YEAR_TO_MONTH );           # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_DAY_TO_HOUR );             # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_DAY_TO_MINUTE );           # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_DAY_TO_SECOND );           # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_HOUR_TO_MINUTE );          # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_HOUR_TO_SECOND );          # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTERVAL_MINUTE_TO_SECOND );        # 4
        #$sth->bind_param(4, $picData, DBI::SQL_INTEGER);                           # 4
        eval { $sth->execute; };
        if ($@) {
          print "Graphic import failed for image $picImage\n";
          $dbh->disconnect;
          exit(255);
        }
        print "  Done!\n";
      } else { print "Could not find image $picImage; not loaded!\n"; }
    } else { print "CSV parsing failed!\n"; }
  }
}

sub exportDB {
  print "Exporting grapics to $tempDir\n";
  unless (-d $tempDir) { print "Temp dir $tempDir does not exist!\n"; exit(1); }
  my $csv = Text::CSV_XS->new;
  my $picList = PicList();
  my $sqlStatement = "select picData from myPics where picID = ?";
  my $sthSelect = $dbh->prepare($sqlStatement);
  foreach (split("\n", $picList)) {
    if ($csv->parse($_)) {
      my ($picID, $picComment, $picType, $picImage) = $csv->fields;
      my $picName = $tempDir . substr($picImage,rindex($picImage,"\\")+1,length($picImage));
      print "picName is $picName\n";
      eval {$sthSelect->execute($picID); };
      if ($@) { carp("Select statement '$sqlStatement' failed!\nErrors: $dbh->errstr \n"); exit; }
      my ($picData) = $sthSelect->fetchrow;
      open(IMAGE, "> $picName") || die("Could not open new image file for write\n");
      binmode(IMAGE);
      print IMAGE $picData;
      close(IMAGE);
      $sthSelect->finish;
      my $origSize = (-s $picImage);
      my $newSize = (-s $picName);
      unless ($origSize == $newSize) {
        print "\tError: Imported and exported files DO NOT match in size....!\n";
        $status = 0;
      } else {
        print "\tSuccess: Imported and exported files match in size....!\n";
      }
    }
  }
}

sub readblobfile($) {
  my $file = shift; #get file name
  local( $/, *FILE); #see perldoc perlvar for an explanation here
  open(FILE, "$file") or die "$!";
  binmode(FILE);
  my $content = <FILE>;
  close(FILE);
  return $content;
}

sub CreateAccessDB {
  if ( -e "$db_name") { # if the file already exists, delete it
    unlink("$db_name") || die("Could not delete the old database file $db_name\n");
  }
  eval { use Win32::OLE; };
  if ($@) { die "Win32::OLE not supported...\n"; }
  eval { use Win32::OLE::Const 'Microsoft ActiveX Data Objects'; };
  if ($@) { die "ADO maybe not supported...?\n"; }
  eval {
    $AccessDB = Win32::OLE->new("ADOX.Catalog");
    $AccessDB->Create("Provider='Microsoft.Jet.OLEDB.4.0';Jet OLEDB:Engine Type=5;Data Source='". $db_name ."'");
  };
  if ($@) { die "Couldn't create the database $db_name...!\n"; }
  Win32::OLE->Uninitialize;
}
  
sub PicList {
  my $picList = <<'EOF';
1,The Charter Communications Logo,image/gif,C:\development\web\PicsDB\Charter_Logo.gif
2,The Google Logo,image/gif,C:\development\web\PicsDB\Google_Logo.gif
3,The Yahoo Logo,image/gif,C:\development\web\PicsDB\Yahoo_Logo.gif
4,The AOL Logo,image/gif,C:\development\web\PicsDB\AOL_Logo.gif
EOF
  return($picList);
}


Thanks in advance, I appreciate any replies!
amonotod

--

    `\|||/         amonotod@    | sun|perl|windows
      (@@)         charter.net  | sysadmin|dba
  ooO_(_)_Ooo____________________________________
  _____|_____|_____|_____|_____|_____|_____|_____|

0
amonotod
11/8/2006 3:51:40 PM
amonotod wrote:

> Hello all,
>   I'm trying to insert images into an Access database (they're small, and that's how the app
> was built, not my choice), but I'm running into errors.  I'm using...

I can reproduce this :-(

>
[...]
>   eval {
>     use Win32::OLE;
>     Win32::OLE->Option(CP => Win32::OLE::CP_UTF8);
>   };

Looks like is has to do with UTF8. Commenting out (or moving it out of the
eval{}) avoids (or hides?) the problem. Sorry, no further ideas.


Steffen
0
sgoeldner
11/9/2006 9:46:23 AM
---- Steffen Goeldner <sgoeldner@cpan.org> wrote: 
> amonotod wrote:
> >   eval {
> >     use Win32::OLE;
> >     Win32::OLE->Option(CP => Win32::OLE::CP_UTF8);
> >   };
> 
> Looks like is has to do with UTF8. Commenting out (or moving it out of the
> eval{}) avoids (or hides?) the problem. Sorry, no further ideas.

The original code (embarrassingly enough) did not have the eval, so I don't think that's it. 
I just wanted to make sure that the code I posted to the list was complete and had 
some minimal error checking.  I actually ginned this code together from it's original 
source  to be a complete example for the list...

  So, might anyone have a snippet of code that does work?  It doesn't necessarily need 
to use ADO, though it should use DBI and work against Access 97/2000 (Jet 4/5)....

> Steffen

Thanks for giving it a look, I appreciate it....
amonotod

--

    `\|||/         amonotod@    | sun|perl|windows
      (@@)         charter.net  | sysadmin|dba
  ooO_(_)_Ooo____________________________________
  _____|_____|_____|_____|_____|_____|_____|_____|

0
amonotod
11/9/2006 8:52:06 PM
Hello all,
  Sometimes it is just more sensible to find a workaround rather than a solution.  So, 
here is an updated script that uses DBI::ADO to create the database, DBI::ODBC to 
populate and test it, and DBI::ADO to retrieve the pics via CGI.  

Thanks for the pointer, Bart!

As before, the pics I used are available at http://geocities.com/amonotod/picsDB_images.zip.

Populate the database with "perl myPics.pl load=1", then view the script via a
browser and web server of choice....

And, again, thanks for DBI, DBI::ADO, DBI::ADO, DBI::Sybase, DBI::Oracle and all the 
other great modules that are supported by this group...

amonotod

#!perl -w
use strict;

eval { use DBI; };
if ($@) { die "This system does not have the DBI installed!\n"; }
eval { use DBD::ADO; };
if ($@) { die "Database type ADO not supported!\n"; }
eval { use CGI; };
if ($@) { die "CGI module not supported!\n"; }
eval { use CGI::Carp; };
if ($@) { die "CGI::Carp module not supported!\n"; }

my ($dbh, $Access, $AccessDB, $Workspace);
my $db_name = "C:/development/web/PicsDB/myPics.mdb"; # Will be created by doDBLoad()...
my $connStr  = "dbi:ADO:Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;Data Source=$db_name";
my $connStrODBC  = "dbi:ODBC:driver=Microsoft Access Driver (*.mdb); dbq=$db_name";
my $tempDir = "C:/Temp/dbd_temp/"; # Must pre-exist...
my $status = 1;

my $q = new CGI;
my $load = $q->param('load');
my $showPic = $q->param('showPic');
my $show_picID = $q->param('picID');
if ($load) {
  print "Doing database load...\n";
  eval { use Text::CSV_XS; };
  if ($@) { die "Text::CSV_XS not supported...\n"; }
  doDBLoad();
  exportDB();
  print "All done!\n";
  if ($status) { print "\n\tOperation was a success! :-)\n\n"; }
  else         { print "\n\tOperation was a failure! :-(\n\n"; }
} else {
  connectDB();
  if (($showPic) && ($show_picID)) {
    showPic();
  } else {
    showPicLinks();
  }
}
$dbh->disconnect();
exit;

sub showPic {
  my $sqlStatement = "select picType, picData from myPics where picID = ?";
  my $sthSelect = $dbh->prepare($sqlStatement);
  eval {$sthSelect->execute($show_picID); };
  if ($@) { carp("Select statement '$sqlStatement' failed!\nErrors: $dbh->errstr \n"); exit; }
  my ($picType, $picData) = $sthSelect->fetchrow_array;
  $sthSelect->finish;
  print $q->header($picType);
  print $picData;
}

sub showPicLinks {
  print $q->header('text/html');
  print $q->start_html("myPics DB Display");
  my $sqlStatement = "select picID, picComment from myPics";
  my $sthSelect = $dbh->prepare($sqlStatement);
  eval {$sthSelect->execute; };
  if ($@) { carp("Select statement '$sqlStatement' failed!\nErrors: $dbh->errstr \n"); exit; }
  while (my ($picID, $picComment) = $sthSelect->fetchrow_array ) {
    print "<a href='myPics.pl?showPic=1&picID=$picID'><b>$picComment:</b></a><br><img src=myPics.pl?showPic=1&picID=$picID><br>\n";
  }
  $sthSelect->finish;
}

sub connectDB {
  eval { $dbh = DBI->connect( $connStr, "Admin", "", {RaiseError => 0, PrintError => 0, AutoCommit => 1} ); };
  if ($@) { die("Database connection failed!\n$@\n"); }
  $dbh->{LongReadLen} = 2000000;
  $dbh->{LongTruncOk} = 0;
}

sub connectDBODBC {
  eval { $dbh = DBI->connect( $connStrODBC, "Admin", "", {RaiseError => 0, PrintError => 0, AutoCommit => 1} ); };
  if ($@) { die("Database connection failed!\n$@\n"); }
  $dbh->{LongReadLen} = 2000000;
  $dbh->{LongTruncOk} = 0;
}

sub doDBLoad {
  my $csv = Text::CSV_XS->new;
  print "Creating database...";
  CreateAccessDB();
  print "  Done!\n";
  connectDBODBC();
  my $create_statement = "create table [myPics] ([picID] INT NOT NULL, [picComment] VARCHAR (50), [picType] VARCHAR (50), [picData] IMAGE , ".
                         "PRIMARY KEY ([picID] ), CONSTRAINT myPic_PK UNIQUE ([picID] ))";
  my $sth = $dbh->prepare($create_statement);
  eval {$sth->execute; };
  if ($@) { die "Create statement failed!\nErrors: $dbh->errstr \n"; }
  print "Column is ", $sth->fetchrow, "\n";
  
  my $sqlStatement = "INSERT INTO myPics (picID, picComment, picType, picData) VALUES (?, ?, ?, ?)";
  $sth = $dbh->prepare($sqlStatement);
  my $picList = PicList();
  foreach (split("\n", $picList)) {
    if ($csv->parse($_)) {
      my ($picID, $picComment, $picType, $picImage) = $csv->fields;
      if (-e $picImage) {
        print "Loading $picImage into database...";
        my $picData = readblobfile($picImage);
        $sth->bind_param(1, $picID);
        $sth->bind_param(2, $picComment);
        $sth->bind_param(3, $picType);
        $sth->bind_param(4, $picData, DBI::SQL_LONGVARBINARY );
        eval { $sth->execute; };
        if ($@) {
          print "Graphic import failed for image $picImage\n";
          $dbh->disconnect;
          exit(255);
        }
        print "  Done!\n";
      } else { print "Could not find image $picImage; not loaded!\n"; }
    } else { print "CSV parsing failed!\n"; }
  }
}

sub exportDB {
  print "Exporting grapics to $tempDir\n";
  unless (-d $tempDir) { print "Temp dir $tempDir does not exist!\n"; exit(1); }
  my $csv = Text::CSV_XS->new;
  my $picList = PicList();
  my $sqlStatement = "select picData from myPics where picID = ?";
  my $sthSelect = $dbh->prepare($sqlStatement);
  foreach (split("\n", $picList)) {
    if ($csv->parse($_)) {
      my ($picID, $picComment, $picType, $picImage) = $csv->fields;
      my $picName = $tempDir . substr($picImage,rindex($picImage,"\\")+1,length($picImage));
      print "picName is $picName\n";
      eval {$sthSelect->execute($picID); };
      if ($@) { carp("Select statement '$sqlStatement' failed!\nErrors: $dbh->errstr \n"); exit; }
      my ($picData) = $sthSelect->fetchrow;
      open(IMAGE, "> $picName") || die("Could not open new image file for write\n");
      binmode(IMAGE);
      print IMAGE $picData;
      close(IMAGE);
      $sthSelect->finish;
      my $origSize = (-s $picImage);
      my $newSize = (-s $picName);
      unless ($origSize == $newSize) {
        print "\tError: Imported and exported files DO NOT match in size....!\n";
        $status = 0;
      } else {
        print "\tSuccess: Imported and exported files match in size....!\n";
      }
    }
  }
}

sub readblobfile($) {
  my $file = shift; #get file name
  local( $/, *FILE); #see perldoc perlvar for an explanation here
  open(FILE, "$file") or die "$!";
  binmode(FILE);
  my $content = <FILE>;
  close(FILE);
  return $content;
}

sub CreateAccessDB {
  if ( -e "$db_name") { # if the file already exists, delete it
    unlink("$db_name") || die("Could not delete the old database file $db_name\n");
  }
  eval { 
    use Win32::OLE; 
  };
  if ($@) { die "Win32::OLE not supported...\n"; }
  eval { use Win32::OLE::Const 'Microsoft ActiveX Data Objects'; };
  if ($@) { die "ADO maybe not supported...?\n"; }
  eval {
    $AccessDB = Win32::OLE->new("ADOX.Catalog");
    $AccessDB->Create("Provider='Microsoft.Jet.OLEDB.4.0';Jet OLEDB:Engine Type=5;Data Source='". $db_name ."'");
  };
  if ($@) { die "Couldn't create the database $db_name...!\n"; }
  Win32::OLE->Uninitialize;
}
  
sub PicList {
  my $picList = <<'EOF';
1,The Charter Communications Logo,image/gif,C:\development\web\PicsDB\Charter_Logo.gif
2,The Google Logo,image/gif,C:\development\web\PicsDB\Google_Logo.gif
3,The Yahoo Logo,image/gif,C:\development\web\PicsDB\Yahoo_Logo.gif
4,The AOL Logo,image/gif,C:\development\web\PicsDB\AOL_Logo.gif
EOF
  return($picList);
}

--

    `\|||/         amonotod@    | sun|perl|windows
      (@@)         charter.net  | sysadmin|dba
  ooO_(_)_Ooo____________________________________
  _____|_____|_____|_____|_____|_____|_____|_____|
0
amonotod
11/10/2006 3:36:25 PM
Resending because it's been several hours and I haven't seen this show up yet...

Hello all, 
  Sometimes it is just more sensible to find a workaround rather than a solution.  
So, here is an updated script that uses DBI::ADO to create the database, DBI::ODBC
to populate and test it, and DBI::ADO to retrieve the pics via CGI.  
 
Thanks for the pointer, Bart! 
 
As before, the pics I used are available at 
http://geocities.com/amonotod/picsDB_images.zip. 
 
Populate the database with "perl myPics.pl load=1", then view the script via a 
browser and web server of choice.... 
 
And, again, thanks for DBI, DBI::ADO, DBI::ADO, DBI::Sybase, DBI::Oracle and all 
the other great modules that are supported by this group... 

Steffen, perhaps you could look at the difference in the bind variable code between 
DBD::ADO and DBD::ODBC?  I'd offer, but my ability with C es even more pitiable 
than with Perl...
 
Thanks again,
amonotod 
 
#!perl -w 
use strict; 
 
eval { use DBI; }; 
if ($@) { die "This system does not have the DBI installed!\n"; } 
eval { use DBD::ADO; }; 
if ($@) { die "Database type ADO not supported!\n"; } 
eval { use CGI; }; 
if ($@) { die "CGI module not supported!\n"; } 
eval { use CGI::Carp; }; 
if ($@) { die "CGI::Carp module not supported!\n"; } 
 
my ($dbh, $Access, $AccessDB, $Workspace); 
my $db_name = "C:/development/web/PicsDB/myPics.mdb"; # Will be created by 
doDBLoad()... 
my $connStr  = "dbi:ADO:Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine 
Type=5;Data Source=$db_name"; 
my $connStrODBC  = "dbi:ODBC:driver=Microsoft Access Driver (*.mdb); 
dbq=$db_name"; 
my $tempDir = "C:/Temp/dbd_temp/"; # Must pre-exist... 
my $status = 1; 
 
my $q = new CGI; 
my $load = $q->param('load'); 
my $showPic = $q->param('showPic'); 
my $show_picID = $q->param('picID'); 
if ($load) { 
  print "Doing database load...\n"; 
  eval { use Text::CSV_XS; }; 
  if ($@) { die "Text::CSV_XS not supported...\n"; } 
  doDBLoad(); 
  exportDB(); 
  print "All done!\n"; 
  if ($status) { print "\n\tOperation was a success! :-)\n\n"; } 
  else         { print "\n\tOperation was a failure! :-(\n\n"; } 
} else { 
  connectDB(); 
  if (($showPic) && ($show_picID)) { 
    showPic(); 
  } else { 
    showPicLinks(); 
  } 
} 
$dbh->disconnect(); 
exit; 
 
sub showPic { 
  my $sqlStatement = "select picType, picData from myPics where picID = ?"; 
  my $sthSelect = $dbh->prepare($sqlStatement); 
  eval {$sthSelect->execute($show_picID); }; 
  if ($@) { carp("Select statement '$sqlStatement' failed!\nErrors: $dbh->errstr 
\n"); exit; } 
  my ($picType, $picData) = $sthSelect->fetchrow_array; 
  $sthSelect->finish; 
  print $q->header($picType); 
  print $picData; 
} 
 
sub showPicLinks { 
  print $q->header('text/html'); 
  print $q->start_html("myPics DB Display"); 
  my $sqlStatement = "select picID, picComment from myPics"; 
  my $sthSelect = $dbh->prepare($sqlStatement); 
  eval {$sthSelect->execute; }; 
  if ($@) { carp("Select statement '$sqlStatement' failed!\nErrors: $dbh->errstr 
\n"); exit; } 
  while (my ($picID, $picComment) = $sthSelect->fetchrow_array ) { 
    print "<a 
href='myPics.pl?showPic=1&picID=$picID'><b>$picComment:</b></a><br><img 
src=myPics.pl?showPic=1&picID=$picID><br>\n"; 
  } 
  $sthSelect->finish; 
} 
 
sub connectDB { 
  eval { $dbh = DBI->connect( $connStr, "Admin", "", {RaiseError => 0, 
PrintError => 0, AutoCommit => 1} ); }; 
  if ($@) { die("Database connection failed!\n$@\n"); } 
  $dbh->{LongReadLen} = 2000000; 
  $dbh->{LongTruncOk} = 0; 
} 
 
sub connectDBODBC { 
  eval { $dbh = DBI->connect( $connStrODBC, "Admin", "", {RaiseError => 0, 
PrintError => 0, AutoCommit => 1} ); }; 
  if ($@) { die("Database connection failed!\n$@\n"); } 
  $dbh->{LongReadLen} = 2000000; 
  $dbh->{LongTruncOk} = 0; 
} 
 
sub doDBLoad { 
  my $csv = Text::CSV_XS->new; 
  print "Creating database..."; 
  CreateAccessDB(); 
  print "  Done!\n"; 
  connectDBODBC(); 
  my $create_statement = "create table [myPics] ([picID] INT NOT NULL, 
[picComment] VARCHAR (50), [picType] VARCHAR (50), [picData] IMAGE , ". 
                         "PRIMARY KEY ([picID] ), CONSTRAINT myPic_PK UNIQUE 
([picID] ))"; 
  my $sth = $dbh->prepare($create_statement); 
  eval {$sth->execute; }; 
  if ($@) { die "Create statement failed!\nErrors: $dbh->errstr \n"; } 
  print "Column is ", $sth->fetchrow, "\n"; 
  
  my $sqlStatement = "INSERT INTO myPics (picID, picComment, picType, picData) 
VALUES (?, ?, ?, ?)"; 
  $sth = $dbh->prepare($sqlStatement); 
  my $picList = PicList(); 
  foreach (split("\n", $picList)) { 
    if ($csv->parse($_)) { 
      my ($picID, $picComment, $picType, $picImage) = $csv->fields; 
      if (-e $picImage) { 
        print "Loading $picImage into database..."; 
        my $picData = readblobfile($picImage); 
        $sth->bind_param(1, $picID); 
        $sth->bind_param(2, $picComment); 
        $sth->bind_param(3, $picType); 
        $sth->bind_param(4, $picData, DBI::SQL_LONGVARBINARY ); 
        eval { $sth->execute; }; 
        if ($@) { 
          print "Graphic import failed for image $picImage\n"; 
          $dbh->disconnect; 
          exit(255); 
        } 
        print "  Done!\n"; 
      } else { print "Could not find image $picImage; not loaded!\n"; } 
    } else { print "CSV parsing failed!\n"; } 
  } 
} 
 
sub exportDB { 
  print "Exporting grapics to $tempDir\n"; 
  unless (-d $tempDir) { print "Temp dir $tempDir does not exist!\n"; exit(1); } 
  my $csv = Text::CSV_XS->new; 
  my $picList = PicList(); 
  my $sqlStatement = "select picData from myPics where picID = ?"; 
  my $sthSelect = $dbh->prepare($sqlStatement); 
  foreach (split("\n", $picList)) { 
    if ($csv->parse($_)) { 
      my ($picID, $picComment, $picType, $picImage) = $csv->fields; 
      my $picName = $tempDir . 
substr($picImage,rindex($picImage,"\\")+1,length($picImage)); 
      print "picName is $picName\n"; 
      eval {$sthSelect->execute($picID); }; 
      if ($@) { carp("Select statement '$sqlStatement' failed!\nErrors: 
$dbh->errstr \n"); exit; } 
      my ($picData) = $sthSelect->fetchrow; 
      open(IMAGE, "> $picName") || die("Could not open new image file for 
write\n"); 
      binmode(IMAGE); 
      print IMAGE $picData; 
      close(IMAGE); 
      $sthSelect->finish; 
      my $origSize = (-s $picImage); 
      my $newSize = (-s $picName); 
      unless ($origSize == $newSize) { 
        print "\tError: Imported and exported files DO NOT match in 
size....!\n"; 
        $status = 0; 
      } else { 
        print "\tSuccess: Imported and exported files match in size....!\n"; 
      } 
    } 
  } 
} 
 
sub readblobfile($) { 
  my $file = shift; #get file name 
  local( $/, *FILE); #see perldoc perlvar for an explanation here 
  open(FILE, "$file") or die "$!"; 
  binmode(FILE); 
  my $content = <FILE>; 
  close(FILE); 
  return $content; 
} 
 
sub CreateAccessDB { 
  if ( -e "$db_name") { # if the file already exists, delete it 
    unlink("$db_name") || die("Could not delete the old database file 
$db_name\n"); 
  } 
  eval { 
    use Win32::OLE; 
  }; 
  if ($@) { die "Win32::OLE not supported...\n"; } 
  eval { use Win32::OLE::Const 'Microsoft ActiveX Data Objects'; }; 
  if ($@) { die "ADO maybe not supported...?\n"; } 
  eval { 
    $AccessDB = Win32::OLE->new("ADOX.Catalog"); 
    $AccessDB->Create("Provider='Microsoft.Jet.OLEDB.4.0';Jet OLEDB:Engine 
Type=5;Data Source='". $db_name ."'"); 
  }; 
  if ($@) { die "Couldn't create the database $db_name...!\n"; } 
  Win32::OLE->Uninitialize; 
} 
  
sub PicList { 
  my $picList = <<'EOF'; 
1,The Charter Communications 
Logo,image/gif,C:\development\web\PicsDB\Charter_Logo.gif 
2,The Google Logo,image/gif,C:\development\web\PicsDB\Google_Logo.gif 
3,The Yahoo Logo,image/gif,C:\development\web\PicsDB\Yahoo_Logo.gif 
4,The AOL Logo,image/gif,C:\development\web\PicsDB\AOL_Logo.gif 
EOF 
  return($picList); 
} 


--

    `\|||/         amonotod@    | sun|perl|windows
      (@@)         charter.net  | sysadmin|dba
  ooO_(_)_Ooo____________________________________
  _____|_____|_____|_____|_____|_____|_____|_____|
0
amonotod
11/10/2006 9:01:45 PM
--------------060900060603050302090402
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

amonotod@charter.net wrote:

> Hello all, 
>   Sometimes it is just more sensible to find a workaround rather than a solution.  
> So, here is an updated script that uses DBI::ADO to create the database, DBI::ODBC
> to populate and test it, and DBI::ADO to retrieve the pics via CGI.  
>  
> Thanks for the pointer, Bart! 
>  
> As before, the pics I used are available at 
> http://geocities.com/amonotod/picsDB_images.zip. 
>  
> Populate the database with "perl myPics.pl load=1", then view the script via a 
> browser and web server of choice.... 
>  
> And, again, thanks for DBI, DBI::ADO, DBI::ADO, DBI::Sybase, DBI::Oracle and all 
> the other great modules that are supported by this group... 
> 
> Steffen, perhaps you could look at the difference in the bind variable code between 
> DBD::ADO and DBD::ODBC?  I'd offer, but my ability with C es even more pitiable 
> than with Perl...

Thanks, but ODBC and ADO are quite different beasts. I further investigated your
test case: the Jet ADO provider creates for the LONGBINARY column a parameter of
type 202 (adVarWChar) and size 510 - both are wrong. Thus, it's necessary to set
the type in bind_param() - which you did. However, DBD::ADO did not set the size.
Attached is a fixed implementation for bind_param(). It would be nice if you (and
others) give it a trial.


Steffen

--------------060900060603050302090402
Content-Type: text/plain;
 name="bind_param.pl"
Content-Transfer-Encoding: base64
Content-Disposition: inline;
 filename="bind_param.pl"

ICBzdWIgYmluZF9wYXJhbSB7CiAgICBteSAoICRzdGgsICRuLCAkdmFsdWUsICRhdHRyICkg
PSBAXzsKICAgIG15ICRjb25uID0gJHN0aC0+e2Fkb19jb25ufTsKICAgIG15ICRjb21tID0g
JHN0aC0+e2Fkb19jb21tfTsKCiAgICAkYXR0ciA9IHt9IHVubGVzcyBkZWZpbmVkICRhdHRy
OwogICAgJGF0dHIgPSB7IFRZUEUgPT4gJGF0dHIgfSB1bmxlc3MgcmVmICRhdHRyOwoKICAg
IG15ICRwYXJhbV9jbnQgPSAkc3RoLT5GRVRDSCgnTlVNX09GX1BBUkFNUycpIHx8IF9yZWZy
ZXNoKCAkc3RoICk7CgogICAgcmV0dXJuICRzdGgtPnNldF9lcnIoIC05MTUsIkJpbmQgUGFy
YW1ldGVyICRuIG91dHNpZGUgY3VycmVudCByYW5nZSBvZiAkcGFyYW1fY250LiIpIGlmICRu
ID4gJHBhcmFtX2NudCB8fCAkbiA8IDE7CgogICAgJHN0aC0+e1BhcmFtVmFsdWVzfXskbn0g
PSAkdmFsdWU7CgogICAgbXkgJGkgPSAkY29tbS0+UGFyYW1ldGVycy0+SXRlbSggJG4gLSAx
ICk7CgogICAgaWYgKCBleGlzdHMgJGF0dHItPnthZG9fdHlwZX0gKSB7CiAgICAgICRpLT57
VHlwZX0gPSAkYXR0ci0+e2Fkb190eXBlfTsKICAgIH0KICAgIGVsc2lmICggZXhpc3RzICRh
dHRyLT57VFlQRX0gKSB7CiAgICAgICRpLT57VHlwZX0gPSAkREJEOjpBRE86OlR5cGVJbmZv
OjpkYmkyYWRvLT57JGF0dHItPntUWVBFfX07CiAgICB9CiAgICBpZiAoIGRlZmluZWQgJHZh
bHVlICkgewogICAgICAkaS0+e1NpemV9ICA9IGRlZmluZWQgJGF0dHItPnthZG9fc2l6ZX0g
PyAkYXR0ci0+e2Fkb19zaXplfSA6IGxlbmd0aCAkdmFsdWU7CiAgICAgIGlmICggJGktPntU
eXBlfSA9PSAkRW51bXMtPntEYXRhVHlwZUVudW19e2FkVmFyQmluYXJ5fQogICAgICAgIHx8
ICRpLT57VHlwZX0gPT0gJEVudW1zLT57RGF0YVR5cGVFbnVtfXthZExvbmdWYXJCaW5hcnl9
CiAgICAgICAgICkgewogICAgICAgIG15ICRwaWMgPSBXaW4zMjo6T0xFOjpWYXJpYW50LT5u
ZXcoIFdpbjMyOjpPTEU6OlZhcmlhbnQ6OlZUX1VJMSgpIHwgV2luMzI6Ok9MRTo6VmFyaWFu
dDo6VlRfQVJSQVkoKSwgJGktPntTaXplfSApOwogICAgICAgICRwaWMtPlB1dCggJHZhbHVl
ICk7CiAgICAgICAgJGktPntWYWx1ZX0gPSAkcGljOwogICAgICAgICRzdGgtPnRyYWNlX21z
ZygiICAgIC0tIEJpbmFyeTogJGktPntUeXBlfSAkaS0+e1NpemV9XG4iLCA1ICk7CiAgICAg
IH0KICAgICAgZWxzZSB7CiAgICAgICAgJGktPntWYWx1ZX0gPSAkdmFsdWU7CiAgICAgICAg
JHN0aC0+dHJhY2VfbXNnKCIgICAgLS0gVHlwZSAgOiAkaS0+e1R5cGV9ICRpLT57U2l6ZX1c
biIsIDUgKTsKICAgICAgfQogICAgfQogICAgZWxzZSB7CiAgICAgICRpLT57VmFsdWV9ID0g
V2luMzI6Ok9MRTo6VmFyaWFudC0+bmV3KCBXaW4zMjo6T0xFOjpWYXJpYW50OjpWVF9OVUxM
KCkgKTsKICAgIH0KICAgIHJldHVybiAxOwogIH0K
--------------060900060603050302090402--
0
sgoeldner
11/27/2006 1:12:20 PM
---- Steffen Goeldner <sgoeldner@cpan.org> wrote: 
> Attached is a fixed implementation for bind_param(). It would be nice if you (and
> others) give it a trial.

Steffen,
  I'm at some off-site training all of this week; if I can, I will try this week, otherwise I'll follow up on Monday or Tuesday of next week.  Either way, I appreciate your time and work investigating this, and will make sure I let you know how it goes...

> Steffen

Thank you!
amonotod

--

    `\|||/         amonotod@    | sun|perl|windows
      (@@)         charter.net  | sysadmin|dba
  ooO_(_)_Ooo____________________________________
  _____|_____|_____|_____|_____|_____|_____|_____|


0
amonotod
11/27/2006 9:28:30 PM
---- Steffen Goeldner <sgoeldner@cpan.org> wrote: 
> I further investigated your
> test case: the Jet ADO provider creates for the LONGBINARY column a parameter of
> type 202 (adVarWChar) and size 510 - both are wrong. Thus, it's necessary to set
> the type in bind_param() - which you did. However, DBD::ADO did not set the size.
> Attached is a fixed implementation for bind_param(). It would be nice if you (and
> others) give it a trial.

Steffen,
  I've tested your fix with my test script (included below), and it seems to work well.  I still need to test with the production code, but my confidence level is high.  :-)

> Steffen

Thanks again for all your great work with DBD::ADO,
amonotod

#!perl -w
use strict;

eval { use DBI; };
if ($@) { die "This system does not have the DBI installed!\n"; }
eval { use DBD::ADO; };
if ($@) { die "Database type ADO not supported!\n"; }
eval { use CGI; };
if ($@) { die "CGI module not supported!\n"; }
eval { use CGI::Carp; };
if ($@) { die "CGI::Carp module not supported!\n"; }

my ($dbh, $Access, $AccessDB, $Workspace);
my $db_name = "C:/development/web/PicsDB/myPics.mdb"; # Will be created by doDBLoad()...
my $connStr  = "dbi:ADO:Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;Data Source=$db_name";
my $tempDir = "C:/Temp/dbd_temp/"; # Must pre-exist...
my $status = 1;

my $q = new CGI;
my $load = $q->param('load');
my $showPic = $q->param('showPic');
my $show_picID = $q->param('picID');
if ($load) {
  print "Doing database load...\n";
  eval { use Text::CSV_XS; };
  if ($@) { die "Text::CSV_XS not supported...\n"; }
  doDBLoad();
  exportDB();
  print "All done!\n";
  if ($status) { print "\n\tOperation was a success! :-)\n\n"; }
  else         { print "\n\tOperation was a failure! :-(\n\n"; }
} else {
  connectDB();
  if (($showPic) && ($show_picID)) {
    showPic();
  } else {
    showPicLinks();
  }
}
$dbh->disconnect();
exit;

sub showPic {
  my $sqlStatement = "select picType, picData from myPics where picID = ?";
  my $sthSelect = $dbh->prepare($sqlStatement);
  eval {$sthSelect->execute($show_picID); };
  if ($@) { carp("Select statement '$sqlStatement' failed!\nErrors: $dbh->errstr \n"); exit; }
  my ($picType, $picData) = $sthSelect->fetchrow_array;
  $sthSelect->finish;
  print $q->header($picType);
  print $picData;
}

sub showPicLinks {
  print $q->header('text/html');
  print $q->start_html("myPics DB Display");
  my $sqlStatement = "select picID, picComment from myPics";
  my $sthSelect = $dbh->prepare($sqlStatement);
  eval {$sthSelect->execute; };
  if ($@) { carp("Select statement '$sqlStatement' failed!\nErrors: $dbh->errstr \n"); exit; }
  while (my ($picID, $picComment) = $sthSelect->fetchrow_array ) {
    print "<a href='myPics.pl?showPic=1&picID=$picID'><b>$picComment:</b></a><br><img src=myPics.pl?showPic=1&picID=$picID><br>\n";
  }
  $sthSelect->finish;
}

sub connectDB {
  eval { $dbh = DBI->connect( $connStr, "Admin", "", {RaiseError => 0, PrintError => 0, AutoCommit => 1} ); };
  if ($@) { die("Database connection failed!\n$@\n"); }
  $dbh->{LongReadLen} = 2000000;
  $dbh->{LongTruncOk} = 0;
}

sub doDBLoad {
  my $csv = Text::CSV_XS->new;
  print "Creating database...";
  CreateAccessDB();
  print "  Done!\n";
  connectDB();
  my $create_statement = "create table [myPics] ([picID] INT NOT NULL, [picComment] VARCHAR (50), [picType] VARCHAR (50), [picData] IMAGE , ".
                         "PRIMARY KEY ([picID] ), CONSTRAINT myPic_PK UNIQUE ([picID] ))";
  my $sth = $dbh->prepare($create_statement);
  eval {$sth->execute; };
  if ($@) { die "Create statement failed!\nErrors: $dbh->errstr \n"; }
  
  my $sqlStatement = "INSERT INTO myPics (picID, picComment, picType, picData) VALUES (?, ?, ?, ?)";
  $sth = $dbh->prepare($sqlStatement);
  my $picList = PicList();
  foreach (split("\n", $picList)) {
    if ($csv->parse($_)) {
      my ($picID, $picComment, $picType, $picImage) = $csv->fields;
      if (-e $picImage) {
        print "Loading $picImage into database...";
        my $picData = readblobfile($picImage);
        $sth->bind_param(1, $picID);
        $sth->bind_param(2, $picComment);
        $sth->bind_param(3, $picType);
        $sth->bind_param(4, $picData, DBI::SQL_LONGVARBINARY );
        eval { $sth->execute; };
        if ($@) {
          print "Graphic import failed for image $picImage\n";
          $dbh->disconnect;
          exit(255);
        }
        print "  Done!\n";
      } else { print "Could not find image $picImage; not loaded!\n"; }
    } else { print "CSV parsing failed!\n"; }
  }
}

sub exportDB {
  print "Exporting grapics to $tempDir\n";
  unless (-d $tempDir) { print "Temp dir $tempDir does not exist!\n"; exit(1); }
  my $csv = Text::CSV_XS->new;
  my $picList = PicList();
  my $sqlStatement = "select picData from myPics where picID = ?";
  my $sthSelect = $dbh->prepare($sqlStatement);
  foreach (split("\n", $picList)) {
    if ($csv->parse($_)) {
      my ($picID, $picComment, $picType, $picImage) = $csv->fields;
      my $picName = $tempDir . substr($picImage,rindex($picImage,"\\")+1,length($picImage));
      print "picName is $picName\n";
      eval {$sthSelect->execute($picID); };
      if ($@) { carp("Select statement '$sqlStatement' failed!\nErrors: $dbh->errstr \n"); exit; }
      my ($picData) = $sthSelect->fetchrow;
      open(IMAGE, "> $picName") || die("Could not open new image file for write\n");
      binmode(IMAGE);
      print IMAGE $picData;
      close(IMAGE);
      $sthSelect->finish;
      my $origSize = (-s $picImage);
      my $newSize = (-s $picName);
      unless ($origSize == $newSize) {
        print "\tError: Imported and exported files DO NOT match in size....!\n";
        $status = 0;
      } else {
        print "\tSuccess: Imported and exported files match in size....!\n";
      }
    }
  }
}

sub readblobfile($) {
  my $file = shift; #get file name
  local( $/, *FILE); #see perldoc perlvar for an explanation here
  open(FILE, "$file") or die "$!";
  binmode(FILE);
  my $content = <FILE>;
  close(FILE);
  return $content;
}

sub CreateAccessDB {
  if ( -e "$db_name") { # if the file already exists, delete it
    unlink("$db_name") || die("Could not delete the old database file $db_name\n");
  }
  eval { 
    use Win32::OLE; 
  };
  if ($@) { die "Win32::OLE not supported...\n"; }
  eval { use Win32::OLE::Const 'Microsoft ActiveX Data Objects'; };
  if ($@) { die "ADO maybe not supported...?\n"; }
  eval {
    $AccessDB = Win32::OLE->new("ADOX.Catalog");
    $AccessDB->Create("Provider='Microsoft.Jet.OLEDB.4.0';Jet OLEDB:Engine Type=5;Data Source='$db_name'");
  };
  if ($@) { die "Couldn't create the database $db_name...!\n"; }
  Win32::OLE->Uninitialize;
}
  
sub PicList {
  my $picList = <<'EOF';
1,The Charter Communications Logo,image/gif,C:\development\web\PicsDB\Charter_Logo.gif
2,The Google Logo,image/gif,C:\development\web\PicsDB\Google_Logo.gif
3,The Yahoo Logo,image/gif,C:\development\web\PicsDB\Yahoo_Logo.gif
4,The AOL Logo,image/gif,C:\development\web\PicsDB\AOL_Logo.gif
EOF
  return($picList);
}


--

    `\|||/         amonotod@    | sun|perl|windows
      (@@)         charter.net  | sysadmin|dba
  ooO_(_)_Ooo____________________________________
  _____|_____|_____|_____|_____|_____|_____|_____|


0
amonotod
12/6/2006 4:55:56 PM
Reply:

Similar Artilces:

Displaying an image from an Access ole object field
This might be an easy one for some.  I have never needed to store images in an Access database but due to requirement, I will have to make it happen this time. The problem I am having is the code runs without an error, but I am getting a red X for the image on the page I am displaying the image.  I used a datareader to retrieve the record and I know there is at least one row in the reader.  The image I stored in Access 2000 is a jpeg file.  Anyone has any input? Thanks in advanace....

Problem in converting MS Access OLE Object[Image] column to BLOB (binary large object bitmap)
Hi All, i have a table in MS Access with CandidateId and Image column. Image column is in OLE object  format. i need to move this to SQL server 2005 with CandidateId column with integer and candidate Image column to Image datatype. its very udgent, i need any tool to move this to SQL server 2005 or i need a code to move this table from MS Access to SQL server 2005 in C#. please do the needfull ASAP. waiting for your reply with regards       Use SSIS to import or DTSWizard. The best approach would be using SQL Server Migration Assistant for Access http://www.mi...

move, access users visual objects within a visual user object
Hi everyone. I'd like to make a user visual object (uvo) like the DW SQL painter: some nvo placed in an other nvo with lines (links) between them. I don't know if any of you has developp something similar but I encountered several problems: I can't move (with mouse) nvo placed on another nvo (no moveable properties, no move events) I's like to make a planning object as you can see here http://www.handyarchive.com/free/chart/index7.html Is there a way to do thath in PB ? You are better off doing this using a datawindow and yes it HAS been done before.....

Render images from Access Database stored as OLE Objects
Hello everyone, I can't get this handler to render anything more than an image placeholder with a red X in it. What am I doing wrong? I have a SQL Server version working perfectly, but this OLE version isn't rendering at all. The images are stored as OLE Objects (long binary), and I'm using C#. Anyone have any ideas? Below is my code so far... public void ProcessRequest (HttpContext context){    OleDbConnection con = new OleDbConnection(ConfigurationManager.ConnectionStrings[2].ToString());    con.Open();    OleDbCommand cmd = new OleDbCommand("select MIMEType, DocFile from tbl_Documents whe...

OLE object, User object, and drag and drop
<!doctype html public "-//w3c//dtd html 4.0 transitional//en"> <html> I can't seem to get OLE objects and User objects to drag when I try to drag them.&nbsp; I have autodrag turned on, but they still refuse to drag.&nbsp; Does anyone know why?&nbsp; If I drag a button or picture, for instance, I see the object actually move with the mouse pointer.&nbsp; With the OLE object and the User object, I get a circle with a line through it when I try to drag the object.&nbsp; The OLE object in question is an icon of a Word file. <p>Any help w...

Dbi objects to access an MS Access Db
I'm having a problem getting Tran-ide to recognize my access 2000 database. I've created a system dsn called Midas and created the attached nnsyreg.dat file and put it into e:\sybase\ImpactClient-5_3\config directory. I've also put this file in my projects directory and still get the following message. Unable to connect to session Midas. Any Advice? Thanks Brent begin 666 nnsyreg.dat M4V5S<VEO;BY-:61A<PT*"4Y.3U1?4TA!4D5$7TQ)0E)!4ED@/2!D8G0R-VUS M;V1B8PT*"4Y.3U1?1D%#5$]265]&54Y#5$E/3B ]($Y.4V5S3T1"0T9A8W1O M<GD-"@E.3E]315-?4T525D52(#T@24U...

how to create a user object in a user object
how to create a user object in a user object dynamically ? I want create an object row focus indicator (p_focusindicator), in an userobject datawindow. But i use in userobject datawindow this code: ip_focusindicator = create p_focusindicator SetRowFocusIndicator(ip_focusindicator) Do you know the solution ? Thanks Mikael You're in the wrong newsgroup. This a PowerDesigner newsgroup not a PowerBuilder newsgroup. You want to be in powersoft.public.powerbuilder.general With that being said, I do know the answer to your questions. First off, you cannot create a us...

Placing user objects on to other user objects.
Hello, Can anybody tell me why I'm not able to use syntax like openuserobject to connect an object to another object. You can attach user objects to a window, but can you attach a user object to another user object? BTW, nobody knows any good 3D drawing ActiveX's out there??? Mark Schupay Schupay Software Design 724-453-1331 ssd@zoominternet.net OpenUserObject() is a window (and all PB descendants thereof), not a system or WIndowObject function. That's the technical reason anyway. However, there is a method of doing this. Also, you may need to add the ...

ADO.net problem: i can not view image data for the rest of my table rows except the image for the first row but i can read all the other non image fields for all rows.
Hi i have a table with the following fields imgid............int imgdata........image imgtitle.........varchar(max) imgType........varchar(max) imgLength.....bigint incident_id.....int i have no problem with uploading the image but viewing the uploaded image(s) in a gridview has caused me great pain. The probelm is i have two pages. page1 grabs the image to display and page2 has a gridview that displays the grabed image. I wanted to have an sql statement like SELECT * FROM mytable WHERE incident_id=someValue. IF i have 5 records in this table and i view page2 ...

OLE Object properties of type OLE Object
I have 2 ocx's, both of which have a property of type OLEObject. One object initializes this property, the other one then needs to use it. However I cannot make an assignment like this work : ole_2.Object.Session = ole_1.Object.Session Session is the property name of the type OLEObject property for both ocx's. Any suggestions or help would be greatly appreciated!! Thanks, Ian Foutz ifoutz@kpmg.com ...

Images from Access Field.
Hello Everybody. If anyone can help... I have an image that i turn to bytes like this:- String fullFileName = Server.MapPath(findImgPath); System.Drawing.Image image = System.Drawing.Image.FromFile(fullFileName); System.IO.MemoryStream bytes = new System.IO.MemoryStream(); if (findImgPath.EndsWith("jpeg")) { image.Save( bytes, System.Drawing.Imaging.ImageFormat.Jpeg ); } else { image.Save( bytes, System.Drawing.Imaging.ImageFormat.Gif); } bytes.Flush(); string imageString = Convert.ToBase64String(bytes.ToArray()); This imageS...

Ole Objects in Access
Hi all,    I've just succeedeed at retrieving an embedded ole object from a mdb file. This ole object is an image but its format can be bmp, gif, jpeg or anything else. So, when I get the bytes corresponding to the ole object, it also retrieves a header with the ole information, besides the image data itself. This header is variable depending on what type of file is embedded, so when I write into a filestream the offset is unknown.        How do you work with images embedded in mdb's as ole objects? Still haven't found any solution :((( Altho...

OLE Object
Hi friends.I have got an Access database and one of my fields is and OLE Object type where I store an image in it. I want to know how in ASP.net to display the image in the web page. I am competly lost and have no idea how to do it. Can anyone PLEASE help me? Thank You for Your time!..Merovingian you Have a look at this article - http://aspalliance.com/articleViewer.aspx?aId=141&pId= - it is for SQL server but should work for Access too given that images are stored in it in appropriate format. Cheers Bug [MCSD]...

OLE in User Object
Hello, I have problem to insert a user object to a window. In the user object, there is a ole object on it. When I insert the user object to the window, the powerbuilder crashed. If the user object does not contain any ole object, that is find. Any idea ? Thanks in advance. Regards, Larry Chiu ...

Web resources about - DBD::ADO and Access IMAGE (OLE Object) fields... - perl.dbi.users

Resources last updated: 12/10/2015 10:59:19 PM