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 [EMAIL PROTECTED]"); }
$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____________________________________
_____|_____|_____|_____|_____|_____|_____|_____|
