List:MySQL on Win32« Previous MessageNext Message »
From:Mark A. Moline Date:June 6 2001 9:25pm
Subject:RE: Storing Pictures or other file types in a MYSQL database
View as plain text  
Trying this again..............


Sorry it to so long folks,  I have been dealing with a NT crisis.


We use the 3rd party package ABCUpload by Webgoo to do our uploading.  It is
a two-step, two-page process.  First page presents a browse button to select
the files to upload.  This is passed on to our "recieve.asp" page, as
folllows:


############################################################################
#######
<%
		#Determine whether the drawing package is needed to be
uploaded#
$sql = "select Check_Type from Assemblies where Assembly_ID = $assyID";
$sth = $dbh->prepare($sql);
$sth->execute;
$library = $sth->fetchrow_hashref;
$type = $library->{'Check_Type'};
if($type >= 100) {
	%>
	<form name=myForm method=POST action=lb_receive.asp
enctype="multipart/form-data" onSubmit="return checkFile()">
	<table border="1" cellspacing="0" cellpadding="4">
	<tr><td><font size=3>Please select<b> <%= $assyNum
%>.zip</b> file
to upload</td>
	<td><input type=file name=upFile></td></tr>
	</table>
	<p><input type=submit value="Upload">
	<%
}
else {
%>
<form name=myForm method=POST action=lb_receive.asp
enctype="multipart/form-data">
	<script language=javascript>
		document.myForm.submit();
	</script>
<%
}
%>


############################################################################
#######

The "recieve.asp" actually does the upload:

############################################################################
#######

if($type >= 100) {
			#File was downloaded, upload it
	$ulObj = $Server->CreateObject("ABCUpload4.XForm");
	$ulObj->{'AbsolutePath'} = 1;
	$ulObj->{'Overwrite'} = 1;
	$ulFile = $ulObj->Item("upFile")->Item(1);
	if($ulFile->{'FileExists'}) {
		mkdir("${baseUnc}temp\\$assyNum") || die "Cannot create temp
directory!\n$!";
	
$ulFile->Save("${baseUnc}temp\\$assyNum\\".$ulFile->{'SafeFileName'});
		$Response->Write("<font size=3>File has been uploaded
successfully.</font><br>\n");
		$success = 1;
		$commandString = "${pkPath}pkzipc -EXTRACT -OVERWRITE=ALL
${baseUnc}temp\\$assyNum\\$assyNum.zip  job.pdf ${baseUnc}temp\\$assyNum\\";
		Win32::Process::Create($proc,
"${pkPath}pkzipc.exe",$commandString, 0, DETACHED_PROCESS, $pkPath);
		$proc->Wait(INFINITE);
		$commandString = "${pkPath}pkzipc -EXTRACT -OVERWRITE=ALL
${baseUnc}temp\\$assyNum\\$assyNum.zip  prep.pdf
${baseUnc}temp\\$assyNum\\";
		Win32::Process::Create($proc,
"${pkPath}pkzipc.exe",$commandString, 0, DETACHED_PROCESS, $pkPath);
		$proc->Wait(INFINITE);
		$sql = "delete from Zips where Assembly_ID = $assyID";
		$sth = $dbh->prepare($sql);
		$sth->execute();
		$sql = "delete from Pdfs where Assembly_ID = $assyID";
		$sth = $dbh->prepare($sql);
		$sth->execute();
		open(ZIP, "${baseUnc}temp\\$assyNum\\$assyNum.zip") || die
"Cannot open file\n$!\n";
		binmode(ZIP);
		$i = 1;
		while(read ZIP, $zipdata, 1048576) {
			$sql = "insert into Zips values (0, $assyID, ?,
$i)";
			$sth = $dbh->prepare($sql);
			$sth->execute($zipdata);
			$i++;
		}
		close(ZIP);
		unlink("${baseUnc}temp\\$assyNum\\$assyNum.zip");
		$i = 1;
		if(-e"${baseUnc}temp\\$assyNum\\job.pdf") {
			open(JOB, "${baseUnc}temp\\$assyNum\\job.pdf");
			binmode(JOB);
			while(read JOB, $jobdata, 1048576) {
				$sql = "insert into Pdfs values (0, $assyID,
?, 2, $i)";
				$sth = $dbh->prepare($sql);
				$sth->execute($jobdata);
				$i++;
			}
			close(JOB);
			unlink("${baseUnc}temp\\$assyNum\\job.pdf");
		}
		$i = 1;
		if(-e"${baseUnc}temp\\$assyNum\\prep.pdf") {
			open(PREP, "${baseUnc}temp\\$assyNum\\prep.pdf");
			binmode(PREP);
			while(read PREP, $prepdata, 1048576) {
				$sql = "insert into Pdfs values (0, $assyID,
?, 4, $i)";
				$sth = $dbh->prepare($sql);
				$sth->execute($prepdata);
				$i++;
			}
			close(PREP);
			unlink("${baseUnc}temp\\$assyNum\\prep.pdf");
		}



############################################################################
#######


We do a couple of gyrations for our specific process, where we zip up files
with a command line archiver prior to loading some data into the database,
but the pdfs do go into the database.  We have a limit on the size of the
files only in the sense that we read in upto 1MB and push that in give it a
unique number, then parcel in the next 1MB.  We have some data that is up to
12+MB, and we have found that 1MB parcels that all tie together to make the
pdf give us the best performance.

So that is how we load the data.  We work with PNGs because we take a PDF
and make it a PNG to display it on a page with some other data.  This looks
like the following:


############################################################################
#######



if($status =~ /P/) {
	$pdftype = 1;		#change to prerelease type
}
else {
	$pdftype = 0;
}
if (($OpID =~ /0250/) || ($OpID =~ /0060/) || ($OpID =~ /0025/)) {
	$pdftype += 3;
	if($dbh->do("select Packet from Pdfs where Assembly_ID = $assyID and
Type = $pdftype") + 0) {
		$page += 100;
	}
}
else {
	$pdftype += 1;
}

if($page =~ /\d/) {
	$sql = "select Png_File from Pngs where Assembly_ID = $assyID and
Type = $page";
	$sth = $dbh->prepare($sql);
	$rows = $sth->execute() + 0;
	if(!($rows)) {
		%>
<!--#include file="../engine/sh_wait_window_open.asp"-->
		<%
		$pdfname = time;
		while(! (open(PDF, ">${baseUnc}temp\\$pdfname.pdf")) ){
			$pdfname = time;
		}
		binmode(PDF);
		$sql = "select Pdf_File, Packet from Pdfs where Assembly_ID
= $assyID and Type = $pdftype order by Packet";
		$sth = $dbh->prepare($sql);
		$sth->execute();
		while($temp = $sth->fetchrow_hashref) {
			print PDF $temp->{'Pdf_File'};
		}
		close(PDF);
		if($page >= 100) {
			$tmpPage = $page - 100;
			if($tmpPage == 0) {
				$tmpPage = 1;	#create valid page number
and allow for prep page numbers#
			}
			$output = "gswin32c.exe -q -dBATCH -sDEVICE=png16m
-sOutputFile=${baseUnc}temp\\$pdfname.png -dFirstPage=$tmpPage
-dLastPage=$tmpPage -r220 -dNOPAUSE ${baseUnc}temp\\$pdfname.pdf";
		}
		else {
			$output = "gswin32c.exe -q -dBATCH -sDEVICE=png16m
-sOutputFile=${baseUnc}temp\\$pdfname.png -dFirstPage=$page -dLastPage=$page
-r220 -dNOPAUSE ${baseUnc}temp\\$pdfname.pdf";
		}
		$return = Win32::Process::Create($proc,
"${gsPath}gswin32c.exe",
			$output,
			0, DETACHED_PROCESS, $gsPath);
			$proc->Wait(INFINITE);
	#	$Response->Write("Output = $output $return<br>\n");

		open(PNG, "${baseUnc}temp\\$pdfname.png") || die "Cannot
open file\n$!\n";
		binmode(PNG);
		$i = 1;
		while(read PNG, $pngdata, 1048576) {
			$sql = "insert into Pngs values (0, $assyID, ?,
$page, $i)";
			$sth = $dbh->prepare($sql);
			$sth->execute($pngdata);
			$i++;
		}
		close(PNG);
		unlink("${baseUnc}temp\\$pdfname.png");
		unlink("${baseUnc}temp\\$pdfname.pdf");
		%>
<!--#include file="../engine/sh_wait_window_close.asp"-->
		<%

	}
	$Response->Write("<img
src=\"${basePath}engine/png.asp?assyID=$assyID&type=$page\" width=\"100%\"
height=\"${percent}%\">\n");
}
else {
	$Response->Write("<font size = 4><b>NO DRAWING
AVAILABLE</b></font>\n");
}



############################################################################
#######

This has a gyration where the pdf is pulled out of the database, into a temp
file, and ghostscript converts it to PNG and dumps it back into the DB; IFF
the PNG doesn't already exist.  If it does, then the SQL commands of OPEN,
SELECT, and perl command of execute reads the PNG from the database.




I hope this makes some sense.  I have pulled only the parts where the logic
deals with BLOBs in the DB.  All of this is wrapped in ASP files and run in
a manufacturing environment.  Any questions, let me know.

Thread
Storing Pictures or other file types in a MYSQL databaseShaun Gilley1 Jun
RE: Storing Pictures or other file types in a MYSQL databaseMark A. Moline1 Jun
RE: Storing Pictures or other file types in a MYSQL databaseDanny Katcher5 Jun
  • RE: Storing Pictures or other file types in a MYSQL databasePaul DuBois5 Jun
RE: Storing Pictures or other file types in a MYSQL databaseShaun Gilley5 Jun
  • RE: Storing Pictures or other file types in a MYSQL databasePete French6 Jun
RE: Storing Pictures or other file types in a MYSQL databaseGlenn F. Henriksen6 Jun
RE: Storing Pictures or other file types in a MYSQL databaseMark A. Moline6 Jun
RE: Storing Pictures or other file types in a MYSQL databaseMark A. Moline6 Jun
RE: Storing Pictures or other file types in a MYSQL databaseMark A. Moline6 Jun